Please see my SQL query below:

SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.NetItemAmount, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime, sum (inv.InvoiceNumber)

FROM tblInvoicesSubmitted AS inv

WHERE (((DateDiff("d",[DateSubmitted],[DateCheckRequested]))<=15) AND ((inv.[NetItemAmount])<=100))

GROUP BY inv.InvoiceNumber;

When I try to run the query it keeps say that Vendor Name or Date Submitted are not part of the aggregate function? Additionally, I want to group by invoice number so there are not repeats and then I want to count the number of unique invoice numbers after they have been grouped. Please advise on the appropriate SQL syntax. Appreciate the help.

SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Dat

FROM tblInvoicesSubmitted AS inv

WHERE (((DateDiff("d",[DateSubmi

GROUP BY inv.InvoiceNumber;

When I try to run the query it keeps say that Vendor Name or Date Submitted are not part of the aggregate function? Additionally, I want to group by invoice number so there are not repeats and then I want to count the number of unique invoice numbers after they have been grouped. Please advise on the appropriate SQL syntax. Appreciate the help.

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

Group By nv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Dat

Select inv.VendorName,count(inv.I

from

where

group by inv.VendorName

Select inv.InvoiceNumber,count(In

from

where

group by inv.InvoiceNumber

I am assuming there are multiple like invoice numbers. this would group them, count how many per group, and sum the net amount for all the invoice numbers.

so if there were three invoice number and they were all 5 then the results would be

invoice 5 has three invoices with that number and the sum of the net amount is.....

SELECT inv.InvoiceNumber, count(InvoiceNumber) as InvCount, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Dat

FROM tblInvoicesSubmitted AS inv

WHERE NetItemAmount<=100 and DateDiff("d",[DateSubmitte

ORDER BY DateDiff("d",[DateSubmitte

But it says 'InvoiceNumber' is not part of an aggregate function.

You need to group it still. after your where and before your order by

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

Union

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

Union

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

Union

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

UNION

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

group by InvoiceNumber;

0-15 days, where sum (netitemamount) is between 0-100, 101-1000,1001+

15-30 days, where sum (netitemamount) is between 0-100, 101-1000,1001+

30-45 days, where sum (netitemamount) is between 0-100, 101-1000,1001+

SELECT InvoiceNumber,Category,sum

FROM qryUnion

WHERE invsum Between 0 And 100

GROUP BY InvoiceNumber,Category

Union

SELECT InvoiceNumber,Category,sum

FROM qryUnion

WHERE invsum Between 101 And 1000

GROUP BY InvoiceNumber,Category

Union

SELECT InvoiceNumber,Category,sum

FROM qryUnion

WHERE invsum > 1001

GROUP BY InvoiceNumber,Category

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

Union

SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

Group By InvoiceNumber

UNION SELECT InvoiceNumber,Count(Invoic

FROM tblInvoicesSubmitted AS TotalInv

WHERE DateDiff('d',[DateRequeste

group by InvoiceNumber;

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial