How to subtract values between two queries if control value is the same (Access 2003)

[ashenton]

Firstly: I've a fairly inexperienced access user!

I'm developing a simple stock control database and need to be able to show a total stock for each item.

So far I have two queries, both are grouped by the unique item of stock.

The first query shows how many items of stock we have received. All unique items of stock are always displayed even if there are none in stock.

The second query shows how many items of stock we have issued. The second query only displays unique items of stock where at least 1 has been issued.

I need to subtract the total issued items from the total received items, for each unique stock item. I need to be able to do this even if no items of a stock item have been issued.

E.g. (NULL denotes stock hasn't been issued and therefore does not appear in query 2):

Item Description | Query Value 1 | Query Value 2 | Total

40gb HDD | 20 | 5 | 15
80gb HDD | 3 | NULL | 3

You get the idea :)

Any ideas on the best way to do this without needing to have the issued stock listed as 0 for things which I haven't issued (this would make other reports look messy!)

[jimatqsi]

Join query 1 to query 2, including all items in query 1 and only those with matching Items in query 2. Add a column "Stock:[Query1Value]-nz([Query2Value],0)"

Note that if you have used the same column name for units in both tables/queries (i.e "Quantity"), you will have to qualify that equation with the table name so it knows which "Quantity" you are talking about, like this:

"Stock:[Query1].[Quantity]-nz([Query2].[Quantity],0)"

NZ converts Nulls to whatever you supply in the second parameter.

Jim

[ashenton]

Hi,


Thanks for that, it works on the calculation side.

I now have a new problem!

I've told the query to group by stock item, so that no matter how many of an item we receive, it only display the item once with the total stock next to it.

E.g.

On the 1st we receive 10 40gb HDD's
On the 15th we receive 10 40gb HDD's.

I'd like it to show:

40gb HDD's | 20

But instead it's showing:

40gb HDD's | 10
40gb HDD's | 10

The attached pictures show how it looks and how it is set up.

Hope someone can help!

[jimatqsi]

The pics are too small to read the text, but I assume you are grouping by quantities and you should be summing the quantities. So change "group by" to "Sum" and you should be good to go.

Jim

[ashenton]

@jimatqsi
Gaaa!!!

I've done that and it works... Unless I issue the same items of stock on different orders...

E.g.

I have 5 40gb HDD's in stock.
I issue 2 40gb HDD's on order 1
I issue 1 40gb HDD on order 2
The total remaining should be 2 40gb HDD's

However... Access takes it upon itself to multiply the received stock by the number of orders that item was issued on and THEN does the subtraction!

So I end up with 10 in stock (5 * number of orders) then it takes 3 away (the total ordered across all orders)!

Is there a way of stopping this?

I thought I'd got it but, as with many things Microsoft, so close yet so far!

[jimatqsi]

The first, simplest way is to group by the on hand quantity. Since you are already grouping by item, and the item has only 1 on hand value, it doesn't really change your grouping any, and it solves your problem.

Another is to make a separate query on the sales table only, to Sum all the sold quantities and group by item. That will give you one row for each item, with a sold qty. Then do another query which joins one row from this query with the inventory master (again one row per item) so your final result is only one row per item, with correct sales totals.

Both ways work and there's something to be learned from trying it each way.

Jim

[ashenton]

I've got it sorted. I opened the database this morning, deleted the existing queries and re-wrote them and it works as I want it too now!

Thanks for your help :)