Summing multiple input records into one output rcd query

[Guest]

I need to produce, from multiple input records, for example Gross - Taxes
withheld - Deductions Withheld - Employer Taxes - Net where each input record
will contain one of the above amounts identified by an "Amount Type". This
will be used to populate an Excel Spreadsheet for all payroll data by Payroll
Period.

I can summarize into a report by breaking on the employee name, but I really
need to have the data into one line. I use a nested iif statement that
identifies the Amount Type and outputs it into the appropriate field - if
Amount Type is "Vac", i will output the field into a "Vac" field in the
query. The problem is that my data base has only one type in the resulting
record where I really want to summarize the data into a single line on
writing to a new table.

I am totally Stumped!

 

[Guest]

Do your query like this ---

SELECT YourTableName.[Job Title], Count(YourTableName.[Job Title]) AS [Total
EE's], Sum(IIf([Sex]="m",1,0)) AS Male, Sum(IIf([Sex]="f",1,0)) AS Female,
Sum(IIf([Miniority]=True,1,0)) AS Miniorities
FROM YourTableName
GROUP BY YourTableName.[Job Title];

Post back if more explaination is needed.

 

[Guest]

This novice is really looking forward to getting in this morning and try
this! Thanks for the feedback, I really appreciate it.

I would rate this AAA+, but can't figure out where to go to do it.

Do your query like this ---

SELECT YourTableName.[Job Title], Count(YourTableName.[Job Title]) AS [Total
EE's], Sum(IIf([Sex]="m",1,0)) AS Male, Sum(IIf([Sex]="f",1,0)) AS Female,
Sum(IIf([Miniority]=True,1,0)) AS Miniorities
FROM YourTableName
GROUP BY YourTableName.[Job Title];

Post back if more explaination is needed.
--
KARL DEWEY
Build a little - Test a little

I need to produce, from multiple input records, for example Gross - Taxes
withheld - Deductions Withheld - Employer Taxes - Net where each input record
will contain one of the above amounts identified by an "Amount Type". This
will be used to populate an Excel Spreadsheet for all payroll data by Payroll
Period.

I can summarize into a report by breaking on the employee name, but I really
need to have the data into one line. I use a nested iif statement that
identifies the Amount Type and outputs it into the appropriate field - if
Amount Type is "Vac", i will output the field into a "Vac" field in the
query. The problem is that my data base has only one type in the resulting
record where I really want to summarize the data into a single line on
writing to a new table.

I am totally Stumped!