Hi,
Here is the SQL of me query
SELECT DISTINCTROW [Employee's Personal File].EmpCode, [Employee's Other
info].[Emp NSSF No1A], [Employee's Other info].[Emp NSSF No2A],
IIf([SpouseFamily Allowance]=True,1,0) AS NbSpouse, IIf(IsNull([Employee's
Children_Crosstab for Family Allowance.-1]),0,[Employee's Children_Crosstab
for Family Allowance.-1]) AS NbChildren, ([NbSpouse]+[NbChildren]) AS NbFam,
IIf([EmpGender]="M",([EmpFirstNameA] & " " & [EmpFatherNameA] & " " &
[EmpLastNameA]),([EmpFirstNameA] & " " & [EmpFatherNameA] & " " &
[EmpMaidenNameA])) AS FullName, IIf([EmpGender]="M",([Spouse 1st NameA] & " "
& [Spouse Father's NameA] & " " & [Spouse Maiden NameA] & " " &
Year([SpouseBirth DateA])),"") AS SpouseFullName,
IIf([ChildNSSFFamilyAllowance]=True,[ChildNameA] & " " & Year([ChildBirth
DateA])," ") AS ChildName
FROM ((((([A-Company Information] INNER JOIN (([Employee's Personal File]
LEFT JOIN [Employee's Children] ON [Employee's Personal File].EmpCode =
[Employee's Children].EmpCode) LEFT JOIN [Employee's Children_Crosstab for
Family Allowance] ON [Employee's Personal File].EmpCode = [Employee's
Children_Crosstab for Family Allowance].EmpCode) ON [A-Company
Information].CompCode = [Employee's Personal File].CompCode) LEFT JOIN
[Employee's Family] ON [Employee's Personal File].EmpCode = [Employee's
Family].EmpCode) INNER JOIN [Employee's Job Description] ON [Employee's
Personal File].EmpCode = [Employee's Job Description].EmpCode) INNER JOIN
[Employee's Job Title] ON [Employee's Personal File].EmpCode = [Employee's
Job Title].EmpCode) INNER JOIN [Employee's Other info] ON [Employee's
Personal File].EmpCode = [Employee's Other info].EmpCode) INNER JOIN
[Employee's Salary] ON [Employee's Personal File].EmpCode = [Employee's
Salary].EmpCode
ORDER BY [Employee's Other info].[Emp NSSF No2A];
and here is the field names in the detail section of my report:
- FullName (for example Paul Newton)
- Emp NSSF No2A (for example 123456)
- Emp NSSF No1A (for example 1990)
- SpouseFullName (for example Suzanne Charles Francis)
- ChildName (for example: 1) John
2) Grace
3) Rebecca
- NbFam ( for example 1) 4
2) 4 (hidden)
3) 4 (hidden)
and in the report footer:
- =Sum([NbFam]) (for example = 12 but must be 4)
Thank you again
Jeff Boyce said:
Najat
Are you saying that your underlying data has the same employee three times
(for three children), but also has "3" (for 3 children) in EACH record? If
so, this seems redundant.
If you simply count the number of records, does that give you what you need?
I suspect I'm having trouble offering useful suggestions because I don't
understand your underlying data.
Can you describe what your data table structure is like?
Can you post the SQL of your query?
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
Najat said:
Thank you for your answer.
I did like you tell me but the answer was an error answer.
For example, if an employee has 3 children and an other 2 children. The sum
of these family member must be 5. But Access give me an answer equal to 13
because it counts
the first row = 3
and the second row = 3
and the third row = 3 (even if it is hidden) and so on for the 2nd employee
Thank you
:
The employee's number of family members shows in the Detail section, right?
To put the sum of NumberOfFamilyMembers in the Report Footer, add a text box
(unbound). In the Control Source, put something like:
=Sum([YourNumberOfFamilyMembersFieldName])
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
In the report footer I want the total number of all employees
