Query Criteria Question

[Guest]

I'm trying to make a query of two tables however they both have multiple
entries of the same information. One table has about 2300 Item Numbers with
lots of duplicates; the other table has about 10000 Item Numbers with even
more duplicates. What criteria do I need to use to get the query to find the
first matchings in both tables and then move on to the next without using the
previously matched numbers?

Thanks In Advance,
Rob

 

[Rick Brandt]

I'm trying to make a query of two tables however they both have
multiple entries of the same information. One table has about 2300
Item Numbers with lots of duplicates; the other table has about 10000
Item Numbers with even more duplicates. What criteria do I need to
use to get the query to find the first matchings in both tables and
then move on to the next without using the previously matched numbers?

Thanks In Advance,
Rob

Use a Totals query with GroupBy on the ItemNumber and First() on all the other
fields you want to see.

Of course the query engine's definition of "first" might very well be different
from your own. First() and Last() pretty much just guarantee that the fields
will all come from the same row. Exactly *which* row that ends up is not within
your control.

 

[Guest]

Sweet. That's awesome, I can now see the first instance of each matching Item
Number. How could I get a query to show only the duplications and then one to
show the orphans?

 

[Rick Brandt]

Sweet. That's awesome, I can now see the first instance of each
matching Item Number. How could I get a query to show only the
duplications and then one to show the orphans?

If I understand what you want correctly you can add a field after your GroupBy
that uses Count() and then filter on that with a >1 criteria. For the latter
you would have to try different join types between the tables. You will need to
be more specific about what you want there.

 

[Guest]

I'll try that and I hope I explained it better below.

Below Gives me the first instance of each matching item number and not the
other extra corresponding duplicates. That's great, I need to have this but I
would also like to show which Item Numbers are indeed duplicated and have
them and their duplicates listed. Then I would also like to show which Item
Numbers are listed in one table but does not have a match in the other.

|
Query Col 1 | Query Col 2
|
Field: Item Number | Item Number
Table: Old Master | New Master
Total: First | Group By
Sort: |
Show: Checked | Checked
Criteria: |
Or: |


Thanks sooo much for helping, seriously.

 

[Jim Polec]

yo;u can do thAT

 

[Guest]

Ummmmmmm OK?.?

yo;u can do thAT