Query - One to Many

[JoeF]

I am having a heck of a difficult time with queries involving a one to many relationship. I have one table that contains sku information and one table that contains sales details.
Table 1
Item # Category Excess Units
123 video 100
124 video 50
125 audi 105

Table 2
Item # Location Sales
123 A $1,000
123 B $1,500
123 C $2,000
124 B $500
125 C $1,000

How do I make a query where I get a sum of all the sales by category along with total excess? A sum of category sales has to sum across locations, yet a sum of category excess has to be across items only. Can I do this in a single query, or do I have to run multiple queries?

 

[Tim1]

Joe,

I think that you will need two queries, because you will need to use a group by to get the sums that you require.

For the first one, just pick item#, category, location and aggregate sales.

For the second one, pick item#, category, and aggregate excess.

You can make a single query, but you will either have to drop the location from the first requirement, or accept that the excess will also be by location.

Sorry,

Tim

 

[JoeF]

Unfortunately, the second table also has sale year as well. I really need to link these tables somehow because I need to run a report by category of excess units for only products sold in a selected year.