Query to add totals

[jenksonu]

my table is called employes. In this table I have fields called NAME JAN FEB
MAR throu DEC and YTD
I'm wanting a query that will update YTD with the total combined numbers from
each Month
Thanks in advance for your help

 

[Jeff Boyce]

You have a spreadsheet, not a relational database.

If your fields are named [JAN], [FEB], ..., won't you need a new table each
year? That's a lot of extra maintenance! And how do you plan to compare
last year's values for, say, [MAR] with this year's?

You can keep struggling with the 'sheet data, or look into
"normalization" -- pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jenksonu]

They only want this for one year. But you are right. I guess I need some
guideance on how to do this.

You have a spreadsheet, not a relational database.

If your fields are named [JAN], [FEB], ..., won't you need a new table each
year? That's a lot of extra maintenance! And how do you plan to compare
last year's values for, say, [MAR] with this year's?

You can keep struggling with the 'sheet data, or look into
"normalization" -- pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

my table is called employes. In this table I have fields called NAME JAN
FEB

[quoted text clipped - 3 lines]

each Month
Thanks in advance for your help

 

[Jeff Boyce]

Try starting out at: mvps.org/access. There are many links there to
explanations of normalization...

Regards

Jeff Boyce
Microsoft Office/Access MVP

They only want this for one year. But you are right. I guess I need some
guideance on how to do this.

You have a spreadsheet, not a relational database.

If your fields are named [JAN], [FEB], ..., won't you need a new table
each
year? That's a lot of extra maintenance! And how do you plan to compare
last year's values for, say, [MAR] with this year's?

You can keep struggling with the 'sheet data, or look into
"normalization" -- pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

my table is called employes. In this table I have fields called NAME JAN
FEB

[quoted text clipped - 3 lines]

each Month
Thanks in advance for your help

 

[Duane Hookom]

To get you started in the right direction, a suggested table structure might
have fields like:
EmployeeName (NAME is NOT a good name to name anything)
TheDate values like 1/1/2009, 2/1/2009, ....
TheValue (or a more accurate name like HoursWorked or BudgetHrs or...)
A table like this allows easy aggregation of YTD like:
SELECT EmployeeName, Sum(TheValue) as SumValue
FROM tblEmployeeNormalized
WHERE Year(TheDate) = 2009
GROUP BY EmployeeName;

--
Duane Hookom
Microsoft Access MVP

Try starting out at: mvps.org/access. There are many links there to
explanations of normalization...

Regards

Jeff Boyce
Microsoft Office/Access MVP

They only want this for one year. But you are right. I guess I need some
guideance on how to do this.

You have a spreadsheet, not a relational database.

If your fields are named [JAN], [FEB], ..., won't you need a new table
each
year? That's a lot of extra maintenance! And how do you plan to compare
last year's values for, say, [MAR] with this year's?

You can keep struggling with the 'sheet data, or look into
"normalization" -- pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

my table is called employes. In this table I have fields called NAME JAN
FEB
[quoted text clipped - 3 lines]
each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

Ok I now have these fields
EmployeeName
The Date
HoursWorked
YTD

Now how do I make YTD field autoupdate when new records are added?

To get you started in the right direction, a suggested table structure might
have fields like:
EmployeeName (NAME is NOT a good name to name anything)
TheDate values like 1/1/2009, 2/1/2009, ....
TheValue (or a more accurate name like HoursWorked or BudgetHrs or...)
A table like this allows easy aggregation of YTD like:
SELECT EmployeeName, Sum(TheValue) as SumValue
FROM tblEmployeeNormalized
WHERE Year(TheDate) = 2009
GROUP BY EmployeeName;

Try starting out at: mvps.org/access. There are many links there to
explanations of normalization...

[quoted text clipped - 29 lines]

 

[jenksonu via AccessMonster.com]

This is what I have tried but get a syntex is incorrect error
SELECT EmployeName, Sum(HoursWorked) as SumValue FROM Test WHERE Year(TheDate)
= 2009 GROUP BY EmployeeNamee;

Ok I now have these fields
EmployeeName
The Date
HoursWorked
YTD

Now how do I make YTD field autoupdate when new records are added?

To get you started in the right direction, a suggested table structure might
have fields like:

[quoted text clipped - 12 lines]

 

