Inventory Level Query With No Entries

[robsworld78]

Hi, I'm making a database to track inventory in many locations and I have everything working good, the only problem is when there is nothing entered in the system.

I have 3 queries to get me each locations inventory levels. The first query shows me product, location and sum of purchased. The second query shows me product, location and sum of sales. The third query is made from the first 2 queries and shows product, location and subtracts the sum of sales and purchase quantity to give me whats left.



I know this can be done on one query but I have lots of other calculated fields in the queries, I wanted to keep fields down so I split sales and purchases.

Anyways everything works great for all locations, there all tracked the same method, 4 locations in all. The problem is nothing works when theres no starting data. Products don't show up in the queries till a purchase, sale and transfer are done on each product. If I do a dummy order, sale and transfer for all locations with 0 in quantity it will work great from that point.

How can I tell the system everything has 0 if no records are found or whatever. I want it to look like I put the dummy orders, etc of zero in.

Any ideas?

[Rod]

Review your table relationships and particularly the joins in your queries.

[Rod]

... oh! nearly forgot: Review your 'Where' clauses for cases where no data exists (null). You may need to use the Nz function.

[robsworld78]

Hi, I'm pretty sure my relationships are ok, everything works the way I want it to once it has some data.

I do have the Nz function in my query like below

Bars: Nz([BarsTransfered],0)-Nz([SumOfBarsGiven],0)

Thanks

[robsworld78]

How would I use the Nz function on a bound field I think is how you say it?

On the first query I have the fields with the info selected from the dropdown list, can I add the Nz function to it somehow. I've tried a few ways but it always comes up empty.

[Rod]

First point to remember is that query joins will override table relationships if they are different. However Access' query builder will use the table relationships to specify a 'first try' join.

Missing data in query result sets are most often caused by one side of the joined tables being empty. I do not mean the table is empty; just that there are no matching records in the child table.

Here's the gospel from the horse's mouth:

Use a LEFT JOIN operation to create a left outer join . Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.
Use a RIGHT JOIN operation to create aright outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

I suggest you read up on joins in Access on-line help.

How are you creating these queries? Can you access the SQL? If so could you post it so someone can review it?

My comment about the Nz function is that sometimes a WHERE clause can result in null. If so the WHERE condition is never satisfied (I don't think you get an 'Invalid use of null' error - can't remember). To overcome this it's helpful to encase the offending part of the WHERE clause in an Nz function. Have you WHERE clauses in your SQL?

I don't really understand your last question. Are you using/passing parameter values to your queries from list control selections?

[robsworld78]

Hi, thanks for trying to explain it to me, I'll play with the relationship some more.

Here's the SQL from one query, this is for transfers from main inventory to another.

SELECT Inventory.Product, Nz([BoxesTransfered],0)*Nz([BoxQuanity],0) AS BarsTransfered, Nz([SumOfCasesTransfered],0)*Nz([BoxesPerCase],0) AS BoxesTransfered, Sum(Inventory.CasesTransfered) AS SumOfCasesTransfered, Transfers.Cooler, Product.BoxesPerCase, Product.BoxQuanity
FROM Transfers INNER JOIN (Product INNER JOIN Inventory ON Product.ProductID = Inventory.Product) ON Transfers.TransferID = Inventory.TransferID
GROUP BY Inventory.Product, Transfers.Cooler, Product.BoxesPerCase, Product.BoxQuanity
HAVING (((Transfers.Cooler)=2));

This is the SQL for the other query, orders.

SELECT Inventory.Product, Sum(Inventory.BarsGiven) AS SumOfBarsGiven, Orders.Cooler, Product.BoxQuanity, Product.BoxesPerCase, Nz([SumOfBarsGiven],0)/Nz([BoxQuanity],0) AS BoxesGiven, Nz([BoxesGiven],0)/Nz([BoxesPerCase],0) AS CasesGiven
FROM Orders, Product INNER JOIN Inventory ON Product.ProductID = Inventory.Product
GROUP BY Inventory.Product, Orders.Cooler, Product.BoxQuanity, Product.BoxesPerCase
HAVING (((Orders.Cooler)=2))
ORDER BY Inventory.Product;

Then I have a 3rd bringing the 2 together

SELECT Cooler2TransferIns.Cooler, Cooler2TransferOuts.Product, Cooler2TransferOuts.SumOfBarsGiven, Cooler2TransferOuts.BoxesGiven, Cooler2TransferOuts.CasesGiven, Cooler2TransferIns.BarsTransfered, Cooler2TransferIns.BoxesTransfered, Cooler2TransferIns.SumOfCasesTransfered, Nz([BarsTransfered],0)-Nz([SumOfBarsGiven],0) AS Bars, Nz([SumOfCasesTransfered],0)-Nz([CasesGiven],0) AS Cases, Nz([BoxesTransfered],0)-Nz([BoxesGiven],0) AS Boxes
FROM Cooler2TransferIns INNER JOIN Cooler2TransferOuts ON (Cooler2TransferIns.Cooler = Cooler2TransferOuts.Cooler) AND (Cooler2TransferIns.Product = Cooler2TransferOuts.Product)
GROUP BY Cooler2TransferIns.Cooler, Cooler2TransferOuts.Product, Cooler2TransferOuts.SumOfBarsGiven, Cooler2TransferOuts.BoxesGiven, Cooler2TransferOuts.CasesGiven, Cooler2TransferIns.BarsTransfered, Cooler2TransferIns.BoxesTransfered, Cooler2TransferIns.SumOfCasesTransfered;

