473,395 Members | 1,762 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Need help creating a query.

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
5 1121
debasisdas
8,127 Expert 4TB
can you please post the query that you are working on .
Jan 7 '09 #2
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.
Thanks.
Jan 7 '09 #3
NeoPa
32,556 Expert Mod 16PB
@jabberwocky75
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
Thanks for the help, I have resolved my problem. It all came down to a faulty understanding of query building.
Jan 19 '09 #5
NeoPa
32,556 Expert Mod 16PB
Glad to hear it. And thanks for posting back :)
Jan 19 '09 #6

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

Similar topics

9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
3
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
1
by: Grammie | last post by:
I need a report that looks like this: 1. Number of Enrollees as of Today: (count of everyone in query){can count last names) 2. Total Active People (count of everyone with "A" as...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
2
by: deja | last post by:
Hello, I am creating an a to z list - basically a count of all results that start with the letter "A", "B", "C" .... and so on. I am pretty poor at SQL so I am sure some brains out there can...
8
by: pamelafluente | last post by:
I am beginning aspNet, I know well win apps. Need a simple and schematic code example to start work. This is what I need to accomplish: ---------------------- Given button and a TextBox on a...
5
by: darnnews | last post by:
Hi, I have been creating a database to keep track of press clippings, but I have hit a couple stumbling blocks. Any help is much appreciate. 1) Seeing if my query is done I have the...
7
by: Rnykster | last post by:
I know a little about Access and have made several single table databases. Been struggling for about a month to do a multiple table database with no success. Help! There are two tables. First...
2
by: Jim Devenish | last post by:
I have a table named StockItems with field: StockItemID QuantityInStock I am creating assemblies of stock items and have another table named StockItemAssemblies with fields:...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.