[jenksonu via AccessMonster.com]

Sorry I misstyped the last message. I left out an e in EmployeeName then
added an e to the end of
EmployeeName at the end.

This is what I have and enclosed it with parentheses. I get no syntax error
but the query does not update YTD field.
SELECT EmployeeName, Sum(HoursWorked) as SumValue FROM Test WHERE Year
(TheDate) = 2009 GROUP BY EmployeeName;

This is what I have tried but get a syntex is incorrect error
SELECT EmployeName, Sum(HoursWorked) as SumValue FROM Test WHERE Year(TheDate)
= 2009 GROUP BY EmployeeNamee;

Ok I now have these fields
EmployeeName

[quoted text clipped - 9 lines]

 

[Duane Hookom]

The query should display your YTD HoursWorked sum value. You shouldn't store
it.

--
Duane Hookom
Microsoft Access MVP

Sorry I misstyped the last message. I left out an e in EmployeeName then
added an e to the end of
EmployeeName at the end.

This is what I have and enclosed it with parentheses. I get no syntax error
but the query does not update YTD field.
SELECT EmployeeName, Sum(HoursWorked) as SumValue FROM Test WHERE Year
(TheDate) = 2009 GROUP BY EmployeeName;

This is what I have tried but get a syntex is incorrect error
SELECT EmployeName, Sum(HoursWorked) as SumValue FROM Test WHERE Year(TheDate)
= 2009 GROUP BY EmployeeNamee;

Ok I now have these fields
EmployeeName

[quoted text clipped - 9 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

I guess I just don't understand where or how to use it.
My query fields, I'm using
EmployeeName
TheDate
HoursWorked
on a select query and put the SELECT statement in the Criteria.
When I run the query, It shows no employee Names and shows 0 for hours worked
What I'm trying to do is show the hours worked for this month and the total
Year to Date.

The query should display your YTD HoursWorked sum value. You shouldn't store
it.

Sorry I misstyped the last message. I left out an e in EmployeeName then
added an e to the end of

[quoted text clipped - 14 lines]

 

[Duane Hookom]

As stated before, if you want YTD totals by employee, create a query with the
SQL view of:
SELECT EmployeeName, Sum(HoursWorked) as SumHrsWrkd
FROM qYourQueryName
WHERE Year(TheDate) = 2009
GROUP BY EmployeeName;

--
Duane Hookom
Microsoft Access MVP

I guess I just don't understand where or how to use it.
My query fields, I'm using
EmployeeName
TheDate
HoursWorked
on a select query and put the SELECT statement in the Criteria.
When I run the query, It shows no employee Names and shows 0 for hours worked
What I'm trying to do is show the hours worked for this month and the total
Year to Date.

The query should display your YTD HoursWorked sum value. You shouldn't store
it.

Sorry I misstyped the last message. I left out an e in EmployeeName then
added an e to the end of

[quoted text clipped - 14 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

Thanks so much... Works Purfect...
How would I add to this query to always show this month or the most resent
entrys?

As stated before, if you want YTD totals by employee, create a query with the
SQL view of:
SELECT EmployeeName, Sum(HoursWorked) as SumHrsWrkd
FROM qYourQueryName
WHERE Year(TheDate) = 2009
GROUP BY EmployeeName;

I guess I just don't understand where or how to use it.
My query fields, I'm using

[quoted text clipped - 14 lines]

 

[Duane Hookom]

Show where?

--
Duane Hookom
Microsoft Access MVP

Thanks so much... Works Purfect...
How would I add to this query to always show this month or the most resent
entrys?

As stated before, if you want YTD totals by employee, create a query with the
SQL view of:
SELECT EmployeeName, Sum(HoursWorked) as SumHrsWrkd
FROM qYourQueryName
WHERE Year(TheDate) = 2009
GROUP BY EmployeeName;

I guess I just don't understand where or how to use it.
My query fields, I'm using

[quoted text clipped - 14 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

when I run the query I would like it to output like this example,
EmployeeName CurrentMonth YTD
Dennis 156 791
Joe 122 622

and so on

Show where?

Thanks so much... Works Purfect...
How would I add to this query to always show this month or the most resent

[quoted text clipped - 12 lines]

 

[Duane Hookom]

The easiest method is to copy the solution I provided before but change the
where clause to include Month(TheDate) = Month(Date())

Then combine both totals queries in a single select query.

You can also try:

SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm")) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;

--
Duane Hookom
Microsoft Access MVP

when I run the query I would like it to output like this example,
EmployeeName CurrentMonth YTD
Dennis 156 791
Joe 122 622

and so on

Show where?

Thanks so much... Works Purfect...
How would I add to this query to always show this month or the most resent

[quoted text clipped - 12 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

In trying to use this when saving it I get the error missing operator in
query expression and when I click on ok it highlights the "as" on this line
"Format(Date(),"yyyymm")) as MTD,"

The easiest method is to copy the solution I provided before but change the
where clause to include Month(TheDate) = Month(Date())

Then combine both totals queries in a single select query.

You can also try:

SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm")) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;

when I run the query I would like it to output like this example,
EmployeeName CurrentMonth YTD

[quoted text clipped - 10 lines]

 

[Duane Hookom]

You need to learn how to trouble-shoot syntax errors to fix mistakes. Try:
SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;
--
Duane Hookom
Microsoft Access MVP

In trying to use this when saving it I get the error missing operator in
query expression and when I click on ok it highlights the "as" on this line
"Format(Date(),"yyyymm")) as MTD,"

The easiest method is to copy the solution I provided before but change the
where clause to include Month(TheDate) = Month(Date())

Then combine both totals queries in a single select query.

You can also try:

SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm")) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;

when I run the query I would like it to output like this example,
EmployeeName CurrentMonth YTD

[quoted text clipped - 10 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

I still can't seen to get this to work. Still getting Syntax error (Missing
Operator)

My Table name is TEST
Fields are;
ID
EmployeeName
The Date (Date/Time formated with shortdate)
HoursWorked

My Query Name is
testQuery
and reads
SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM testQuery
GROUP BY EmployeeName;

You need to learn how to trouble-shoot syntax errors to fix mistakes. Try:
SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;

In trying to use this when saving it I get the error missing operator in
query expression and when I click on ok it highlights the "as" on this line

[quoted text clipped - 19 lines]

 

[Duane Hookom]

Try:

SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Year(TheDate) = Year(Date())) ) as YTD
FROM testQuery
GROUP BY EmployeeName;


--
Duane Hookom
Microsoft Access MVP

I still can't seen to get this to work. Still getting Syntax error (Missing
Operator)

My Table name is TEST
Fields are;
ID
EmployeeName
The Date (Date/Time formated with shortdate)
HoursWorked

My Query Name is
testQuery
and reads
SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM testQuery
GROUP BY EmployeeName;

You need to learn how to trouble-shoot syntax errors to fix mistakes. Try:
SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Date(TheDate) = Year(Date())) as YTD
FROM qYourQueryName
GROUP BY EmployeeName;

In trying to use this when saving it I get the error missing operator in
query expression and when I click on ok it highlights the "as" on this line

[quoted text clipped - 19 lines]

each Month
Thanks in advance for your help

 

[jenksonu via AccessMonster.com]

This still doesn't seem to work... So I back tracked in our conversation and
changed the YTD code you gave me to look like this.
SELECT [EmployeeName], Sum([HoursWorked]) AS MTD
FROM test
WHERE Month(TheDate)=Month(Date())
GROUP BY [EmployeeName];
Then I did a new query that joined the MTD and YTD that looks like this
SELECT MTD.EmployeeName, MTD.MTD, YTD_2009.YTD_2009
FROM MTD INNER JOIN YTD_2009 ON MTD.EmployeeName = YTD_2009.EmployeeName;
This seems to work for what I need.
One question, I have noted that if there is no data for this month, then this
query MTD and YTD shows no user names and or YTD Information. Is there a way
to make MTD show all 0 if no entry's for the current month?

Try:

SELECT EmployeeName,
Sum(HoursWorked * Abs(Format(TheDate,"yyyymm") =
Format(Date(),"yyyymm"))) as MTD,
Sum(HoursWorked * Abs(Year(TheDate) = Year(Date())) ) as YTD
FROM testQuery
GROUP BY EmployeeName;

I still can't seen to get this to work. Still getting Syntax error (Missing
Operator)

[quoted text clipped - 28 lines]