Question about formatting a report
I have to make a report that shows part numbers, where they are located and their on hand balances. I created a query that shows part numbers located in FG location and Stock Sales Location with their on hand balance.(attached photo) I want the report to have a single line for each part number and then a column that says Stock Sales (on hand) and a column that says FG (on hand) so that you can read across one line and see where that part is and if it is in two different locations and both locations on hand balance. Right now my query just lists part numbers and if there is a part in FG and stock sales that part number shows up twice in the list? How would I go about making everything show up on one line?
Example (what I want my report to look like)
Part # Stock Sales (on hand) FG (on hand)
HS045049323 5 10
Example (what I want my report to look like)
Part # Stock Sales (on hand) FG (on hand)
HS045049323 5 10
Last Comment
Paul Cook-Giles
Hotwatt, I don't see an attached picture... could you post the text of your query, and a screenshot of the report?
ASKER
I have not created a report yet I just have the query because I couldn't figure out how to make the part #s that are in both FG and Stock Sales locations show up across one line instead of multiple.
Query-view.PNG
Query.PNG
Query-view.PNG
Query.PNG
If I understand the requirements and your table structure, this should work:
Change the criteria for ML_LOCATION to “STCK SALES”.
Add a new column: OnHand: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION + ‘FG’”)
These changes restrict the records returned by the query to STCK SALES rows; the DLookup function looks at the ML table and grabs the FG value for the IM_KEY. (This assumes that there are exactly two rows (one STCK SALES, one FG) per IM_KEY, and that the FG row has 0 in it if there are no parts; if either of those assumptions are wrong, we'll need to do some more refining of the query.)
You may need to type over the single and double quotes if you copy/paste the line into your query grid; they sometimes come through a paste as curved marks, and they need to be straight.
Let us know how it work out. :)
Change the criteria for ML_LOCATION to “STCK SALES”.
Add a new column: OnHand: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION + ‘FG’”)
These changes restrict the records returned by the query to STCK SALES rows; the DLookup function looks at the ML table and grabs the FG value for the IM_KEY. (This assumes that there are exactly two rows (one STCK SALES, one FG) per IM_KEY, and that the FG row has 0 in it if there are no parts; if either of those assumptions are wrong, we'll need to do some more refining of the query.)
You may need to type over the single and double quotes if you copy/paste the line into your query grid; they sometimes come through a paste as curved marks, and they need to be straight.
Let us know how it work out. :)
ASKER
Some part numbers can be in either just "FG" location or just "Stock sales" location so there are not exactly two rows for every part number.
OK, we can make that work.
Put the query in Design view, and in the properties box, set Unique Values = Yes.
Leave criteria for ML_LOCATION = “STCK SALES” or "FG"
Delete the ML_QTY and DOLLAR columns.
Add two new columns:
InStock: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘STCK SALES’”)
OnHand: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘FG’”)
When you build your report, add fields to calculate [InStock] * [IM_PRICES1] and [OnHand] * [IM_PRICES1].
The Unique Values setting means that you'll see only one row for each unique IM_KEY/IM_PRICES1 pair, no matter how many times it may be in the table.
The two DLookup columns look at the ML table and grab either the FG value or the STCK SALES value for the IM_KEY, and display both on the same row.
Put the query in Design view, and in the properties box, set Unique Values = Yes.
Leave criteria for ML_LOCATION = “STCK SALES” or "FG"
Delete the ML_QTY and DOLLAR columns.
Add two new columns:
InStock: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘STCK SALES’”)
OnHand: DLookup(“ML_QTY”, “ML”, “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘FG’”)
When you build your report, add fields to calculate [InStock] * [IM_PRICES1] and [OnHand] * [IM_PRICES1].
The Unique Values setting means that you'll see only one row for each unique IM_KEY/IM_PRICES1 pair, no matter how many times it may be in the table.
The two DLookup columns look at the ML table and grab either the FG value or the STCK SALES value for the IM_KEY, and display both on the same row.
ASKER
I put those two new columns in but when I run the query I get an error. I attached a photo.
error.PNG
error.PNG
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I have IM_KEY and ML_IMKEY connected
OK, so your criteria argument should be “[ML_IMKEY] = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘STCK SALES’”.
Does that change let the query compile and run?
Does that change let the query compile and run?
ASKER
Awesome! Looks like that worked!
ASKER
I thought it worked but I still seem to be getting double records in my query when there is a part with two locations.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
