Simple Report Query? I think not!

Benjamin Bolduc

Registered User.
Local time
Today, 07:43
Joined
Jan 4, 2002
Messages
169
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
 
I tried that and I couldn't get it to work. If anyone could help, Id really appreciate it. thanks again
smile.gif
 
I think something like this should work:

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

Simon
 
Do I put that in the Querie or in VB Code. Sorry Im not very good at this
wink.gif
 
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;
 
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.
 
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...)
 

Users who are viewing this thread

Back
Top Bottom