Stock report totalling

[Andy B]

I am trying to create a stock report that shows total stock by product.

The problem l have is that as not all my sales are out of stock , l have created a tick box on my order form to denote stock in & stock out, only ticked as appropriate.

What l do not seem to be able to sort out is a formula that adds up the total stock in (ticked) but minus the total stock out (ticked) where appropriate.

I know this SHOULD be simple but the answer alludes me at present.

Any help would be appreciated.

Andy B

 

[Len Boorman]

Hmmm

Think that doing it in a single hit is going to be difficult

Think that you need query to select stock in
Think that you need a query to select stock out Note that in this case quantity =quantity x -1

Then a Union All query

Then a summation of the Union query

You said it was simple

Len B

 

[Andy B]

thanks for the advise.

I have created the two queries stock in & stock out, But l do not know how to creat the UNION ALL query that you talk about.

Any advise on how to do this would be much appreciated.

Andy B.

 

[Len Boorman]

This is the real easy bit

Display both of the queries you have created in the SQL view

Delete the semi colon at the end of query 1

On the next line type UNION ALL

then copy and paste the sql from query 2 after the UNION ALL

Leave the final semi colon.

Run this query and you should see that you have query 1 and query 2 joined together. You must have the same field sequence in each of the individual queries.

Okay providing this works then you no longer need the two initial queries (it a great cheat to produce the sql though)

Now you can do a summation query using the Union query as the source

Len

 

[Andy B]

All done and reporting just fine, thanks for your help it is much appreciated.

Andy B.

 

[Len Boorman]

Just a little bit more info on the UNION query

UNION ALL will combine all rows from all sub queries

UNION will just combine all non duplicate rows

The is also a UNION MINUS that Access does not support which just gives the duplicates.

UNION can be very handy

Len

 

[Len Boorman]

Noticed that we are nearly neighbours
len