Help with query

[Ben2k]

I have built the query below with the query builder, it works listing all the records I specified - however,

A couple of problems, if I car has been invoiced, credited then re-invoiced there will be two invoices in the table both will have the same stock number so how can I group these into one record?

Second problem is rather than a list I want a count of the number of records, i've been playing around with this in query builder for a while now and keep getting an error message about "aggregate functions"

Pls help!

SELECT CINVOICE.Type, CINVOICE.Date, CARS.[Stock Number], CARS.Type, CARS.Status
FROM CINVOICE INNER JOIN CARS ON CINVOICE.[Stock Number] = CARS.[Stock Number]
WHERE (((CINVOICE.Type)="invoice") AND ((CINVOICE.Date) Between [from] And [to]) AND ((CARS.Type)="trade") AND ((CARS.Status)="sold"))
ORDER BY CARS.[Stock Number];

 

[Joe4]

You can use Aggregate Functions in the query builder to group records, and to Count, Sum, etc.

In the query builder, if you click on the Totals button (looks like a Sigma), it will add a "Total" row to the query builder, with the default value of "Group By" for each field.

Let's say you simply wanted to list all the different Invoice Numbers (only once), but list how many times they occur and want a total of the Amounts associated with that Invoice Number. Then start a new query where you add the Invoice Number field two times, and the Invoice Amount field once.

Then click on the Totals button. Leave the first Invoice Number field set to "Group By". Under the second Invoice Number field, select Count under the Totals row. Then in the Invoice Amount field, select Sum.

For more information, enter the expression in the Access Help window: "About designing a query" and take a look at the "Calculate Amounts" drop down.

 

[Ben2k]

Okay then, so my situation is as follows. The query results look a little something like this

Cinvoice.Type Date StockNumber Cars.Type Status

INVOICE 1/7/2004 1670 TRADE SOLD
INVOICE 7/7/2004 2152 TRADE SOLD
INVOICE 13/7/2004 2152 TRADE SOLD
INVOICE 16/7/2004 1470 TRADE SOLD
INVOICE 22/7/2004 1878 TRADE SOLD

The second and third records both have the same stock number 2152, this is because on 7/7/2004 the car was invoiced but then it was credited in effect cancelling the invoice, then on the 13/7/2004 it was re-invoiced.

The purpose of this query is to calculate the number of TRADE deals in a month so I dont want to count the first one as the deal never actually happened.

So I need to modify this query so that if a stock number appears more than once it is only counted once, and instead of a list of deals I simply want the number - i.e. 4 in this example.

Can you tell me what I need to put in the totals box for each field

CINVOICE.Type
CINVOICE.Date
CARS.Stock Number
CARS.Type
CARS.Status

(CARS and CINVOICE are linked by stock number)

Thanks
Ben

 

[Joe4]

Use "Group By" for all of them except Date. Change that one to "Max"

 

[Ben2k]

Okay thanks, that gets rid of the duplicate stock numbers, how do I now get a count of the number of records

Ben

 

[Joe4]

Can you more specific as to exactly what it is you are counting? Are you just wanting to count the total numbers of rows after we remove the duplicates for the purpose of a report or form?

 

[Ben2k]

Yes I want the total number of records after we remove the duplicates.

I have been able to do it using a second query which queries the first i.e.

SELECT COUNT (*) FROM query1

Can it be done in one single query?

 

[Joe4]

If you want to place the count on a report or form based on this query, go to the Footer section of the report/form, insert a text box, and then build the Count expression in the Control Source of the text box, i.e.

=COUNT([QueryName]![FieldName])

Since you are just counting the rows, you can select any of the fields to count.

 

[Ben2k]

Okay thats great, many thanks for your help.

Out of curiosity how does the GROUP BY and MAX functions work in this case.

We've selected group by for everything but date, so for example why does it not return only one record as all records have INVOICE in the cinvoice.type field?, also the cars.type field is always TRADE

Also, what is the MAX used for on date?

Ben

 

[Joe4]

What is the "Group By" does is combine all the records that have the same values in ALL the fields with "Group By" under their name. It is sort of like Excel's Subtotaling functionality.

The "Max" option selects the largest value for all the records for that grouping. For stock # 2152, there are two dates which were grouped, and it returned the largest one.

If you had chosen the "Count" function instead of "Max", it would have returned "2" for stock #2152 and "1" for all the rest. This is a great way to identify duplicate records. I use it a lot.

"Sum" is another one I use a lot.