Calculating total values for groups in queries

[Nixx1401]

I have a dbase recording invoice totals for goods and services for vendors. I would like to create a query which provides the total invoice amount for each vendor by date and goods or service.



The below is what I currently have but when I add the invoice date it is not adding the invoice totals

SELECT tblVendor.strVendorName, tblTransactions.[Invoice Type], Sum(tblTransactions.curInvoiceTotal) AS SumOfcurInvoiceTotal
FROM tblVendor INNER JOIN tblTransactions ON tblVendor.strVendorsID = tblTransactions.strVendorID
GROUP BY tblVendor.strVendorName, tblTransactions.[Invoice Type];

Basically I want the total invoice amount for a vendor for a given period and indicating the type whether good or service

[June7]

What happens - error message, wrong results, nothing?

Did you include the date in the GROUP BY?

Build a report using Grouping & Sorting with aggregate calcs. This will allow display of details as well as group totals.

[Nixx1401]

When I add the date field to the query it does not add the invoice totals for each vendor it displays the invoice totals for goods and services individually for each vendor. I wanted to include the date field so you can enter a date parameter to reveal the total invoice amount for a vendor including if it is a good or service rendered or purchase.

I tried using grouping and sum and display summary rather than details. With this one it adds the totals for goods and services for a particular vendor.

[June7]

Does the date include time part?

Do you want to provide project for analysis?

[Nixx1401]

the date does not include time. I want to provide the total cost of goods and services for each vendor yearly and this information has to be exported to xml and emailed to accounts department.

[June7]

I won't be able to figure it out without directly analyzing the data. If you want to provide project, I will look at.

[Nixx1401]

I have attached the dbase

[June7]

If you only need date to determine the year so can summarize by year, need a field with year value. Extract the year from date with an expression. Use this constructed field as a GROUP BY value or to filter records by a year criteria.
InvYear: Year(dteInvoiceDate)

If you need separate totals for Goods and Services, include Type field in grouping. However, you have a query for each Type. Do you want separate reports?

I usually prefer not to do grouping in a query and instead set grouping with Report. However, since you need to export to xml I expect that will have to be from a query and not a report. So if you want a total for each vendor by year and type, don't include full date in the query.