Count Function for Certain Record Criteria?

[frosty2000]

Hello,

I am creating a query that will pull demographic information on our employees' spouses and children. The spouse and dependent records are linked to the employee by the employee's social security number, and the column "Record Type" differentiates between an employee "E", spouse "S", and child "C".

In the Category field in my query, I need it to output "S" if Record Type="S", and if it is a Child ("C"), then I need it to output C1 for child record 1, C2 for child record 2, etc.

How do I set this up? Any help would be appreciated. Bear with me...I'm a beginner! Thanks!

 

[PHV]

I assume the tblDependent table has a birthday field:
SELECT SSN, 'E' AS Category, FirstName, LastName
FROM tblEmployee
UNION SELECT SSN, 'S', FirstName, LastName
FROM tblDependent
WHERE RecordType='S'
UNION SELECT A.SSN, 'C' & Count(*), A.FirstName, A.LastName
FROM tblDependent A INNER JOIN tblDependent B ON A.SSN=B.SSN And A.Birthday>=B.Birthday
WHERE RecordType='C'
GROUP BY A.SSN, A.FirstName, A.LastName

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

 

[frosty2000]

PHV - Thanks for the info.

Yes, the tblDependent has a birthday field. Please explain to me the purpose of including birthdays in the code?

Also, the data is all coming from a Union query that I used to combine the employee and the dependent data together. Does that matter?

And please tell me exactly where to put the above code? Do I put that in the "Build" section of my query field?

Again, I'm a beginner... Thanks for your help.

 

[PHV]

the purpose of including birthdays in the code
It's for ranking the Children records.
the data is all coming from a Union query
Without the original table(s) schema it's hard to say if that does matter ...

I suggest you post the relevant infos about your database structure, some input samples and expected result.

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