Union query data doubles

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
I have a problem with a union query and counting. I have two queries that count different items from different stores. I want to see like items from each store and put the count of usage from each store into a single query. The problem is when I union these queries together, I have like items say:
Item Store 2100 Usage 2150 Usage
ABC 2100 0 10
ABC 2150 0 10

The item is used at both stores as identified by the store number. The usage will appear for store 2150 for each line the item appears in. I would like to have the union query remove the usage if the store number is not equal to the line the item is in:
Item Store 2100 Usage 2150 Usage
ABC 2100 0 0
ABC 2150 0 10

Is there a way to filter this out?

Thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
I think you might need something other, or more, than a union query eg a crosstab query.
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
Got it. I made the union query only give me the information without any usage counts, then created another query to fill in the counts.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,997
Members
414,419
Latest member
JRDunya

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