Q: Easy query?

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

Hi

Could somebody help me with hopefully a simple query question?

I have two tables, each of which has a field holding a number. What steps do
I need to take to find the common numbers in both tables? That is, suppose
one table had 2,3,4,5 in its field and the second table had 4,5,6,7 in its
field, I'll like access to generate another table with the values 4 and 5.

Thanks in advance

Geoff
 
Create a query that includes both tables. Join the number field in the first
table to the number field in the second query. The query will only return
the records in both tables that have common numbers. You can convert the new
query to a Make Table query to create a new table of common numbers.
 
Hiya

Firstly, thanks for your prompt reply. Your suggestion did work, but
unfortunately it gives multiple instances of the same value i.e. if 2 occurs
several times in both tables then it appears sveral times in the query
result. Is there a way for me to filter it down so only one occurance of any
common number occurs?

Thanks again

Geoff
 
Geoff,

In your query design, go View > Totals; notice the new line headed
Totals: that appears in the grid; the default setting is Group By, leave
it to that, that's al that's required!

HTH,
Nikos
 
Open your query in design view. Right click in the query window. Click on
Properties in the dialog that appears. Set Unique Values to Yes. You will
now get one record for each common number.
 
Back
Top