Avatar of HOTWATT
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
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?

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.
Paul Cook-Giles

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.  :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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.
Paul Cook-Giles

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.

I put those two new columns in but when I run the query I get an error. I attached a photo.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Paul Cook-Giles

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

I have IM_KEY and ML_IMKEY connected
Paul Cook-Giles

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?

Awesome! Looks like that worked!
Your help has saved me hundreds of hours of internet surfing.

I thought it worked but I still seem to be getting double records in my query when there is a part with two locations.