Count total names in a table

[Denisdh]

Hi,
I'm very new to access and only have the basics of SQL. I have a table of Names and the county they are connected to. These names appear multiple times in a county.

I'm looking for a way to count the number of times each name appears and show it beside each name and county in a display of unqiue names.

Can anyone help?

Thanks
Denis

 

[JohnLee]

Hi,

The information you have provided is not very detailed, but nonetheless create a crosstab query using the query wizard, which will walk you through how to set it up. You should then get your desired results.

John

 

[Denisdh]

Thanks for the input JohnLee. I've given that a go and its close to what i want but I'm not sure how to get total?

I'll explain my problem a bit more:

I have a table with a list of Properties. Each property has an ID, Property Agent, Price, Location etc.

All I want is the Property Agent and Location/County. I have these two in a table and I now want to add another column with the total number of times an agent appears in a given property in that column and then get ride of all the duplicates.

After doing the CrossTab Query I now have the layout I want but not the totals e.g. Agent Name, County 1, County 2, County 3.....

 

[JohnLee]

Hi,

Now that's a different picture entirely. Sounds like your using a table like a spreadsheet. You need to normalise your data. a table for your properties where the property only appears once and a table for your agents, where they only appear once.

You would then need to create a linking table, which will contain the primary key from both tables, then you can create a many to many relationship via the linking table, because a property can have many agents and an agent can have many properties.

Once you've done that, you can then start to look at the next phase.

If you can, perhaps you can post a copy of your db, so that I can see more clearly how to help you, providing it's not sensitive that is.

John

 

[Denisdh]

I clicked the wrong field value. When i went back and selected sum it worked fine!

I need to import this data into a .Net Web Service, could anyone tell me if there is a way to write my query to a table or if I can get the query in an SQL statement I can use in the Web Service?

 

[JohnLee]

Hi,

In the Crosstab Design view, from the menu options choose "View" then SQL View, you will have the SQL code your looking for.

John.

 

[lemo]

for edifying purposes, could you please post the SQL code of your query.

thanks,
l

 

[Denisdh]

Got that, thanks. But the compiler doesn't like it.

The SQL query is;
TRANSFORM Count(tblReportData.[Number of Properties]) AS [CountOfNumber of Properties]
SELECT tblReportData.Agent,
Count(tblReportData.[Number of Properties]) AS [Total Of Number of Properties]
FROM tblReportData
GROUP BY tblReportData.Agent
PIVOT tblReportData.County

"Microsoft Jet database engine does not recognize 'tblReportData.[Number of Properties]' as a valid field name or expression" is the error I'm getting.

 

[Denisdh]

Apologies, I had the SQL wrong, it should be;

TRANSFORM Count(tblReportData.ID) AS CountOfID
SELECT tblReportData.Agent, Count(tblReportData.ID) AS [Total Of ID]
FROM tblReportData
GROUP BY tblReportData.Agent
PIVOT tblReportData.County;

And the error in the compiler is;

Server Error in '/AgentReports' Application.
--------------------------------------------------------------------------------
A field or property with the name 'County' was not found on the selected data source.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: A field or property with the name 'County' was not found on the selected data source.


I've been through the table and county is there and I can even select it from the datasource in VWD.