Results 1 to 6 of 6
  1. #1
    rlfranz
    Guest

    Using table as input to query (Access97 SR-2)

    I have looked for this topic and didn't find it so please pardon any duplication. I have a table which contains user IDs (USER_TABLE). I have another table (DETAILS_TABLE) which contains, among other things, a field called JOBNAME. I want to use the userID in USER_TABLE to find matching values (LIKE) in the JOBNAME of the DETAILS_TABLE and create another table to be used as input to a report. Is this possible? Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using table as input to query (Access97 SR-2)

    If both the USER_TABLE and the DETAILS_TABLE have a field called UserID, create a query placing both tables in the upper pane of the query design and link them on UserID. Place fields from both tables that you want on the report in the lower grid. Then base your report on this query.

  3. #3
    rlfranz
    Guest

    Re: Using table as input to query (Access97 SR-2)

    The difficulty is that the details table does not have a field called UserID. The JOBNAME field in DETAILS_TABLE may contain the userID as part of the value. This is what I want to match.I need to do a LIKE query using each UserID from USER_TABLE against jobname in DETAILS_TABLE and create an output table containing the results.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using table as input to query (Access97 SR-2)

    You could do this in a query by not explicitly joining the tables and by using the criteria to create an implicit join. However, this is less efficient than simply adding a userID field to the table.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using table as input to query (Access97 SR-2)

    That's quite a different story. In that case, place both tables in the query and the criteria for JOBNAME would be similar to:
    Like "*" & [USER_TABLE].[UserID] & "*"
    However, it is much cleaner to place a UserID field in the table as Charlotte suggested.

  6. #6
    rlfranz
    Guest

    Re: Using table as input to query (Access97 SR-2)

    That's exactly what I need. Since the data in the DETAILS_TABLE gets refreshed each month it is not possible to add USERID to it. Thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •