Two Tables in Query Results in Wrong Count of Records

[Zaxxon]

Hi, I'm trying to create a query to count the number of records based on information stored in different tables. For instance, one table tracks general complaints, another support complaints and a third product complaints. I want a query that would count the number of each.

When I try to do this, I usually set the join property to #2 to include ALL records from my general complaints table (as it's my biggest/main table).

The problem is that it wrongly counts the number of complaints in the other tables. I've created a sample table to show what I did and reproduce the result. If you run the "Count Records that Exist" query, it will show you 7 records have that property. But if you run the "Count Records and Exclusions" query, it will show you that 10 records have that property...7 is the correct amount.

Any thoughts? I'm just starting to work with relational db stuff...

 

[Rabbie]

It's a little complicated but you are not actually counting the records more than once if they have a link across to more than 1 record in the other table. Try writing a query which displays the linked fields rather than a count to see what I mean

 

[khawar]

Use the Sql below

SELECT CountReq.[CountOfRecord Exists] AS [Record That Exist], CountComplaints.TotalExclusions
FROM [SELECT Count(Complaints.Exclusions) AS TotalExclusions
FROM Complaints]. AS CountComplaints, [SELECT Count(Requests.[Record Exists]) AS [CountOfRecord Exists]
FROM Requests
WHERE (((Requests.[Record Exists])=-1))
]. AS CountReq;