Solved

Need Some Help With an Access Query

Posted on 2014-02-19
3
544 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

636 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