Simple Report Query? I think not!

[Benjamin Bolduc]

Hi everyone!
Im trying to make a report that only shows products whose inventory is lower than 20,000. Now I know this sounds like a simple querie, but I dont have a "Balance" field in any of my tables. I use a calculated field that subtracts the "Quanity-Out" from the "Quantity-In". This works great to see the inventory of all the products, but how can I use a querie with an Unbound calculated field?

Thanks for responding!
Ben

 

[Rich]

Can't you add the calculated field to the query?

 

[Benjamin Bolduc]

I tried that and I couldn't get it to work. If anyone could help, Id really appreciate it. thanks again

 

[SimonC]

I think something like this should work:

SELECT ProductID, [Quantity-In]-[Quantity-Out] AS Balance
FROM tblStock
WHERE ([Quantity-In]-[Quantity-Out]) < 20000;

Simon

 

[Benjamin Bolduc]

Do I put that in the Querie or in VB Code. Sorry Im not very good at this

 

[Rich]

Post the SQL of your query

 

[Benjamin Bolduc]

Ok here it is, I hope you can make some sense of it.

SELECT Inventory.[UPC#], Inventory.[Product Code], Inventory.Brand, Inventory.Size, Inventory.Flavor, Inventory.Type, Inventory.Casing, Inventory.[Extra Info], [Inventory Entry].[Quantity-In], [Inventory Entry].[Quantity-Out], [Inventory Entry].Balance
FROM Inventory INNER JOIN [Inventory Entry] ON Inventory.[UPC#] = [Inventory Entry].[UPC#]
ORDER BY Inventory.Size, Inventory.Type;

 

[Rich]

Where does [Inventory Entry].Balance come from?

 

[Benjamin Bolduc]

Thats actually not being used. I was going to store the Balance amounts in that field but everyone said that was a bad idea, so that Balance field is inactive.

 

[Gizmo]

Since I'm an Access novice, I use Excel and do all my formulas there and link the table to Access. Make sure you open and save the Excel table before linking to Access (its a silly thing but...)