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];

 

[Ben2k]

Thankyou

 

[Ben2k]

HELP!!

Okay i've copied this query and added a couple more fields from another table. I've selected group by for them. The new SQL looks like this

SELECT Max(CINVOICE.Date) AS MaxOfDate, CARS.[Stock Number], CUSTOMER.Name, CARS.Model, CARS2.Buyer, CARS2.SalesPerson AS Seller, CINVOICE.Cost, CINVOICE.CarPrice, [cinvoice.carprice]-[cinvoice.cost] AS Profit
FROM (CUSTOMER INNER JOIN (CINVOICE INNER JOIN CARS ON CINVOICE.[Stock Number]=CARS.[Stock Number]) ON CUSTOMER.Reference=CINVOICE.InvoiceTo) INNER JOIN CARS2 ON CINVOICE.[Stock Number]=CARS2.[Stock Number]
GROUP BY CARS.[Stock Number], CUSTOMER.Name, CARS.Model, CARS2.Buyer, CARS2.SalesPerson, CINVOICE.Cost, CINVOICE.CarPrice, [cinvoice.carprice]-[cinvoice.cost], CINVOICE.Type, CARS.Type, CARS.Status
HAVING (((Max(CINVOICE.Date)) Between forms!frm_input!datefrom And forms!frm_input!dateto) And ((CINVOICE.Type)="invoice") And ((CARS.Type)="trade") And ((CARS.Status)="sold"))
ORDER BY CARS.[Stock Number];

Problem is the duplicate stock numbers are back, can anyone see what is wrong by looking at the SQL?

 

[Norie]

added a couple more fields

This is probaly causing the problem.

 

[Ben2k]

Why should it cause problem when "group by" is selected, I thought it wouldn't matter if you have 2 or 10 fields in the query, or have I understood incorrectly?

Cheers
Ben

 

[Joe4]

When you are dealing with "Grouping" and such, adding more fields can add more criteria to group on, thus "ungrouping" that which you already grouped.

My advice is to take it back to the point where it was working. Then create a NEW query, which uses this old query and any other queries/tables you need to add in your new fields.

 

[Joe4]

It is important to understand how grouping works. Let's look at your original example:

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

If you have a query where you have the following fields (and the following Totals line for each field):

Cinvoice.Type (Group by)
StockNumber (Group by)
Date (Max)

This will return 4 rows like we had worked through earlier. However, if we had:

Cinvoice.Type (Group by)
StockNumber (Group by)
Date (Group by)

This would return all 5 original lines because of the different dates.

Think of it this way, each field with the "Group By" under it is treated as an AND expression, so the grouping is down on all records that have ALL those fields exactly the same. If you add more fields with the "Group By" expression, and those fields have different values, it will probably lead to more records being ungrouped because the don't match ALL fields of other records.

 

[Ben2k]

Thanks

Okay I know my first query works, it returns 51 records.

Have followed your advice and created in new query, from the old query I've added the following fields

query1.date
query1.stocknumber

When I run this query it returns 58 records (i.e. duplicates)

I've added totals line to the second query and specified MAX for date and group by for stock number. That corrects the problem and 51 records are returned.

Now I want to add other fields the the second query from other tables. So I add customer.name and select group by

Now 52 records are returned - i.e. one is duplicated, do I need to select something other than group by for the 3rd field?

 

[Joe4]

I think you misunderstood my directions:

I've added totals line to the second query and specified MAX for date and group by for stock number. That corrects the problem and 51 records are returned.

OK, stop this query here. Do NOT add another field to it. Let's call this query QueryX.

Now, create a brand new query. Select QueryX as one of the tables/queries. Then add your other table/query with the customer.name field. Make sure you are linking from QueryX to your new query (under properties it should say "Select ALL fields from QueryX and only those that match from query/table _."

Now select the fields that you want to display.