Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Comparing tables (Access 2000)

    I have two tables containing client order records, with each record including a client number field.

    I need to calculate (a) the number of clients who have one or more order records in the first table (I can do this with a simple total query) and ([img]/forums/images/smilies/cool.gif[/img] the number of those clients who also have one or more records in the second table. Can the Find Duplicates wizard be used for this, or will I need to create a special query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing tables (Access 2000)

    No, the Find Duplicates Query Wizard is used to find duplicate records within one table.

    To find the new clients, you can use the Find Unmatched Query Wizard. Specify the "current" orders table in the first step, and the "old" orders table in the second step.
    Specify the client number as link field in the third step (it's probably already selected), and specify other fields you want to see in the fourth step.

    To find returning clients, create a query based on the "current" and "old" orders tables, joined on the client number field.
    Add the client number field to the query grid. In the query properties, set Unique Values to Yes, to suppress duplicates.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Comparing tables (Access 2000)

    Thanks, Hans. I now have a slightly more complicated situation. I have two tables containing client numbers for clients who have were active in periods A and B respectively, and a totals query listing client numbers for clients who were active in period C. I need to find the clients who were active in period A who were also active in period B or C (not necessarily both). I imagine that I could I create a query to merge table B and query C, then create a query joining the result of that with table A? If so, how would I create the merge query?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing tables (Access 2000)

    You can create a union query; this must be done in SQL view.
    - Create a new query, but don't add any tables.
    - Select View | SQL.
    - Enter an SQL statement like the following, substituting the correct names:

    SELECT [Client Number] FROM <!t>[Table for B]
    UNION
    SELECT [Client Number] FROM [Query for C]

    Save this query as (for example) qryB_or_C. You can then create a query based on the table for period A and this query.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Comparing tables (Access 2000)

    That works fine thanks, Hans.

Posting Permissions

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