Query with counts, '0' values, and a calculation

[adagio12]

Hi guys, I tried looking up other threads in this forum for some problem similar to mine, but no luck.

I'm trying to build a query that could calculate the # of available products in stock. It seems simple when you think of it, but I couldn't get the results that I needed and couldn't figure out what I'm doing wrong. ;(

I have a table that lists all the products, where the product code is the primary key, and on that table, there is a column that states how many units of that product are there in total (UnitsTotal).

I also have another table that has records of transactions involving the sale of those products.


Columns that I want to see on the query are:
ProductID || ProductName || UnitsTotal || QtySold || UnitsAvailable


- ProductID, ProductName, UnitsTotal: I want every possible product listed (one entry for each product on one row) << values can be obtained from the 'ProductListing' Table

- QtySold: I want this column to be a count of products sold, and '0' values are allowed.

- UnitsAvailble: This will be a calculation of the difference between 'UnitsTotal' and 'QtySold' for each product.


Also, I know restocking of products is another issue (affects 'UnitsTotal'), but I don't want to include that into this query yet.
I've been working on this for hours and the query only returns information involving products that have been sold, but the records the products that have yet to be sold are omitted. ;(

Any help would be appreciated. Thanks in advance!

 

[vbaInet]

So what have you got so far and what is the output? Can you show us your query including the SQL too?

Welcome to AWF by the way

 

[adagio12]

Thanks for the welcome!
Anyways, I finally got it figured out. I had to add the "Nz" function and add 'join properties'.
I guess it must be a common problem that people might have with Access; it should go on a list of FAQs.

Now I'm struggling with a joint 'Iff' and 'DateDiff' function calculation

 

[vbaInet]

I guess it must be a common problem that people might have with Access; it should go on a list of FAQs.

It can be common in different contexts and it would be difficult to list out all the possible probabilities. It can be easily traced once we see the sql of the query

Now I'm struggling with a joint 'Iff' and 'DateDiff' function calculation

What's the problem there?

 

[gemma-the-husky]

partial quote ...

I have a table that lists all the products, where the product code is the primary key, and on that table, there is a column that states how many units of that product are there in total (UnitsTotal).

can you clarify

how does this value relate to the transactions in the transactions table?


In theory, you dont need to store the stock on hand anywhere - but it depends exactly what this figure is