We help IT Professionals succeed at work.

packing Slip Query

I am trying to build a query that I can use to support a packing slip report.  I have a table (t-PackingSlip) that contains a packing slip number (increment), and a packing slip detail table [t-PackingSlipDetail] that contains specific shipment records. The relationsip between the two is one to many.

When a user creates a packing slip, they are prompted to enter an order ID number into a form for t-PackingSlip.  This action creates the packing slip ID number.  Then, they enter specific data into a sub form for t-PackingSlipDetail.

I would like to create a query that shows the following columns of data:  Original order qty., previous shipped qty., ship qty., and outstanding qty..  I think I've done a good job struturing my tables/forms/sub-forms to accommodate this calculation, but I can not quite figure out how to get started w/ the expression for the "previously shipped qty.".

When a user runs the packing slip report, they will be prompted to enter the packing slip ID number.  How do I insert a conditional expression that says, "Add all of the ship qty. values from packing slips that are less than the user entered packing slip number?"
Comment
Watch Question

Top Expert 2011

Commented:
create a separate access query


select top 1 p.orderno,p.packslipno,sum(p1.qty)
from t-oackingslipdetail as p
inner join t-[ackingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipno
group by p.orderno,p.packslipno
order by 1,2 desc

then join that query to your existing query
as

left Join Q_priorpackqty as ppq
on X.orderno=ppq.orderno
 

Author

Commented:
Thanks - I'm goign to need a litle more help implenting this soluition.
Q1 - where do I add the first paragraph of code above?
Top Expert 2011

Commented:
go into access
and create a new query

then paste that into the SQL VIEW (mode?) (under view)

and save it as q_priorpackqty


then go into your query
and select the q_priorpackqty  
and make sure its joined/related as a LEFT JOIN

hth
 

Author

Commented:
I get the following error message:  Syntax error in FROM clause
 
Top Expert 2011

Commented:
the p1 packingslipdetail was misspelt.

select top 1 p.orderno,p.packslipno,sum(p1.qty)
from t-oackingslipdetail as p
inner join t-packingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipno
group by p.orderno,p.packslipno
order by 1,2 desc
Top Expert 2011

Commented:
duh! and the p

select top 1 p.orderno,p.packslipno,sum(p1.qty)
from [t-packingslipdetail] as p
inner join [t-packingslipdetail] as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipno
group by p.orderno,p.packslipno
order by 1,2 desc

Author

Commented:
okay, so I am prompted to enter five piece of info. when I un the query
p.orderno
p.packingslipno
p1.qty
p1 packingslipno
p1.orderno
 
Do I need to sycn that field names in the SQL query with the field names in my query?
Top Expert 2011

Commented:
you should just join with the orderno / packingslipno


then go into your query
and select the q_priorpackqty  
and make sure its joined/related as a LEFT JOIN

i don't understand why you are being asked for 5 column inputs?


Author

Commented:
Sorry, I'm probably doing something wrong here.  I've attached the two queries (w/ supporting tables).  Can you take a look?  Thanks
eetemp.mdb
Top Expert 2011

Commented:
i don't have access to MS access at my location

can you just post the sql view of the queries

Author

Commented:


I saved your SQL query as q-Report-SQL
SELECT [q-Report-PackingSlip].OrderID, [q-Report-PackingSlip].PONumber
FROM [q-Report-SQL] LEFT JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].OrderID = [q-Report-PackingSlip].OrderID) AND ([q-Report-SQL].packslipno = [q-Report-PackingSlip].PackingSlipID);
 
Top Expert 2011

Commented:
the left join is the wrong way around

your query (is the base query) should be on the left...

so either re-order it or change the left into right

SELECT [q-Report-PackingSlip].OrderID, [q-Report-PackingSlip].PONumber
FROM [q-Report-SQL] Right JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].OrderID = [q-Report-PackingSlip].OrderID) AND ([q-Report-SQL].packslipno = [q-Report-PackingSlip].PackingSlipID);

 

Author

Commented:
Here is my current query:
SELECT [q-Report-PackingSlip].OrderID, [q-Report-PackingSlip].PONumber
FROM [q-Report-SQL] RIGHT JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].packslipno=[q-Report-PackingSlip].PackingSlipID) AND ([q-Report-SQL].OrderID=[q-Report-PackingSlip].OrderID);
I am still being prompted to enter 5 piece of information?
p.orderno
p.packingslipno
p1.qty
p1 packingslipno
p1.orderno
 

Author

