Problem with Sum Query

[rhett7660]

Hello..

I have a report that is based of a query. In the query I have the following calculation:

total: ([quanity]*[packagecost])

I have also been able to group the like products, but it does not add the quanity correctly.

Here is the sql of the query:

SELECT tblOrder.OrderID, tblOrder.OrderNumber, tblOrder.OrderDate, tblOrder.VendorID, tblLUVendor.VendorCompany, tblLUVendor.StreetAddress, tblLUVendor.City, tblLUVendor.PhoneNumber, tblLUVendor.FaxNumber, tblLUVendor.VendorNumber, tblLUVendor.AgreementNumber, tblOrder.ObjectCode, [City] & " " & [StateAbbreviation] & ". " & [ZipCode] AS FullAddress, tblLUVendor.ContactPerson, qrytblProduct.ProductID, qrytblProduct.Quanity, qrytblProduct.PackageCost, qrytblProduct.ProductNumberID, qrytblProduct.ItemDescription, qrytblProduct.ProductNumber, qrytblProduct.Packaging, [quanity]*[packagecost] AS total, qrytblProduct.Item, qrytblProduct.UnitID, qryUnit.Unit
FROM (((tblOrder LEFT JOIN tblLUVendor ON tblOrder.VendorID = tblLUVendor.VendorID) INNER JOIN qrytblProduct ON tblOrder.OrderID = qrytblProduct.OrderID) LEFT JOIN qryStates ON tblLUVendor.State = qryStates.ID) INNER JOIN qryUnit ON qrytblProduct.UnitID = qryUnit.UnitID
GROUP BY tblOrder.OrderID, tblOrder.OrderNumber, tblOrder.OrderDate, tblOrder.VendorID, tblLUVendor.VendorCompany, tblLUVendor.StreetAddress, tblLUVendor.City, tblLUVendor.PhoneNumber, tblLUVendor.FaxNumber, tblLUVendor.VendorNumber, tblLUVendor.AgreementNumber, tblOrder.ObjectCode, [City] & " " & [StateAbbreviation] & ". " & [ZipCode], tblLUVendor.ContactPerson, qrytblProduct.ProductID, qrytblProduct.Quanity, qrytblProduct.PackageCost, qrytblProduct.ProductNumberID, qrytblProduct.ItemDescription, qrytblProduct.ProductNumber, qrytblProduct.Packaging, [quanity]*[packagecost], qrytblProduct.Item, qrytblProduct.UnitID, qryUnit.Unit
HAVING (((tblOrder.OrderNumber)=[Forms]![frmDataEntry]![OrderNumber]));

I have attached the database...

The report in question is rptInvoice and the query is qryInvoice. The adding up works correctly on the form... frmDataEntry

Thanks
R~

 

[Brianwarnock]

To me it seems that the total is correct , its the listing that's wrong, i.e. the total agrees with the query but there are 2 lines missing. Order SHER12345.

But cannot figure out why, mind you its difficult working with a system that is set up for the users rather than for diagnostics ie switching between prewview and design, having the Form open and accessing the query or report all seem difficult.

Brian

 

[rhett7660]

I should take at the macro part for right now.. but if you hold down the shift key..... is that what you are referring to?

 

[rhett7660]

I have upped a version without the macro..

 

[Brianwarnock]

I think the problem is that you have put the detail rows into the Group Footer section rather than the detail section, the result is that only one record per group is displayed, but the SubTotal is that of all records.

Brian

 

[rhett7660]

Brian..

I wanted it to group like items...... that part is right.. but it isn't adding up the quanity correctly....

IE query:

Order # 12345GG

Part# Quanity Price total
123 2 $1.00 $2.00
123 1 $1.00 $1.00

Report (what I would like for it to read)

Order #12345GG

Part# Quanity Price Total
123 3 $1.00 $3.00


but I am getting this:

123 3 $1.00 $5.00

Does this make sense? The quanity is not adding up correctly..... So I am getting the right total price but not the correct total quanity...

 

[Brianwarnock]

I cannot see that order number but on SHER12345 or VB,erm forgotten the rest, I'm not getting that problem, only the missing rows as mentioned earlier.

I would be thinking of doing my grouping in a query and sending 1 record per group to the report detail section, and then the sub and grand totals in the report footer as now, You don't actually use the grouping in the report to do anything as far as I can see.

Brian

 

[rhett7660]

I am posting a screen shot of what I am talking about.....

Invoice screen and total from form...

 

[rhett7660]

and form screen..

 

[Brianwarnock]

