Queries = Stock In - Stock Out

[Chaz]

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

 

[RainLover]

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

 

[Chaz]

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?

 

[RainLover]

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]

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.

 

[Chaz]

Mmmm:

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

• Load the page again by clicking the Refresh button in your web browser.
• Open the www.access-programmers.co.uk home page, then try to open another page.
• Click the Back 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 if the problem persists.
We apologise for any inconvenience.

 

[Chaz]

OK, zipped worked better.

 

[RainLover]

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.

 

[Chaz]

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?

 

[Chaz]

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.

 

[RainLover]

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.

 

[RainLover]

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

A bit complicated but it does handle stocktakes.

 

[Chaz]

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?

 

[RainLover]

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.

 

[Chaz]

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.

 

[RainLover]

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.

 

[Chaz]

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?

 

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

 

[Chaz]

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?

 

[RainLover]

Try using

rain at indasol dot com dot au