Commented:
any thougths?
You're being prompted to enter values for those items because they aren't your field names.  They're not in your tables, so Access asks you to enter values for them.  You needed to take the field names in the query that Lowfatspread posted and replace them with your actual field names.

 Also, you posted that the detail table was named t-PackingSlipDetail, so that's what he used in his query, but the actual name is t-PackingSlipsDetail (Slips, not Slip).

 Having said that, the query he posted won't work, because the tables aren't laid out quite the way he was thinking.  At least not in the Access db that you posted.

 t-PackingSlips has these fields:

 PackingSlipID
 OrderID
 Date
 TrackingNumber
 ShippingCost
 ShipDate
 PaymentDueDate

 t-PackingSlipsDetail has these fields:

 PackingSlipDetialID
 PackingSlipID
 OrderLineItemDetail
 ProductionLotNumber
 MaterialLotNumber
 ShipQty


 This complicates things a bit, because ShipQty is in t-PackingSlipsDetail, but OrderID is in t-PackingSlips.  You (presumably) need OrderID to keep the packing slips for each order separate.

 I've tried to come up with a new version of Lowfatspread's query.  The "TOP 1" in his query doesn't make sense to me, so I left it out, and without that, the "ORDER BY" doesn't seem necessary, so I left it out too.

 Also, he had p1.packslipno<=p.packslipno.  It should be just <, not <=.  This was _really_ driving me nuts.  I just couldn't figure out where the screwy results were coming from until I finally paid attention and realized that that test was wrong.  :-)  Note that I reversed the order of the fields in my query, so my test is >, not <.

 This query seems to produce the correct results with the data in the db you posted.

SELECT p.OrderID, p.PackingSlipID,sum(p2.ShipQty) as Prev_Ship_Qty
FROM ([t-PackingSlips] AS p
INNER JOIN [t-PackingSlips] AS p1
ON p.OrderID=p1.OrderID
and p.PackingSlipID>p1.PackingSlipID)
 INNER JOIN [t-PackingSlipsDetail] AS p2
 ON p1.PackingSlipID=p2.PackingSlipID
GROUP BY p.OrderID, p.PackingSlipID;


  As Lowfatspread said, the idea is that you set this up as a new query, then link your current report data to this query using OrderID and PackingSlipID, to get the previously shipped quantity (Prev_Ship_Qty).


 James

Author

Commented:

Thanks for the help, James.  So when I run the query, I get the following data using the previously posted db.
OrderID / PackingSlipID / Prev_Ship_Qty : 2146 / 907 / 51
The Prev_Ship_Qty. value that has been calculated is 51.  Yet, packing slip #906 included the shipment of two different part numbers, a qty. of 50 for item one and a qty. of 1 for item two.  It appears as though the SQL is adding these two values together, but I need them grouped by CustomPartID and then totaled.
Is there an easy fix for this?
Thanks
 
Sorry.  I didn't see your last post until earlier today, and I was at home at the time and don't have Access there.

 I think the query could be changed to also group by CustomPartID, but, at a glance, it looks like that would require adding two more tables to the query.  I see CustomPartID in t-OrderPartDetail.  In order to get to that, it looks like you''d need the field OrderPartDetailID.  I think you'd have to link from t-PackingSlipsDetail to t-OrderLineItemDetail to get that field, and then from t-OrderLineItemDetail to t-OrderPartDetail.

 All of that seems doable, but is it necessary?

 Another option that seems to work with your test data is to add a group on OrderLineItemDetail to the query.  That seems to be unique for each order line, and it's in t-PackingSlipsDetail, so you don't have to add any additional tables.  Here's a version of the query with OrderLineItemDetail added.  You'd link to this using OrderID, PackingSlipID and OrderLineItemDetail.


SELECT p.OrderID, p.PackingSlipID, p2.OrderLineItemDetail, sum(p2.ShipQty) AS Prev_Ship_Qty
FROM ([t-PackingSlips] AS p INNER JOIN [t-PackingSlips] AS p1 ON (p.PackingSlipID>p1.PackingSlipID) AND (p.OrderID=p1.OrderID)) INNER JOIN [t-PackingSlipsDetail] AS p2 ON p1.PackingSlipID=p2.PackingSlipID
GROUP BY p.OrderID, p.PackingSlipID, p2.OrderLineItemDetail;


 Whether or not that will work in the long run may depend on your data and how you want to handle certain possible situations.  For example, can you have more than one line on an order with the same CustomPartID?  If so, would the previously shipped quantity be the quantity for all lines on that order with that same CustomPartID, or just for one specific line on the order?  If the query was grouped on CustomPartID, you'd get the total for all lines with that CustomPartID.  With it grouped on OrderLineItemDetail, you will (I think) get the total for that specific line on the order.

 James

Author

Commented:
Thank You!  Works great.
I'm glad I could help, but I really think Lowfatspread should get some of the credit (and points) here.  He came up with the original idea and query.  I just took his query and made some corrections and modifications.  Honestly, I doubt that I would have even attempted this if he hadn't provided a starting point.  I haven't done much with Access, so I wouldn't have really known where to start.  He gave me something to work on (which is actually kind of a specialty of mine).

 If you like, you can ask to have the question re-opened (use the "Request Attention" link?) and then split the points between us.  FWIW, I'm fine with a 50/50 split.

 James