How can I return unique values without using SQL?

[Freitag]

How can I return unique values from Table 1 where there are multiple associated records in Table 2? For example, table 1 is a quarterly report with thousands of records & has fields: division, store number,item#, percentage (12 items/measures per uniquedivision). Table 2 lists store# & state. To return the state associated with the division, I have to link the two tables. The problem is that there are multiple stores per division (it appears that all stores in a division are in the same state). How can I design my query grid to return only unique division numbers & their associated state w/o using SQL?

Please help!!

 

[boblarson]

Do you mean without WRITING any SQL? (just using the QBE (query by example grid)?

 

[Freitag]

Bob,

Yes, that is correct; ONLY using the QBE & NOT SQL - I don't know SQL yet.

Thanks!!

 

[boblarson]

Normally, writing SQL you would use a subquery to do this. But, in light of your requirements, you would write one query from ONLY table 1 and using the Group button, group on DIVISION and STORE #
Using this button:

And save that query. Then create a new query where you have that query and table two and link store #. Then you should be able to select DIVISION, STORE and STATE and have your output (if I read it correctly).

 

[Freitag]

Thanks, Bob! Will this also return each of the 12 measures by division (I do need this)?

 

[boblarson]

That will generate 12 records for each division/state. If you want this then I think you may want to use a report which can do grouping and stuff and not have duplicate values.

 

[Freitag]

Thank you, again!!