Solved

Need Some Help With an Access Query

Posted on 2014-02-19
3
541 Views
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.

Error
0
Comment
Question by:Basicfarmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 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
0
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 250 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.

ab
0
 

Author Closing Comment

by:Basicfarmer
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.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

752 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