Question about formatting a report

HOTWATT
HOTWATT used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
Hotwatt, I don't see an attached picture...  could you post the text of your query, and a screenshot of the report?

Author

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
Paul Cook-GilesSenior Application Developer

Commented:
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.  :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.
Paul Cook-GilesSenior Application Developer

Commented:
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.

Author

Commented:
I put those two new columns in but when I run the query I get an error. I attached a photo.
error.PNG
Senior Application Developer
Commented:
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.

Author

Commented:
I have IM_KEY and ML_IMKEY connected
Paul Cook-GilesSenior Application Developer

Commented:
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?

Author

Commented:
Awesome! Looks like that worked!

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial