Total Row refresher

M

mdavison

Its been quite some time since I've effectivly used to Totals button (sigma)
in a query. Please help!
My data is relevant to employee 401K stuff, where each record relates to
values for a pay-period. I want my query to total (on the relevant $ amount
fields) just the totals for the year to date for each field, but I can't
remember how to achieve that. But I know I've achieved something like this
in the past.

The primary key is an auto-count since the name and social security are
repeated based on pay period date - the owner of this file didn't know about
using multi-field keys and its a little late to make that change now. Is it
possible this "ID" field is getting in my way?
Thanks in advance!
-Monica

I'm not a big SQL person, but in the interest of providing data, below is
the SQL version of my query:
SELECT Assoc.ID, Sum(Assoc.[YTD Gross Earnings]) AS [SumOfYTD Gross
Earnings], Assoc.Name, Assoc.[Social Security Number], Assoc.[Contribution
Type], Assoc.[Employee Pre-Tax Deferral], Sum(Assoc.[Employer Match]) AS
[SumOfEmployer Match], Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan],
Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth], Sum(Assoc.[Employer
Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof Sharing],
Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover],
[Assoc-Dates].[Hire Date], [Assoc-Dates].[Termination Date]
FROM Assoc LEFT JOIN [Assoc-Dates] ON Assoc.Name = [Assoc-Dates].Name
GROUP BY Assoc.ID, Assoc.Name, Assoc.[Social Security Number],
Assoc.[Contribution Type], Assoc.[Employee Pre-Tax Deferral],
[Assoc-Dates].[Hire Date], [Assoc-Dates].[Termination Date];
 
M

mdavison

I removed an extraneous table.
here is the correct (I think) SQL for those of you who work better that way.
Thanks again,
-M

SELECT Assoc.[Social Security Number], Sum(Assoc.[YTD Gross Earnings]) AS
[SumOfYTD Gross Earnings], Assoc.Name, Assoc.[Contribution Type],
Assoc.[Employee Pre-Tax Deferral], Sum(Assoc.[Employer Match]) AS
[SumOfEmployer Match], Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan],
Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth], Sum(Assoc.[Employer
Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof Sharing],
Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number], Assoc.Name, Assoc.[Contribution
Type], Assoc.[Employee Pre-Tax Deferral];
 
J

John Spencer

SELECT Assoc.[Social Security Number]
, Sum(Assoc.[YTD Gross Earnings]) AS [SumOfYTD Gross Earnings]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral]
, Sum(Assoc.[Employer Match]) AS [SumOfEmployer Match]
, Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan]
, Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth]
, Sum(Assoc.[Employer Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof
Sharing]
, Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral];

Do you want to limit the results based on a datefield?
In the query grid, add the datefield and replace GROUP BY with WHERE and
then enter the criteria.

In SQL enter
WHERE <<Some date Field>> Between #1/1/06# and #9/30/06#
between the FROM and the Group By.

If that is not what you want perhaps you can explain more fully how you want
to limit the data returned.
I do question why you are summing YTD Gross Earnings - since that would seem
to be a running total already.
 
M

mdavison

I tried this and still get same results. I agree regarding your YTD
question - but that data isn't even added until the end of the year. Mine is
not to question the logic of a 70+ year old woman who is actually quite
sharp.

Seems like regardless of what I do - I my results still show each atty name
multiple times (representative of each pay period) and each value, instead
of combining all the records for each person into one lump sum. the obvious
fields - name or social - are not key fields. Is that the trouble?

OR....I'll try this, maybe someone will have feedback before I finish
testing - If I add an expression field to the query which uses a date
function to display ONLY the year of each entry based on the date field -
then maybe it will allow me to Sum "where <<expression field>> =2006"???

I'll let you know if that works.


