Combining Line Items From Query onto a Form (1 Viewer)

[Shipper225]

Ok so I've been put in charge of another database, this one was built by someone else and I have been trying to create a new form based off the programmers tables and queries. The problem I have run into is that I have 1 shipment with 10 line items to it so on my form it displays that way. Is there a way I can modify the query to show 1 line for the shipment with the total units? Attached is a PDF of the form I am working with since the database is to large to attach right now. Oh and I am doing this in Access 2007

 

[GinaWhipp]

Not with the Ship Date unless there is only one Ship Date. Look at using a *Groups and Totals* query.

 

[Shipper225]

There is only 1 date per shipment but can have a few shipments on one day. The biggest issue I am running into is the fact that each shipment has a few line items and that is what makes them show up on the report more then once.

 

[jdraw]

Do you really have to show line items?

 

[Shipper225]

My boss would like to see the total pieces shipped out on each shipment but we don't need to see the actual break down of how many pieces per line item

 

[vbaInet]

Like Gina mentioned, look into Groups and Totals query. -->

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

What fields do you want displayed with the Total?

 

[Shipper225]

I looked into that link a few days ago but couldn't get it to not list all the line items. The only real fields I need to display are: COR#, JB, Ship Date, Frt Cost, Invoice #, Invoice Paid. My boss would like to see me get a total piece count displayed in the Shipped QTY field if possible.

 

[vbaInet]

I looked into that link a few days ago but couldn't get it to not list all the line items.

So let's see what you tried.

 

[Shipper225]

Ok attached are the relevant tables for this query, I didn't write this I'm just trying to generate a new report since the programmer is no longer with the company. Compressed as a RAR file had to change extension to ZIP

 

[vbaInet]

Not the whole db, just the SQL statement.

 

[Shipper225]

Not the whole db, just the SQL statement.

Just the SQL and support tables attached, whole database is .. well huge by now lol

 

[vbaInet]

I'm sure it is

I can't seem to open your attachment. Open the query in SQL view and copy and paste the SQL statement here.

 

[Shipper225]

Hope this is correct, still learning things with Access 2007

SELECT tblCarrier.Carrier, tblContainerID.Container, tblDepthRecord.ContainerNumber, tblWorkOrders.CORNumber, tblCustomers.Customer, tblWorkOrders.CustomerPONumber, tblCustomers.Extension1, tblCustomers.Extension2, tblModules.HazMat, ("JB-" & [tblWorkOrders.JobNumber] & "-" & [ShipmentNumber]) AS JB, tblWorkOrders.JobNumber, tblModules.Module, tblModules.ModuleDescription, tblDepthRecord.ModuleNumber, tblCustomers.Phone1, tblCustomers.Phone2, tblDepthRecord.SealNumber, tblDepthRecord.ShipDate, tblDepthRecord.ShipmentNumber, tblCustomers.ShipToAdd1, tblCustomers.ShipToAdd2, tblCustomers.ShipToAdd3, tblCustomers.ShipToCity, tblCustomers.ShipToState, tblCustomers.ShipToZip, tblTermsConditions.TermsConditions, tblDepthRecord.UnitsShipped, tblDepthRecord.Weight, tblDepthRecord.FrtCost, tblDepthRecord.InvoiceNumber, tblDepthRecord.Paid
FROM ((((tblDepthRecord INNER JOIN tblModules ON tblDepthRecord.ModulesID=tblModules.ModulesID) INNER JOIN tblCarrier ON tblDepthRecord.CarrierID=tblCarrier.CarrierID) INNER JOIN ((tblWorkOrders INNER JOIN tblCustomers ON tblWorkOrders.CustomerID=tblCustomers.CustomerID) INNER JOIN tblWorkOrderDetails ON tblWorkOrders.WorkOrdersID=tblWorkOrderDetails.WorkOrdersID) ON tblDepthRecord.WorkOrderDetailsID=tblWorkOrderDetails.WorkOrderDetailsID) INNER JOIN tblTermsConditions ON tblWorkOrders.TermsConditionsID=tblTermsConditions.TermsConditionsID) INNER JOIN tblContainerID ON tblDepthRecord.ContainerID=tblContainerID.ContainerID
GROUP BY tblCarrier.Carrier, tblContainerID.Container, tblDepthRecord.ContainerNumber, tblWorkOrders.CORNumber, tblCustomers.Customer, tblWorkOrders.CustomerPONumber, tblCustomers.Extension1, tblCustomers.Extension2, tblModules.HazMat, ("JB-" & [tblWorkOrders.JobNumber] & "-" & [ShipmentNumber]), tblWorkOrders.JobNumber, tblModules.Module, tblModules.ModuleDescription, tblDepthRecord.ModuleNumber, tblCustomers.Phone1, tblCustomers.Phone2, tblDepthRecord.SealNumber, tblDepthRecord.ShipDate, tblDepthRecord.ShipmentNumber, tblCustomers.ShipToAdd1, tblCustomers.ShipToAdd2, tblCustomers.ShipToAdd3, tblCustomers.ShipToCity, tblCustomers.ShipToState, tblCustomers.ShipToZip, tblTermsConditions.TermsConditions, tblDepthRecord.UnitsShipped, tblDepthRecord.Weight, tblDepthRecord.FrtCost, tblDepthRecord.InvoiceNumber, tblDepthRecord.Paid
ORDER BY tblWorkOrders.CORNumber, tblDepthRecord.ShipmentNumber;

 

[vbaInet]

Ok, you are using far too many fields for you to get distinct row counts. So I would advise you start with the following fields:

COR#, Job No, Shipped Qty

Then you can Sum on Shipped Qty.

 

[Shipper225]

Works beautifully, thanks

 

[vbaInet]

Happy to hear!

So I hope you understand what's going on there. If you want to break it down further you can add Ship Date. And if you want to go even further, add another field... etc.

 

[Shipper225]

The only problem I run into now is that it wont let me make any edits to the form. I get the message that the RecordSet is not updateable

 

[vbaInet]

Yep, that's how queries with aggregates work.

Naturally you would display a breakdown in a report.

If you want it to be updateable then you need to use the DSum() function or a subquery.

 

[Shipper225]

Ah ok, I think I'm starting to get it now, I need to do a DSum() so that I can edit the data in the 3 fields I need then

 

[vbaInet]

That's correct. So you move away from using the aggregate query completely.