I know I need the same technique for all 3 queries

I'm not using the WHERE clause

[Rod]

Hi,

Sorry for the delay - just been out to the local DIY store.

It's the inner joins that are causing your symptoms.

You can use an INNER JOIN operation in any FROM clause. This is the most common type of join. Inner joins combine records from two tables whenever there are matching values in a field common to both tables.

The phrase to note is, "whenever there are matching values in a field common to both tables." I think what you are telling us is that there may be no matching record in table 'Transfers' and thus no matching value. Even if there is a match on 'Product' and 'Inventory', your SQL will not return a record in the result set unless a record also exists in 'Transfers.'. You need to use outer joins.

My best advice - rather than try to alter your SQL and get it wrong, i.e. not what you want - is to tell you to 'play' with the SQL using Access' query design grid and experiment with different set-ups. If you want more guidance on how to do this then post back.

Rod

[robsworld78]

No need to ever be sorry, I'm very thankful you're giving your time to help me.

I never looked at the SQL view before, it might help me understand better. I have seen the view but never paid attention to it, a few times I screwed up the queries and they wouldn't open in design view anymore just the SQL view which I didn't know about. So I would just delete query and start over. I've been making the queries with design view, don't use the wizard very often. I'll play more with my relationships in the query and look at the SQL to see when I successfully made an OUTER JOIN.

In the meantime I did find a way to hide some dummy records in the system so it starts good and nobody knows those dummy entries are in the system.

Say I couldn't get it working the way it should with the OUTER JOIN does it mean my DB is made wrong and will give issues later even though it works the way I want? Or would it be best to spend days if thats what it took to get it right?

[Rod]

Another delay while I slept!

Your DB is probably fine. Leave it alone if it works as wanted.

I'm somewhat of a pedant: I use the term 'relationship' when referring to relationships between tables in the database and the term 'joins' when referring to joins between tables in queries. It preserves the distinction.

They can (and often need to be) different. If you like: relationships are permanent and reflect the 'true' nature of your data; joins are temporary in that they only exist for the query in which they are specified. When you use the automated query design, Access looks at the relationships and joins the tables to match those relationships. This explains why you sometimes get an inconsistent joins message when specifying a query; the relationships work fine in the DB but the matching joins make no sense in a query.

The Access Query Design (I'm not referring too the wizard here) is one of the best SQL learning tools around. If you switch between the GUI grid and the SQL view you can quickly learn the correct SQL syntax. You can also immediately run the query and check it gives you what you want. Start with a simple query and gradually add the complications until you have achieved your aims. (The lazy experts often use the grid to specify their query, switch to the SQL view and then copy and paste the resulting code. I of course never do this. You can tell when someone has done this as Access tends to use a superfluity of parentheses.)

In your case you can change the join types by double-clicking on the lines joining the tables in the GUI grid view. Access offers you three choices, the meaning of each is self evident and each corresponds with one of the join types. You can also delete the join and redraw it between other attributes (fields). The double-clicking operation is quite sensitive so you might have to move the cursor a fraction to get it to get Access to react.

Have fun.

[robsworld78]

Well I'll see what happens, I might end up leaving it as it is, who knows. All these lines are getting to me. I also spent so much time on my queries I hate to redo them all. There's alot more in them then you saw now, they also calculate volume and weight for piece, box and case which meant adding another table to the query.

One more question, you mentioned copying and pasting the SQL, where would someone put that if they manually typed it out? I thought a query had to be made from the grid or wizard, and now also just manually typed in the SQL view, where else can you put it?

Well I'm off to play for the night, but wish I could play on here instead, this stuffs a bit addicting once you get at it.

[Rod]

The most obvious place is in a DoCmd.RunSQL statement. Using DoCmd avoids the query being catalogued and available to 'destructive' users. Most often such SQL is of the append or make-table variety.

ADO has objects called Commands. These are essentially SQL where copy and paste is very, very useful and saves hours of frustration and debugging.

[Rod]

... forgot to mention another situation. Cutting and pasting SQL is very useful for Row Source properties, etc. when it's somewhat complicated.

BTW those cramped cells in the properties window and query design grid may be zoomed by right-clicking and selecting 'zoom' or more conveniently by Shift + F2.