Copying a column from one query/table to another within the same database

[scorpio]

I have two sets of data in queries with an inner join on the ID number only.



These are the fields in the two queries.

Query A Query B Query B Date
ID Number ID Number Date
12345 12345 1/1/12
12345 1/2/12
12345 1/3/12

I would like the data from Query A and Query B that have the same ID Number. So I want my results to look like below. The results should only return one field because there is only 1 field in table A.
ID Number Date
12345 1/1/12


However, the query is pulling the ID number from query A and all of the fields from query B that because they all match query A creating duplicate data. For example:

ID Number 12345 Date 1/1/12
ID Number 12345 Date 1/2/12
ID Number 12345 Date 1/3/12

Is there a way for me to put the date field in table A? so that way I can query and join the data using the ID # and the date so the results will only show that one record that is in that is in table A with the correspoding record in Table B? I am unable to just copy and past because there is no way for me to query data between the 2 except for ID #.

[June7]

Not understanding.

ID Number is unique in QueryA?

There are multiple records for each ID Number in QueryB?

Why do you want only 1/1/12 to show?

[scorpio]

No ID # is not unique in either table. I only want that exact record from table A that's why I only want to show 1/1/12 from table B. I don't want the field from table B to duplicate my results in table A. Yes there are multiple records for ID #s in both tables. But If table A has one record for ID I don't want it to pull 3 records from table B with the same ID #. How do I prevent this? I have an aggregate query that groups the appropriate fields but it still creates multiple records in results of my inner join query between table A & table B

[June7]

Do a query for each table that pulls only the desired single record for each ID Number. Then join the two queries.

Do you want all fields to be returned?

Why should 1/1/12 be the date returned? Why not 1/3/12? What is the rule for filtering?

[scorpio]

That's the problem there isn't one. I'm just going to run a duplicate query & delete the records I don't want. Thanks for your help

[June7]

The rule could be latest date or earliest date.

On what basis will be decision to delete? That is the rule to apply in query.