New to queries

[bugme]

Hi all,

I'm new to Access, but I have a database which I'm having trouble trying to get a simplified query from. The databases I currently have are below:

Sales database:

Date	Product	Colour	Quantity Sold
01/01/2015	T-shirt	Blue	3
02/01/2015	T-shirt	Black	6
04/01/2015	Pants	Red	2
04/01/2015	Pants	Green	1
05/01/2015	T-shirt	Blue	2
07/01/2015	Pants	Green	1

Stock on Hand (SOH) Database:

Product	Colour	SOH as at end of 31/12/2014
T-shirt	Blue	10
T-shirt	Black	12
Pants	Red	6
Pants	Green	5

What I'm trying to do is to output a query which sums up the total sales and current inventory of each product (irregardless of colour). I've written down what the query should look like, but I'm not sure how to get there. Any ideas?

Product	SOH at end of 2014	Quantity Sold	Current SOH
T-shirt	22	11	11
Pants	11	4	7

[CJ_London]

use a group by query - group by product and sum the other columns

[bugme]

Thanks a lot for the reply mate. I've updated the question in the OP as it's a little more complicated than what I thought it will be.

[CJ_London]

same answer as before - just don't bring through the date or colour

[bugme]

For some reason, this is the result I am getting (I've omitted the Current SOH for now):

Product	SOH at end of 2014	Quantity Sold
T-shirt	66	22
Pants	33	8

The two latter columns are grouped by 'Sum' and the Product column is just left as 'Group By'.

[CJ_London]

Ah - it is because you have 3 sales records and 2 SOH records - so SOH is repeated 3 times - and added 3 times.

You could try changing sum to avg or to be clearer you need two group by queries, one on the Sales table and one of the SOH table which you then combine in a third query, linking on product.

However it does get more complicated because you might have sales in the year, but no SOH (e.g. a new product), or you might have a SOH, but no sales. In either of these cases, a result will not appear.

To resolve this you need to bring in the product table as well and left join it to the two group by queries - so you will see all products and a SOH where it exists and sales where they exist

[bugme]

Ah - it is because you have 3 sales records and 2 SOH records - so SOH is repeated 3 times - and added 3 times.

You could try changing sum to avg or to be clearer you need two group by queries, one on the Sales table and one of the SOH table which you then combine in a third query, linking on product.

However it does get more complicated because you might have sales in the year, but no SOH (e.g. a new product), or you might have a SOH, but no sales. In either of these cases, a result will not appear.

To resolve this you need to bring in the product table as well and left join it to the two group by queries - so you will see all products and a SOH where it exists and sales where they exist

I've uploaded the Access file as for some reason, I just can't figure it out. Are you able to look at it for me?

[CJ_London]

One of the reason you are having problems is your data isn't normalised. I've taken the liberty of normalising it - please see attached which also has the solution you are looking for

[bugme]

You're a lifesaver! The database I have doesn't have separate tables for 'products' and 'product colours', but I managed to get it to work. Thanks again!

[CJ_London]

The database I have doesn't have separate tables for 'products' and 'product colours'

It should do if you are doing it properly!

[bugme]

I'm trying to minimise the filesize of this database (the wonders of working with big data) and am looking at a way to perform this query without having to use subqueries as above. My instinct says to use a SQL query to achieve this - any suggestions?

[CJ_London]

you'll need to remind me of the query. Also not sure what you mean by using a sql query - all queries are sql