Having trouble creating a report using data from multiple tables.

[Fizban99]

I have four tables, with the following relevant fields.

tblCustomer
AlphaID

tblJob
JobNumber

tblOrder
QuantityReceived
QuantityShipped
QuantityScrapped
Quantity

tblReceive
ReceiveDate


I would like to create a report that includes the most recent date parts were received (ReceiveDate), the total number of parts received, shipped, and scrapped for this order (QuantityReceived, QuantityShipped, and QuantityScrapped), and the total number of parts for this order (Quantity). This reports should be grouped by the AlphaID and JobNumber fields.

The problem is that I only want each job number to show up once, and since there can be several orders with the same job number, I use the sum function to add all the quantities together. However, when there are multiple entries in tblReceive for an order, my quantity sums are always too large.

I'm not quite sure how to proceed here, any help would be appreciated.

 

[hneal98]

1st, you did not list the field(s) that relate each of the tables (Key). Next, can you list some of the data in your tables and the results you expect to get back?

Thanks.

 

[Fizban99]

Primary keys:
tblCustomer: CustomerID
tblJob: JobID
tblOrder: OrderID
tblReceive: ReceiveID

Relations:
All of the relations are by the keys listed above.
tblCustomer has a one to many relation to tblJob
tblJob has a one to many relation to tblOrder
tblOrder has a one to many relation to tblReceive

Sample data:

tblCustomer
AlphaID CustomerID
ACI 2

tblJob
CustomerID JobNumber JobID
2 RP-1229 232
2 RP-1420 504
2 RP-1456 565

tblOrder
JobID QuantityReceived QuantityShipped
232 85736 82870
504 17063 4786
565 102391 97512
565 7877 0

Quantity OrderID
85736 20174
17063 20314
102391 20056
7877 20298

tblReceive
OrderID ReceiveDate
20174 7/24/03
20174 8/8/03
20174 8/11/03
20314 8/19/03
20314 8/20/03
20056 6/27/03
20056 7/29/03
20298 8/18/03


This is what I would like to end up with:

Cust Job No. Date Rec. Qty Rec. Qty Shp. Qty
ACI RP-1229 8/11/03 85736 82870 85736
ACI RP-1420 8/20/03 17063 4786 17063
ACI RP-1456 8/18/03 110268 97216 110268


Hope this helps.

 

[pdldavis]

Hi, you can construct a query similar to the one below and get your desired results if you leave out the recieve dates. Basically, group on the customer & job number and sum the quantities. Use View-Totals from the dropdown selection for the query in design view.

SELECT tblCustomer.AlphaID, tblJob.JobNumber, Sum(tblOrder.QtyRec) AS SumOfQtyRec, Sum(tblOrder.QtyShip) AS SumOfQtyShip1, Sum(tblOrder.Qty) AS SumOfQty
FROM ((tblCustomer INNER JOIN tblJob ON tblCustomer.CustomerID = tblJob.CustomerID) INNER JOIN tblOrder ON tblJob.JobID = tblOrder.JobID) INNER JOIN tblRecieve ON tblOrder.OrderID = tblRecieve.OrderID
GROUP BY tblCustomer.AlphaID, tblJob.JobNumber;

Hope that helps

 

[Fizban99]

I've managed to get correct results without using the receive dates, but I don't really have a choice about including them. They have to be there, and that's what's been giving me trouble.