By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Need help creating a query.

P: 3
I am trying to create an On Hand column for an inventory report. I have been all over the web and the solutions offered seem overly complex. All I need is a simple query for 'UnitsReceived (minus) UnitsSold' but I can't seem to get it to work no matter how I phrase it.
Table is 'Inventory Transactions'
Fields are 'UnitsReceived' and 'UnitsSold'
Thanks in advance.
Jan 7 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,127
can you please post the query that you are working on .
Jan 7 '09 #2

P: 3
Maybe query isn't the right term, I need an expression to enter as the source for the report. I have tried things such as =[UnitsReceived]-{UnitsSold], and just about every variation I can think of and it just returns blank spaces on the report. I know I am missing something but I don't know what. My inventory report lists all the products in the database along with their info (suppliers, product id, reorder level, etc.) My intent is to use this report to look at my products and compare reorder levels to onhand qty to see what needs ordering.
Jan 7 '09 #3

Expert Mod 15k+
P: 31,419
I suspect it is however.

In Access, a report is based on a record source of some kind. It's true that it is also possible to do some manipulation of results within the design of the report itself, but as a simple rule of thumb - if it can be done within the record source (generally a query though tables also work) then it is advisable to do so.

If we had some more details then we would be in a better position to help, but from what you have posted I would suggest something like the following be included in the SQL of your query (the record source) :
Expand|Select|Wrap|Line Numbers
  1. SELECT ...,
  2.        [UnitsReceived]-[UnitsSold] AS UnitsAvailable,
  3.        ...
  4. FROM [Inventory Transactions]
  5. ...
Welcome to Bytes!
Jan 8 '09 #4

P: 3
Thanks for the help, I have resolved my problem. It all came down to a faulty understanding of query building.
Jan 19 '09 #5

Expert Mod 15k+
P: 31,419
Glad to hear it. And thanks for posting back :)
Jan 19 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.