THe views you show are exactly what I see, and as i said in my first post it is because two rows are missing from the display and as i suggested in post#7 that is because in the group footer only one row can be displayed therefore I suggested in #9 a different approach, I can add no more only to remind you that the totals in the report footer go back to the query source for their information, that is why they are correct.

Brian

 

[rhett7660]

Brian...

I grouped the items... so that I wouldn't have the ordered items repeating on the invoice...

I am working on trying the different groupings.... Not have to much luck.. but I am working on it....

Thank you for your help...

 

[Brianwarnock]

Hi I think I’ve cracked it, but it requires a couple of new queries
The first computes the totals of quantity and Cost per Group
Note that my query names are a bit whacky

qrybjwtots

SELECT qryInvoice.ProductNumber, Sum(qryInvoice.Quanity) AS SumOfQuanity, Sum(qryInvoice.total) AS SumOftotal
FROM qryInvoice
GROUP BY qryInvoice.ProductNumber;

The second then constructs the summary detail row.

Qrybjwtots2
SELECT qrybjwtots.ProductNumber, qryInvoice.ItemDescription, qrybjwtots.SumOfQuanity, qryInvoice.Packaging, qrybjwtots.SumOftotal, qryInvoice.OrderNumber
FROM qrybjwtots INNER JOIN qryInvoice ON qrybjwtots.ProductNumber = qryInvoice.ProductNumber
GROUP BY qrybjwtots.ProductNumber, qryInvoice.ItemDescription, qrybjwtots.SumOfQuanity, qryInvoice.Packaging, qrybjwtots.SumOftotal, qryInvoice.OrderNumber
HAVING (((qryInvoice.OrderNumber)=[Forms]![frmDataEntry]![OrderNumber]));

The third then brings in all the non detail info for the header.

Qrybjwtot3

SELECT qrybjwtots2.ProductNumber, qrybjwtots2.ItemDescription, qrybjwtots2.SumOfQuanity, qrybjwtots2.Packaging, qrybjwtots2.SumOftotal, qrybjwtots2.OrderNumber, qryInvoice.OrderDate, qryInvoice.VendorCompany, qryInvoice.StreetAddress, qryInvoice.City, qryInvoice.PhoneNumber, qryInvoice.FaxNumber, qryInvoice.VendorNumber, qryInvoice.AgreementNumber, qryInvoice.ObjectCode, qryInvoice.FullAddress, qryInvoice.ContactPerson
FROM qrybjwtots2 INNER JOIN qryInvoice ON qrybjwtots2.ProductNumber = qryInvoice.ProductNumber
GROUP BY qrybjwtots2.ProductNumber, qrybjwtots2.ItemDescription, qrybjwtots2.SumOfQuanity, qrybjwtots2.Packaging, qrybjwtots2.SumOftotal, qrybjwtots2.OrderNumber, qryInvoice.OrderDate, qryInvoice.VendorCompany, qryInvoice.StreetAddress, qryInvoice.City, qryInvoice.PhoneNumber, qryInvoice.FaxNumber, qryInvoice.VendorNumber, qryInvoice.AgreementNumber, qryInvoice.ObjectCode, qryInvoice.FullAddress, qryInvoice.ContactPerson
HAVING (((qrybjwtots2.OrderNumber)=[Forms]![frmDataEntry]![OrderNumber]));

I changed your report source to the third and I think it works , but note that several fields on the report need the source fields changing,eg quantity and totals have changed.

Best of luck

Brian

 

[rhett7660]

Brian..

Thank you very much.. I am working on it as we speak....... using the new queries you made...

Thanks again
R~

 

[Brianwarnock]

Oh dear, actually what I said was not printable but having spent hours on this the other day the penny dropped this morning, the problem I think is that a product can have many "Unit Price" and thus it will generate a record/line of print per Unit Price (package cost), I don't know what I did when I thought my queries worked because I've messed them up trying to improve them , but anyway I leave you with the comment above.


Brian

 

[rhett7660]

Oh lord.. I am back at work now trying to figure this out and then I read your post...lol.. it is going to be a long week...lol...

 

[rhett7660]

Anybody else care to take a shot at this...... I am tapped... And I think Brian is ready for a much needed vacation ! I am looking for it to just add up the quanity correcly in the report....... which is based of a query.... there has got to be an easier way of accomplishing this..

 

[rhett7660]

Well I think I figured it out..... Probably not the most effecient way, but it works.

What I did was grouped them by productnumber in the productnumber header, the created a text box to sum up my quanity inthe productnumber footer, then called the sumquantiy in the productnumber header.. put in the following formula in the totals box: =sum([quanity})*([PackagePrice)].

Works like a charm...



Keywords: sum, quanity, count