Queries = Stock In - Stock Out

[Chaz]

Try using

rain at indasol dot com dot au

Thanks, sending now.

 

[RainLover]

For some reason my query, now that Ive populated real data, is counting allocated stock incorrectly.

I know that 2 of one item is allocated, but it counts 4. The dB has some confidential bits in at the moment - somewhere I can host or put it for you to have a look please RainLover?

Which query are you refering to.

 

[Chaz]

Which query are you refering to.

I sent you the detail in the email. q_count_total_stock counts both in and out stock.

The outgoing stock is incorrect and should read 2 for 24 Volt Power supplies but reads 4. Other values are also increased / doubled and Im not sure why.

 

[Chaz]

The other values are also wrong - looks like its looking up the wrong ID or similar - will recheck it in a short while. If you look at the f_stock_outgoing you will see what should be counted .....

 

[RainLover]

Your joins are causing things to be counted twice.

I really don't think you are going to suceed by going down this track.

Perhaps you could try two sub queries then join the results into one.

 

[Chaz]

Your joins are causing things to be counted twice.

I really don't think you are going to suceed by going down this track.

Perhaps you could try two sub queries then join the results into one.

OK, thanks.

Ive tried previously (another db) to do it separately but found that I struggled to combine the 2 query results. I dont need to see the results in a combined query as long as I can use both queries on a report (if possible) and do the subtraction there.

What type of query or means do you recommend? I know someone prior had mentioned a union query which I got stuck on the last time when I tried.

Thanks once again.

 

[RainLover]

Did you look at the sample database I posted?

 

[Chaz]

Did you look at the sample database I posted?

I did, quickly as I did not think I needed to refer to it. Ill recheck it later once I get a bit of time.

 

[Chaz]

Got it working - but a different method.

I simply have 1 query counting outgoing stock. I then have a query counting incoming. The outgoing one that didnt work would yield correct results if I removed the incoming table.

I then put both query counts in a new query which gave odd answers. As soon as I joined the Product ID together (relationship line) - it worked.