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?"
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?"
create a separate access query
select top 1 p.orderno,p.packslipno,sum
from t-oackingslipdetail as p
inner join t-[ackingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
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
select top 1 p.orderno,p.packslipno,sum
from t-oackingslipdetail as p
inner join t-[ackingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
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
Thanks - I'm goign to need a litle more help implenting this soluition.
Q1 - where do I add the first paragraph of code above?
Q1 - where do I add the first paragraph of code above?
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
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
the p1 packingslipdetail was misspelt.
select top 1 p.orderno,p.packslipno,sum
from t-oackingslipdetail as p
inner join t-packingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
group by p.orderno,p.packslipno
order by 1,2 desc
select top 1 p.orderno,p.packslipno,sum
from t-oackingslipdetail as p
inner join t-packingslipdetail as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
group by p.orderno,p.packslipno
order by 1,2 desc
duh! and the p
select top 1 p.orderno,p.packslipno,sum
from [t-packingslipdetail] as p
inner join [t-packingslipdetail] as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
group by p.orderno,p.packslipno
order by 1,2 desc
select top 1 p.orderno,p.packslipno,sum
from [t-packingslipdetail] as p
inner join [t-packingslipdetail] as p1
on p.orderno=p1.orderno
and p1.packslipno<=p.packslipn
group by p.orderno,p.packslipno
order by 1,2 desc
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?
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?
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?
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?
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
eetemp.mdb
I saved your SQL query as q-Report-SQL
SELECT [q-Report-PackingSlip].Ord
FROM [q-Report-SQL] LEFT JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].OrderID = [q-Report-PackingSlip].Ord
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].Ord
FROM [q-Report-SQL] Right JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].OrderID = [q-Report-PackingSlip].Ord
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].Ord
FROM [q-Report-SQL] Right JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].OrderID = [q-Report-PackingSlip].Ord
Here is my current query:
SELECT [q-Report-PackingSlip].Ord
FROM [q-Report-SQL] RIGHT JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].packslipno
I am still being prompted to enter 5 piece of information?
p.orderno
p.packingslipno
p1.qty
p1 packingslipno
p1.orderno
SELECT [q-Report-PackingSlip].Ord
FROM [q-Report-SQL] RIGHT JOIN [q-Report-PackingSlip] ON ([q-Report-SQL].packslipno
I am still being prompted to enter 5 piece of information?
p.orderno
p.packingslipno
p1.qty
p1 packingslipno
p1.orderno
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.packslipn
This query seems to produce the correct results with the data in the db you posted.
SELECT p.OrderID, p.PackingSlipID,sum(p2.Shi
FROM ([t-PackingSlips] AS p
INNER JOIN [t-PackingSlips] AS p1
ON p.OrderID=p1.OrderID
and p.PackingSlipID>p1.Packing
INNER JOIN [t-PackingSlipsDetail] AS p2
ON p1.PackingSlipID=p2.Packin
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
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.packslipn
This query seems to produce the correct results with the data in the db you posted.
SELECT p.OrderID, p.PackingSlipID,sum(p2.Shi
FROM ([t-PackingSlips] AS p
INNER JOIN [t-PackingSlips] AS p1
ON p.OrderID=p1.OrderID
and p.PackingSlipID>p1.Packing
INNER JOIN [t-PackingSlipsDetail] AS p2
ON p1.PackingSlipID=p2.Packin
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
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.Packin
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
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.Packin
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial