Query question

[jerrypeco]

Have an existing Access 97 database. I am trying to create a query (and ultimately a report) that will show line items purchased and a separate freight charge.



I am using a query that pulls records from 3 tables. All is ok except that my freight charges in their field “duplicate” in conjunction with the records obtained.

Order Date	Ship Date	City	Unit Price	Freight	Order Detail ID	Order ID
3/10/2013	3/12/2013	Atlanta	$310.00	$0.00	33418	18810
3/10/2013	3/12/2013	Atlanta	$0.00	$0.00	33417	18810
5/13/2013	5/14/2013	Spokane	$310.00	$280.00	33447	18819
5/13/2013	5/14/2013	Spokane	$0.00	$280.00	33428	18819
5/13/2013	5/14/2013	McAllen	$310.00	$115.00	33437	18820
5/13/2013	5/14/2013	McAllen	$41.41	$115.00	33434	18820
5/13/2013	5/14/2013	McAllen	$0.00	$115.00	33432	18820

The Order ID and freight come from one table and the Freight and the Order Detail and Unit Price come from another. Freight is listed only one time in the Order table but duplicates on the query and naturally the report.

Not sure if this is enough info, but can anyone point me in the right direction?

[JoeM]

Can you post the SQL code of your query (switch query to SQL View and copy and paste code here)?

[jerrypeco]

Can you post the SQL code of your query (switch query to SQL View and copy and paste code here)?

Sure. Here it is:

SELECT Customers.Company, Orders.OrderDate, Orders.ShipDate, Customers.Phone, Customers.FirstName, Customers.LastName, Customers.Address1, Customers.City, Customers.State, Customers.Zip, Orders.[Customer Call Number], Orders.[Call Number], [Order Details].PartNumber, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].OrderDesc, [XXXX Freight Charges].Freight
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN [XXXX Freight Charges] ON Orders.OrderID = [XXXX Freight Charges].OrderID
GROUP BY Customers.Company, Orders.OrderDate, Orders.ShipDate, Customers.Phone, Customers.FirstName, Customers.LastName, Customers.Address1, Customers.City, Customers.State, Customers.Zip, Orders.[Customer Call Number], Orders.[Call Number], [Order Details].PartNumber, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].OrderDesc, Orders.BillToID, [XXXX Freight Charges].Freight
HAVING (((Orders.BillToID)="XXXX"));

"XXXX" is a substitute for a company name. Thank you.

[JoeM]

I do not see any duplicate records. With a duplicate record, ALL fields in a certain record would exactly match the ALL the fields in another record. From the sample you have posted, I do not see that.
Some where along the line, you have a one-to-many relationship in one of your joins. The first step is to identify which relationship it is, and then the next thing to do is to decide how you want to handle it. It should be pretty easy to identify. Basically, look the fields that you are joining the tables on. At least one them is not a unique field (meaning multiple records in that table have the value you are joining on).

Also, in your SQL query, you have more fields in your "Group By" clause than your "Select" clause. That is not typical. You usually do not group on more records than you are returning.

[jerrypeco]

The SQL code is different than the results in the table above. I removed fields from that query so that customer names/locations would no be listed. The SQL code is for the entire query.

I am using 3 tables and a query. I am joining the tables/queries by the following:

Tables
Order Details and Orders
Joined by OrderID (Join Properties - Only include rows where joined fields from both tables are equal)
In each table, OrderID is set up as Indexed - Duplicates OK.


Customers and Orders -
Joined by CustomerID (Join Properties - Only include rows where joined fields from both tables are equal)
In each table, CustomerID is set up as Indexed - Duplicates OK.

Query and Table
Table - Orders
Query - Freight Charges

Joined by OrderID - (Join Properties - Only include rows where joined fields from both tables are equal)

Does this help? Thank you

[JoeM]

I am failing to see what the problem is. I am not seeing any duplicates in your example. Customers could have multiple orders, right? So what exactly is the issue?

Perhaps if you could post a small simple example, where you show the pertinent records and values from each table, and based on that, what you would like your output to look like, it might clarify what you want to happen.

[jerrypeco]

Sure. I have attached a sample report.

The freight total on the right side should appear only once per Cust Call# on left. It comes up on multiple lines and totals incorrectly. Basically I need one freight charge per Cust Call #.

Hope this helps. Thank you.

[JoeM]

It looks like this one slipped through the cracks and I missed your reply. Sorry about that.

In looking it over again, in looking at your original example, why does each Order have two Order Detail ID, one of which always has a value of zero in the Unit Price?
Is it just a matter of filtering those zero Unit Price records out of your query?

If you still need help, it might be beneficial to post a copy of your database, so we can see what the data in each underlying table looks like. Just make sure not to post any sensitive information, if that applies.

[jerrypeco]

The order ID contains line items that are in the order details table. Can be many items in an order. For example, the two lines that contain Atlanta are separate line items within an order. Freight is input in the orders table only and shows up once on an individual invoice, whereas the records in the order details table can show up as many times as a record is required.

I will see if I can strip the sensitive info and send a copy of the DB. Thank you.

[jerrypeco]

Joe

I have attached a sample of my DB that exhibits the same issue that I am having. Any help is greatly appreciated.

Thanks
Jerry

[JoeM]

I will try to take a look at it tonight (due to my company's Corporate Policy, I cannot download files from the internet here, but I can on my home computer).

[jerrypeco]

Thank you!

[JoeM]

I see what is going on. Since there is a "one-to-many" relationship between Orders and Order Details, and Freight appears in the Orders table, it is being repeated many times, once for each making Order Details record.

Here is what you can do. Create a query between just Customers and Orders. Base the Report off of that. Then, create a Subreport of the Order Details (based solely on the Order Details table). Then. embed that Subreport in your main report, joining on the Order ID field.

If you need to do a sum of all the costs, you can do an Aggregate Query on your Order Details table, and include that in the query (that will then have a one-to-one relationship). So you can use that information for your Order totals, and the Subreport just for the details.

Hope that helps!

[jerrypeco]

Thank you! Seems I am pretty close. The report is taking shape.

Just a couple of questions on the above. I will need a sum of the costs in the Order Details subreport. Not sure I am following this statement - "do an Aggregate Query on your Order Details table, and include that in the query". Am I to create an Aggregate Query on the Order Details table and then insert it into query created in the Customers and Orders Query created earlier?

[JoeM]

If you just want to show the totals of the Order Details on the Subreport, you can do it right in there.
If you need to sum the totals of the Subreport with values from the Orders table (i.e. the "Freight" charges"), that is where you may need to create an Aggregate Query to get the total of the Order Details for each Order ID, and include that in your other query with the Customers and Orders so you can add that Subtotal to the Freight charges for each order (I don't know if there is a better way to add the Subtotals from a Subreport to a value on the Main Report, never really tried that, but the way I am proposing should work).

If you do not need to add the Subreport Subtotals to your Freight Charges on your Main Report, then it is a moot point and you don't need to worry about that.