Solved

Need Some Help With an Access Query

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 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