group by + count problem:

[b. hotting]

Hi,

I would like to count inspections and group them by customer (for those
where the repdate = #1/1/1900#)

Something like:
SELECT customerid, count() AS Inpections
FROM inspection
WHERE repdate = #1/1/1900#
GROUP BY customerid;

this works fine!
But now i would like the customer name instead of the ID
so i have to join in another table called Customer to find the CustomerName
with the right CustomerID
so i thought it would work like:

SELECT i.customerid, c.customername, count(i.repdate) AS Inpections
FROM inspection AS i, customer AS c
WHERE i.repdate = #1/1/1900#
and i.customerid = c.customerid
GROUP BY i.customerid, c.customername;

when i run this querry, the counts are not correct, (much to high) seems
like it also counts the iteration for the joined table or so.
anybody??

thanks a lot!
bjorn

[Madhivanan]

Try without Selecting customerid and group by customerid

Madhivanan

[b. hotting]

sorry, not working,

same result,
the cout is too high

"Madhivanan" <ma************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Try without Selecting customerid and group by customerid

Madhivanan

[Madhivanan]

Can you post sample data with expected result?

Madhivanan

[b. hotting]

hi Madhivanan & others,

you can download an Occess sample db from www.SmartColourSensor.com/db1.mdb
(160Kb)
see querry 1 & 2 (and the tables)
they should give the same result, but don't

Reason is that in the customer table, 1 customer has multiple rows because
of branches.

thanks!
bjorn

"Madhivanan" <ma************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Can you post sample data with expected result?

Madhivanan

[Bob Quintal]

"b. hotting" <bh******@REMOVtHISwxs.nl> wrote in
news:d7**********@reader13.wxs.nl:

hi Madhivanan & others,

you can download an Occess sample db from
www.SmartColourSensor.com/db1.mdb (160Kb)
see querry 1 & 2 (and the tables)
they should give the same result, but don't

Reason is that in the customer table, 1 customer has multiple
rows because of branches.

thanks!
bjorn

You can fix this two ways, the right way is to split your
customer table into customers and customerbranches, or you can
replace the table in your query with a new query that does a
SELECT DISTINCT customerID, customername.
If you do it the first way, you now have a customerbranchID to
do even more sophisticated reporting.

--
Bob Quintal

PA is y I've altered my email address.