Hi,

I have a query that adds stockin in one expression and adds stockout in another:

SELECT t_product.Product_ID, t_product.Product_Name, Sum([t_acq_detail]![Quantity]) AS StockIn, Sum([t_invoice_detail]![Quantity]) AS StockOut, ([StockIn]-[StockOut]) AS StockAvailable
FROM (t_product INNER JOIN t_acq_detail ON t_product.Product_ID=t_acq_detail.Product_ID) INNER JOIN t_invoice_detail ON t_product.Product_ID=t_invoice_detail.Product_ID
GROUP BY t_product.Product_ID, t_product.Product_Name;

This only shows stock where both sets of queries match. How do I write or do a query to show stock where it may have come in (stock in) but not out?

Is this an issue with my relationship joins or something else?

Thanks

SELECT t_product.Product_ID, t_product.Product_Name, Sum(NZ([t_acq_detail]![Quantity],0)) AS StockIn, Sum(NZ([t_invoice_detail]![Quantity],0)) AS StockOut, ([StockIn]-[StockOut])

etc

Thanks, but makes no change. Not sure how to tally something as a 0 if its not populated in the table as ever recorded.

Perhaps I need to just enter fake rows to start things off?

Chaz

Could you post a cut down version of your database.

Just need the tables in question and your query.

Compact and Repair first to get the size down. Then ZIP it.

Please do not send a 2007 version as I do not have that.

Chaz

Could you post a cut down version of your database.

Just need the tables in question and your query.

Compact and Repair first to get the size down. Then ZIP it.

Please do not send a 2007 version as I do not have that.

Doing that now - thanks.

Mmmm:

The Access World Forums database has encountered a problem. Please try the following:

Load the page again by clicking the Refresh (http://www.access-programmers.co.uk/forums/newattachment.php?do=manageattach&p=#) button in your web browser.
Open the www.access-programmers.co.uk (http://www.access-programmers.co.uk/forums/../) home page, then try to open another page.
Click the Back (javascript:history.back(1)) button to try another link.

The www.access-programmers.co.uk forum technical staff have been notified of the error, though you may contact them (jon@access-programmers.co.uk) if the problem persists.
We apologise for any inconvenience.

OK, zipped worked better.

in q_count_total_stock

Try this.

SELECT t_product.Product_ID, t_product.Product_Name, Sum(NZ([t_acq_Detail].[Quantity],0)) AS acqQuanitity, Sum(NZ([t_invoice_detail].[Quantity],0)) AS invoiceQuanitity
FROM (t_product LEFT JOIN t_acq_detail ON t_product.Product_ID = t_acq_detail.Product_ID) LEFT JOIN t_invoice_detail ON t_product.Product_ID = t_invoice_detail.Product_ID
GROUP BY t_product.Product_ID, t_product.Product_Name;

You need to use the NZ function to return a 0 when there is a Null result.

Also your joins were incorrect.

Hope this helps.

I will post back the ammended database if you need it.

in q_count_total_stock

Try this.

SELECT t_product.Product_ID, t_product.Product_Name, Sum(NZ([t_acq_Detail].[Quantity],0)) AS acqQuanitity, Sum(NZ([t_invoice_detail].[Quantity],0)) AS invoiceQuanitity
FROM (t_product LEFT JOIN t_acq_detail ON t_product.Product_ID = t_acq_detail.Product_ID) LEFT JOIN t_invoice_detail ON t_product.Product_ID = t_invoice_detail.Product_ID
GROUP BY t_product.Product_ID, t_product.Product_Name;

You need to use the NZ function to return a 0 when there is a Null result.

Also your joins were incorrect.

Hope this helps.

I will post back the ammended database if you need it.

Could you please upload so I can compare and learn?

Struggling to get my head around the join types .... are these from the SQL (Query) or from the relationships?

Do the relationships define the inner join term in the SQL or is this set by the properties in the query windows and how the relationships are defined there?

Thanks for your help btw.

Here tis.

Have a look in the query grid.

Right click on the joining line and look at the Join Properties.

PS I did not change your query I added a new one with Rain on the end of the name.

This is a sample database on how to do Inventory Control.

A bit complicated but it does handle stocktakes.

Here tis.

Have a look in the query grid.

Right click on the joining line and look at the Join Properties.

PS I did not change your query I added a new one with Rain on the end of the name.

Thanks. Are the relationships correct and then needed to mod the query version? I take it these are not related then?

A little bit more information.

You have set up the relationships correctly, except I would not tick either Cascade Update or Cascade Delete.

It is in this window that you set the Enforce Referential Intregity which you have done correctly. Your Joins are also correct.

But these joins are only the default Joins and can be and often are overridden in the SQL or Query.

The Relationship window is also good to allow you to print out your relationships, and pin that to the wall so you can refer to it as your blueprint of the Database structure.

What you have done so far is good.

A little bit more information.

You have set up the relationships correctly, except I would not tick either Cascade Update or Cascade Delete.

It is in this window that you set the Enforce Referential Intregity which you have done correctly. You Joins are also correct.

But these joins are only the default Joins and can be and often are overridden in the SQL or Query.

The Relationship window is also good to allow you to print out your relationships, and pin that all the wall so you can refer to it as your blueprint of the Database structure.

What you have done so far is good.

Thanks, understood.

Most welcome.

But I would suggest that you download my sample Database as this is really the correct method.

The way you are going you will not be able to show stock levels as at a given date.

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?

Check my profile. I think it has my email address.

I have to go to the office now. Will check in later in the day.

Check my profile. I think it has my email address.

I have to go to the office now. Will check in later in the day.

Thanks, added you on MSN, not sure if you use the same email?

Try using

rain at indasol dot com dot au

Try using

rain at indasol dot com dot au

Thanks, sending now.

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.

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.

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 .....

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.

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.

Did you look at the sample database I posted?

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.

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.