We help IT Professionals succeed at work.

Question about formatting a report

HOTWATT
HOTWATT asked
on
124 Views
Last Modified: 2017-03-22
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

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions