New Fields based on Query?

[Chris2404]

Hey guys,

Not quite sure how to approach this one but I have a table containing the area, location and name of someone there is also a field contains a number from 0-10. I need to run a query that will count the number of times a number appears in this field and put this total in a field on its own.

To try and help with this my table fields are as follows...

Area Location Name Number

And I would like the query to show....

Area Location Name 10's 9's 8's ect...

Thanks for the help

 

[plog]

Post some sample data from the relevant sources and then what the query should return based on that sample data. Include field and table names, use this format for posting data:

Table1NameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 12, 4/3/2008
Steve, 8, 5/16/2010
Amy, 2, 6/6/2007

 

[Chris2404]

Here is some of the data I could use

CustTable
Area, Location, Name, Satisfaction
Scotland, Edinburgh, Chris, 10
Scotland, Edinburgh, Chris, 10
Scotland, Edinburgh, Chris, 7
Scotland, Edinburgh, Chris, 3
Scotland, Perth, James, 2
Scotland, Perth, James, 10
Scotland, Perth, James, 4
Scotland, Perth, James, 7
Scotland, Perth, James, 0

And the output I would like to be...

Area, Loaction, Name, Satisfaction 10, Satisfaction 9,Satisfaction 8...ect
Scotland, Edinburgh, Chris, 2,0,0,1,0,0,0,1,0,0,0
Scotland, Perth, James, 1,0,0,1,0,0,0,0,1,0,1

So it will display the count of 10s, 9s, 8s and so on, if there is no count in the table it should display 0

 

[plog]

That's a special query called a Cross-Tab query (http://office.microsoft.com/en-us/a...ad-by-using-a-crosstab-query-HA010229577.aspx). Its similar to a pivot table in Excel. Click the link and it should walk you through it.

 

[Chris2404]

Ok thanks for the reply.

I have this working in Excel and im starting to move it to access as running the formulas through Excel is far to slow now.

Thanks for the help