How can I return unique values without using SQL?

Freitag

Board Regular
Joined
Jan 26, 2008
Messages
108
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!!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Do you mean without WRITING any SQL? (just using the QBE (query by example grid)?
 

Freitag

Board Regular
Joined
Jan 26, 2008
Messages
108
Bob,

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

Thanks!!
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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:
sigma.png

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

Board Regular
Joined
Jan 26, 2008
Messages
108

ADVERTISEMENT

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

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,395
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top