John Spencer said:
SELECT Assoc.[Social Security Number]
, Sum(Assoc.[YTD Gross Earnings]) AS [SumOfYTD Gross Earnings]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral]
, Sum(Assoc.[Employer Match]) AS [SumOfEmployer Match]
, Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan]
, Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth]
, Sum(Assoc.[Employer Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof
Sharing]
, Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral];

Do you want to limit the results based on a datefield?
In the query grid, add the datefield and replace GROUP BY with WHERE and
then enter the criteria.

In SQL enter
WHERE <<Some date Field>> Between #1/1/06# and #9/30/06#
between the FROM and the Group By.

If that is not what you want perhaps you can explain more fully how you want
to limit the data returned.
I do question why you are summing YTD Gross Earnings - since that would seem
to be a running total already.


mdavison said:
I removed an extraneous table.
here is the correct (I think) SQL for those of you who work better that
way.
Thanks again,
-M

SELECT Assoc.[Social Security Number], Sum(Assoc.[YTD Gross Earnings]) AS
[SumOfYTD Gross Earnings], Assoc.Name, Assoc.[Contribution Type],
Assoc.[Employee Pre-Tax Deferral], Sum(Assoc.[Employer Match]) AS
[SumOfEmployer Match], Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan],
Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth], Sum(Assoc.[Employer
Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof Sharing],
Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number], Assoc.Name, Assoc.[Contribution
Type], Assoc.[Employee Pre-Tax Deferral];
Click to expand...
Click to expand...
 
M

mdavison

I had too many fields set to "Group By".
I removed all of them EXCEPT Social Security # and Name and got exactly the
results I wanted. :) One record result per employee displaying sum totals of
contributions and loans.
Thanks all.

for future help, here is what it looks like:

SELECT Assoc.[Social Security Number], Assoc.Name, Sum(Assoc.[Employer
Match]) AS [SumOfEmployer Match], Sum(Assoc.[Employee Loan]) AS
[SumOfEmployee Loan], Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth],
Sum(Assoc.[Employer Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof
Sharing], Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number], Assoc.Name;



John Spencer said:
SELECT Assoc.[Social Security Number]
, Sum(Assoc.[YTD Gross Earnings]) AS [SumOfYTD Gross Earnings]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral]
, Sum(Assoc.[Employer Match]) AS [SumOfEmployer Match]
, Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan]
, Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth]
, Sum(Assoc.[Employer Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof
Sharing]
, Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number]
, Assoc.Name, Assoc.[Contribution Type]
, Assoc.[Employee Pre-Tax Deferral];

Do you want to limit the results based on a datefield?
In the query grid, add the datefield and replace GROUP BY with WHERE and
then enter the criteria.

In SQL enter
WHERE <<Some date Field>> Between #1/1/06# and #9/30/06#
between the FROM and the Group By.

If that is not what you want perhaps you can explain more fully how you want
to limit the data returned.
I do question why you are summing YTD Gross Earnings - since that would seem
to be a running total already.


mdavison said:
I removed an extraneous table.
here is the correct (I think) SQL for those of you who work better that
way.
Thanks again,
-M

SELECT Assoc.[Social Security Number], Sum(Assoc.[YTD Gross Earnings]) AS
[SumOfYTD Gross Earnings], Assoc.Name, Assoc.[Contribution Type],
Assoc.[Employee Pre-Tax Deferral], Sum(Assoc.[Employer Match]) AS
[SumOfEmployer Match], Sum(Assoc.[Employee Loan]) AS [SumOfEmployee Loan],
Sum(Assoc.[Employee Roth]) AS [SumOfEmployee Roth], Sum(Assoc.[Employer
Discr/Prof Sharing]) AS [SumOfEmployer Discr/Prof Sharing],
Sum(Assoc.[Employee Rollover]) AS [SumOfEmployee Rollover]
FROM Assoc
GROUP BY Assoc.[Social Security Number], Assoc.Name, Assoc.[Contribution
Type], Assoc.[Employee Pre-Tax Deferral];
Click to expand...
Click to expand...
 
Top