Missing records in my sql count query 1

[DAmoss]

SELECT Count([INDIVIDUALS Starter Info].CONST) AS Total, CONST.Description
FROM CONST INNER JOIN [INDIVIDUALS Starter Info] ON CONST.Value = [INDIVIDUALS Starter Info].CONST
GROUP BY CONST.Description;

Using the above code I end up with the following results:

Total Description
1 No
3501 Yes

It's counted the 'Yes' & 'No's .. great!

But I actually have 3615 records, how do I count the records that don't match my joined table as well?

Thanx in advance

 

[PHV]

Where are the missing records ?
In CONST or [INDIVIDUALS Starter Info] ?
A starting point:
SELECT Count(*) AS Total, C.Description
FROM CONST AS C LEFT JOIN [INDIVIDUALS Starter Info] AS I ON C.Value = I.CONST
GROUP BY C.Description;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lynchg]

Try this:

SELECT Count([INDIVIDUALS Starter Info].CONST) AS Total, [INDIVIDUALS Starter Info].Value
FROM [INDIVIDUALS Starter Info] LEFT OUTER JOIN CONST ON [INDIVIDUALS Starter Info].CONST = CONST.Value
WHERE CONST.Value IS NULL
GROUP BY [INDIVIDUALS Starter Info].Value;

This will give you the count of the different CONST values in [INDIVIDUALS Starter Info] grouped by the Value field where there is no corresponding record int the CONST table. The sum of the Total field for this query should be 113.

 

[lynchg]

PHV and I were attacking the problem in a similar fashion from both sides. Try them both, I think you will find what you are looking for.

 

[DAmoss]

Thanks guys, but your examples still don't give me the totals I have already and the total of the unmatched records.

The missing records (main data) are held in the [INDIVIDUALS Starter Info] table. [CONST] is the lookup table.

PHV ... the Left join only gives me a count of items that are in the joined table, not the missing records as well. This is the same as what I already have.

lynchg ... I suspect you have written the code the other way round, it didn't work either.

I need to be able to count all the records that match the joined table and the ones that don't as well, effectively that will be my error count.#

Thanx for trying ...

 

[PHV]

And what about replacing the LEFT JOIN by a Right JOIN in my suggestion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Or perhaps this ?
SELECT Count(*) AS Total, C.Description
FROM CONST AS C LEFT JOIN [INDIVIDUALS Starter Info] AS I ON C.Value = I.CONST
GROUP BY C.Description
UNION ALL SELECT Count(*), I.CONST
FROM CONST AS C RIGHT JOIN [INDIVIDUALS Starter Info] AS I ON C.Value = I.CONST
WHERE C.Value Is Null GROUP BY I.CONST

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DAmoss]

Tried the right join as you suggested, it gives me the extra row for the unknown total, but it has a value of 0 when it should be 113.

 

[DAmoss]

PHV, you cracked it! you have given me the answer as usual.

Thank you.

Ps. I must read up on the use of this 'Union' command.

 

[PHV]

I guess the UNION was required due to the lack of FULL JOIN in JetSQL.