Need Some Help With an Access Query

Posted on 2014-02-19
Medium Priority
Last Modified: 2014-02-19
Experts please help me with a Query. It should be very simple but i have not worked with Access in quite a while so I am not doing something right.

I have two tables; Company and Contact.

Company table has two fields; [ID] and [CompanyName].
Contact table has three fields; [ID] and [CompanyID] and [ContactName]

I need the query to output all Company Names and a count of the number of contacts for that company.

Anything I do in the query builder gives me the following error.

Question by:Basicfarmer
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39871660
try this query

select C.CompanyName, Count(C.[ID])
From Company As C
Inner Join Contact as C1
On C.ID=C1.CompanyID
Group By C.CompanyName
LVL 10

Accepted Solution

Anthony Berenguel earned 1000 total points
ID: 39871663
Go to the SQL View of your query and get the structure of the query to match this format (you'll have to replace my table names with your exact table names)
SELECT tblCompany.id, tblCompany.companyName, Count(tblContracts.id) AS CountOfid
FROM tblCompany INNER JOIN tblContracts ON tblCompany.id = tblContracts.companyId
GROUP BY tblCompany.id, tblCompany.companyName;

Open in new window

Hopefully that helps.


Author Closing Comment

ID: 39871737
Both of your posts work correctly and produce the desired results. So i am splitting the points. It forces me to choose a best solution and they are both equally good so i just left it at the default choice. Thanks for the great help and rapid response to my question.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question