SQL error

    Hello


    I am trying to count the number of unique invoices using the following SQL in a query.


    SQL
    SELECT Count([InvoiceNo]) AS Uniques, [Invoice Log Table].[Batch Number]
FROM [Invoice Log Table] INNER JOIN [IFIS Receipt] ON [Invoice Log Table].ID = [IFIS Receipt].InvoiceID
WHERE ((([Invoice Log Table].[Batch Number]) Is Not Null));


    I get the following error message:
    You tried to execute a query that does not include the specified expression Batch Number as part of an aggregate function. (Error 3122)


    Any help much appreciated

    Re: SQL error


    Thanks Shark,


    I'm not exactly sure to what you mean, fairly new at access. Can you plz clarify where the Aggregate Function is changed?

    Re: SQL error


    OK.


    Create a new query in design view and select the tables that you want with the correct join.
    Right click on the grid and select Totals.
    A new row appears in teh grid with a selection of functions.
    Add the Batch number field and select Group By
    Add Invoice number and select count.


    This will return a query that counts the number of invoices per batch.


    However, I have just re-read your original post. If you want to know how many unique invoice numbers there are then create a select query that returns the invoice number only and then edit the SQL by adding the word Distinct after the Select keyword. The number of records returned will be the number of invoices.


    I think that with these two methods you should be able to find the invoice numbers.


    Good luck,


    I am off home now so will check tomorrow!


    A.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login