Subquery Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I pulled data from 4 different tables. This data now needs to be sorted into
months using the Receipt Date if it has one. If there is no Receipt Date,
then we will use the PO Date. If there is no PO Due Date, then we will use
the PR Due Date. If there is no PR Due Date, then we will use the Need Date.

I need to create a report that will look sort of like this:

Month Part No Qty Price
January AAA 2 $50.00
BBB 1 $25.00
Total Cost for January $75.00

February CCC 2 $15.00
Etc.

I was thinking that a subquery might be the answer and tried the following
that does not work. The Select statement works, but not the Where clauses
(It's also in regular SQL format.)

SELECT tblPartDetails.PricePL, tblPartDetails.QtyOrdered, tblParts.PartNo,
tblOrders.PONo, tblOrders.PRNo, tblPartDetails.ReceiptDate,
tblOrderDetails.PODueDate, tblOrderDetails.PRDueDate,
tblCabinetDetails.IWDateN, tblCabinetDetails.Plus60Days
FROM ((tblOrders INNER JOIN tblOrderDetails ON tblOrders.id_PoN =
tblOrderDetails.id_PoN) INNER JOIN ((tblCabinets INNER JOIN tblCabinetDetails
ON tblCabinets.id_Cab = tblCabinetDetails.id_Cab) INNER JOIN tblParts ON
tblCabinets.id_Cab = tblParts.id_Cab) ON tblOrders.id_PoN = tblParts.id_PoN)
INNER JOIN tblPartDetails ON tblParts.id_Part = tblPartDetails.id_Part

WHERE tblPartDetails.ReceiptDate IN

(SELECT tblPartDetails.ReceiptDate
FROM tblOrders
WHERE tblOrders.PODueDate IN

(SELECT tblOrders.PODueDate
FROM tblOrders
WHERE tblOrders.PRDueDate IN

(SELECT tblOrders.PRDueDate
FROM tblCabinetDetails.IWDateN
WHERE tblCabinetDetails.IWDateN = Is Not Null)));

Any ideas on how to do this would be appreciated!!!
 
Create a calculated field and sort on it:

SortDate: IIF(IsNull([Receipt Date]),IIF(IsNull([PO Date]),IIF(IsNull([PR Due
Date]),[Need Date],[PR Due Date]),[PO Date]),[Receipt Date])

I hope I got all the parentheses correct. Anyway, you get the idea.
 
Just wanted to say thanks for your answer. Had to go home sick yesterday, so
I'll try your suggestion today.

kingston via AccessMonster.com said:
Create a calculated field and sort on it:

SortDate: IIF(IsNull([Receipt Date]),IIF(IsNull([PO Date]),IIF(IsNull([PR Due
Date]),[Need Date],[PR Due Date]),[PO Date]),[Receipt Date])

I hope I got all the parentheses correct. Anyway, you get the idea.
I pulled data from 4 different tables. This data now needs to be sorted into
months using the Receipt Date if it has one. If there is no Receipt Date,
then we will use the PO Date. If there is no PO Due Date, then we will use
the PR Due Date. If there is no PR Due Date, then we will use the Need Date.

I need to create a report that will look sort of like this:

Month Part No Qty Price
January AAA 2 $50.00
BBB 1 $25.00
Total Cost for January $75.00

February CCC 2 $15.00
Etc.

I was thinking that a subquery might be the answer and tried the following
that does not work. The Select statement works, but not the Where clauses
(It's also in regular SQL format.)

SELECT tblPartDetails.PricePL, tblPartDetails.QtyOrdered, tblParts.PartNo,
tblOrders.PONo, tblOrders.PRNo, tblPartDetails.ReceiptDate,
tblOrderDetails.PODueDate, tblOrderDetails.PRDueDate,
tblCabinetDetails.IWDateN, tblCabinetDetails.Plus60Days
FROM ((tblOrders INNER JOIN tblOrderDetails ON tblOrders.id_PoN =
tblOrderDetails.id_PoN) INNER JOIN ((tblCabinets INNER JOIN tblCabinetDetails
ON tblCabinets.id_Cab = tblCabinetDetails.id_Cab) INNER JOIN tblParts ON
tblCabinets.id_Cab = tblParts.id_Cab) ON tblOrders.id_PoN = tblParts.id_PoN)
INNER JOIN tblPartDetails ON tblParts.id_Part = tblPartDetails.id_Part

WHERE tblPartDetails.ReceiptDate IN

(SELECT tblPartDetails.ReceiptDate
FROM tblOrders
WHERE tblOrders.PODueDate IN

(SELECT tblOrders.PODueDate
FROM tblOrders
WHERE tblOrders.PRDueDate IN

(SELECT tblOrders.PRDueDate
FROM tblCabinetDetails.IWDateN
WHERE tblCabinetDetails.IWDateN = Is Not Null)));

Any ideas on how to do this would be appreciated!!!
Click to expand...
Click to expand...
 
Back
Top