S
saraqpost
I am stumped! I have a query that uses a function to calculate the
total freight for each order.
curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)
The query is 2 simple fields:
POKey and ActualFreight
It runs in about a second as: (No "Group By")
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;
But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;
Any idea why? What should I do?
Thanks
Sara
total freight for each order.
curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)
The query is 2 simple fields:
POKey and ActualFreight
It runs in about a second as: (No "Group By")
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;
But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;
Any idea why? What should I do?
Thanks
Sara