Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

Need Some Help With an Access Query

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
Basicfarmer
Asked:
Basicfarmer
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Anthony BerenguelCommented:
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
 
BasicfarmerAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now