Stuck with simple count query...

[downhilljon]

Hi all,

I've got a simple count query I can't make work properly.

I'm trying to display the number of bikes purchased from each suburb from my extensive database of sold bikes.

I've set up 2 queries (see image1.jpg). The first one works as I'd expect it to - gives me 1758 records (the total number of bikes sold) of lots of repetitive suburbs and states. All good.

The 2nd one isn't working properly though. I can only manage one of 2 scenarios. With the current setup, as shown in image1, I think the count function is working OK, but instead of showing each suburb once with the total count for the sales as I want it to, I still get 1758 records. But the count figure is correct.

If I remove the PurchaseID column and run the query, I now get each suburb only listed once (bringing total records displayed down to 300 and something), but the count value now becomes stupid - some values over 4000. I believe this is the result of summing all the count totals from scenario 1.

Can anyone see something I'm missing?

I've gotten this to work in other parts of the database, and all i can think of that might be causing an issue is that there is no 'SuburbID' - I did not create a table listing all possible suburbs, it is just a text field.

I hope this makes sense, it's late and I'm frustrated

Thanks for looking at this!
Jon

 

[Brianwarnock]

to know the number of bikes sold per suberb wont grouping on suberb and counting customerid give it?

Brian

 

[downhilljon]

Hey Brian,

Thanks for the reply. What you suggested wasn't the solution (each customer can purchase more than 1 bike, so that's why I had purchaseID instead), however, your suggestion prompted me to try some more options. Whatever I did, it now works!

Thanks a lot,
Jon