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
HOTWATTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Hotwatt, I don't see an attached picture...  could you post the text of your query, and a screenshot of the report?
0
HOTWATTAuthor Commented:
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
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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.  :)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HOTWATTAuthor Commented:
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.
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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.
0
HOTWATTAuthor Commented:
I put those two new columns in but when I run the query I get an error. I attached a photo.
error.PNG
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Example:    DLookup(“1:WhatImLookingFor”, “2:DataSource”, “3:CriteriaStatement’”)

The DLookup function has three "arguments"... elements that tell the function what you want.  In this example, you want 1) the ML_QTY field, 2)  the ML table is the source, and 3) the selection criteria is “IM_KEY = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘STCK SALES’”.  The function will return the first ML_QTY in ML that meets the criteria.

The message is telling you that the DLookup function cannot find the IM_KEY field in the ML table.

What fields are in the relationship between IM and ML?  On the IM side, it should be IM_KEY.  Once you know what the ML side of the relationship is, you can edit the DLookup function so that the criteria is “[FieldInML] = ‘” & [IM_Key] & “’ and ML_LOCATION = ‘STCK SALES’”.

You'll need to do that for both DLookups.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HOTWATTAuthor Commented:
I have IM_KEY and ML_IMKEY connected
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
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?
0
HOTWATTAuthor Commented:
Awesome! Looks like that worked!
1
HOTWATTAuthor Commented:
I thought it worked but I still seem to be getting double records in my query when there is a part with two locations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.