SELECT DISTINCT help...Please

[Adam1979]

Ok , I will try and explain this as best I can.
I have a database and would like to run a DISTINCT query ( which I can do no problems ) I would also like to count the number of records at the same time. Say for example my database looked like this

Name Fruit

Adam Apple
Carrie Apple
Hollie Apple
Joe Apple
Adam Orange
Carrie Orange
Hollie Orange
Joe Orange

What I would like it to do is Select the distinct names and list them once ( I can do this bit )
Adam
Carrie
Hollie
Joe

I would then like to count the number of records found for each name and put it next to the relevant name

for example Adam (2) (one apple and one orange)

How would I write the actual query
I hope this makes sense.

Thanks in advance

Adam
PS , great site btw

 

[SRAB]

Try this

SELECT NAME, Count(NAME) AS [CountOfNAME]
FROM [NAME TABLE]
GROUP BY NAME;

 

[Adam1979]

Thanks for the super quick reply. I cannot get it to work though. It says too few parameters. The actual table is called CONS , I need to select Username from cons but only list distinct usernames ( there are only 11 in my database but there are 1600 records ) , Then count the amout of records for each distinct username

Then display them all together. So below I have 5 distinct usernames. But they appear more than once in the table. So I need to be able to count number of times each username appears , and put the number next to the username

username1 ( 15 ) records
username2 ( 16 ) records
username3 ( 57 ) records
username4 ( 85 ) records
username5 ( 150 ) records

 

[SRAB]

That is what the query does; it would help if you posted the query you used.

 

[Adam1979]

Thasts the problem , I cannot post the query as I dont know which query to use. Currently I have

SELECT DISTINCT accountname2
FROM cons

This lists the 11 account names I have

The table (cons) has 1600 records. Each of these records has an account name associated with it . I need to be able to list each accountname and then display the number of times each one appears in the database.

each account name appears more than once which is why I have used the distinct query.

It is difficult for me to explain but I hope this helps

So for example
the distinct query returns the 11 distinct account names

I then want it to count the amount of times each account name appears and then display it next to the account name in the for of a number.

 

[SRAB]

Ok, I gave you a query to use...and you said it didn't work. Try using it. Select distinct is not the way to get your results.

 

[CraigDolphin]

For clarity, Adam: you will need to modify SRAB's query to substitute the equivalent field names and table names of your actual fields and tables, since they appear to be different than in the example you posted.

 

[Adam1979]

I LOVE YOU

Im sorry , has been a long day and I have spent the last 24 hours trying to work it out in my head. The problem is that I use dreamweaver which normally writes in the querys via wizard type options. I have changed it as suggested and it works perfectly. Sorry If I was starting to get on everyones nerves a bit.

Now , Just as an Idea but tell me to go jump if you think im talking the mick a bit ,is there an easy way to convert the number results into a bar chart/ graph ? so the higher the number , the longer the bars etc

Thank you both sooooooooooo much for your help , I guess I should learn how do do this stuff but its easier and quicker using wysiwyg type software.

Adam

 

[SRAB]

Less and less people know the code behind the pictures...that's good for us dinosaurs that know the words...

I would export the results to Excel to make the chart using the chart wizard. Then you can make a picture out of the chart.

 

[Adam1979]

The bar chart would have to be dynamic as the numbers change daily

 

[CraigDolphin]

In Access, on a form in the same db as your query, insert a chart object (Insert Menu>Chart). Follow the wizard instructions and base the chart on the query you've just made. Use the name as x-axis items, and the count field as the value itme. Choose bar chart type, obviously.

Save your form. Just open the form any time you need to see the chart.