Weekly Hours

[Guest]

I'm stuck. I'm trying to design a payroll datasheet form (or tabular form)
that shows my hours by week. My HrsWrkd table is joined to my Employees
table in a way that shows the entire roster of employees and the hours they
worked for each date. This is fine for one day of the week at a time, but I
want the whole week to show up. I thought that the way to go was to create a
query that added calculated fields for "Monday", "Tuesday", etc. where the
condition limited that record to the corresponding day of the week. But, of
course, you can't have multiple records in one field. By illustration, the
form should resemble this layout:

EMPLOYEE MON TUE WED THU FRI SAT SUN

emp#1 8 8 0 6 7 0 0
emp#2 9 7 0 5 7 8 0

The hours and the date worked by each employee are stored in the HrsWrkd
table. Previously, I created an Employee Hours table which had a field for
each day of the week, organized by Monday's date, but I need to be able to
track the hours daily as well as weekly. What's the workaround?

Jaybird

 

[Guest]

Do you need to only track hours per week or tasks that make up the hours, in
other words do you need
3/1/06 - 8 hours

or

3/1/06 - real work 6 hours
3/1/06 - phone calls 1 hour
3/1/06 - meetings 1 hour

I'd have as my table:
WeekendingDate
TypeOfWork (needed to track type of work)
MonHrs
TueHrs
WedHrs
ThuHrs
FriHrs
SatHrs
SunHrs

-Dorian

 

[Guest]

Aha! You've lit upon my dilemma... I've designed a table just as you
suggest, and now I can't figure out how to track the hours by date. Is there
a way to identify each day's hours by each day's date when they are
identified in the table only by day of the week? I suspect that a pivot
table may do the trick, but it seems like an awefully wonky tool to use in a
form. Perhaps a crosstab query... I'm just confused by them both. I'm
doing research on them, but I guess I'm a little thick... Thanks for your
response.

Jaybird

 

[Arvin Meyer [MVP]]

The design of your table is wrong, as is the design of the other poster's
table. Typically, think of data as being long, not wide. That means that you
do not store the data with a field for each day, rather store it in a table
this way:

tblHoursWorked
EmpID
Date
Hours

Everything else can be calculated, and you can display the data in a
cross-tab query to show it "wide"

First write a query to show a week of data:

SELECT EmpID, DateField, Hours
FROM Table1
WHERE DateField Between [Forms]![MyForm]![txtStartDate] And
[Forms]![MyForm]![txtEndDate];

Then Crosstab the results of that query:

TRANSFORM Sum(Hours) AS [The Value]
SELECT EmpID, Sum(Hours) AS [Total Of NumberCode]
FROM Query1
GROUP BY EmpID
PIVOT Format([DateField],"Short Date");

There is a CrossTab query wizard that will help you with the crosstab
design.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I thought as much... Thank you for your comments. I'm working on this
solution right now. I'm having a little trouble setting my parameters to one
week at a time, but I've seen several posts dealing with this issue, so I
should be able to figure it out. Mr. Meyer, I really appreciate you and all
of the other Access gurus out there who take the time to answer posts such as
this. I know that your expertise is far more lucrative in a business setting
rather than a public forum. Some of you even have web sites dedicated to
helping out people like me. I just want to say, "Thanks".

Jaybird

The design of your table is wrong, as is the design of the other poster's
table. Typically, think of data as being long, not wide. That means that you
do not store the data with a field for each day, rather store it in a table
this way:

tblHoursWorked
EmpID
Date
Hours

Everything else can be calculated, and you can display the data in a
cross-tab query to show it "wide"

First write a query to show a week of data:

SELECT EmpID, DateField, Hours
FROM Table1
WHERE DateField Between [Forms]![MyForm]![txtStartDate] And
[Forms]![MyForm]![txtEndDate];

Then Crosstab the results of that query:

TRANSFORM Sum(Hours) AS [The Value]
SELECT EmpID, Sum(Hours) AS [Total Of NumberCode]
FROM Query1
GROUP BY EmpID
PIVOT Format([DateField],"Short Date");

There is a CrossTab query wizard that will help you with the crosstab
design.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I'm stuck. I'm trying to design a payroll datasheet form (or tabular form)
that shows my hours by week. My HrsWrkd table is joined to my Employees
table in a way that shows the entire roster of employees and the hours they
worked for each date. This is fine for one day of the week at a time, but I
want the whole week to show up. I thought that the way to go was to create a
query that added calculated fields for "Monday", "Tuesday", etc. where the
condition limited that record to the corresponding day of the week. But, of
course, you can't have multiple records in one field. By illustration, the
form should resemble this layout:

EMPLOYEE MON TUE WED THU FRI SAT SUN

emp#1 8 8 0 6 7 0 0
emp#2 9 7 0 5 7 8 0

The hours and the date worked by each employee are stored in the HrsWrkd
table. Previously, I created an Employee Hours table which had a field for
each day of the week, organized by Monday's date, but I need to be able to
track the hours daily as well as weekly. What's the workaround?

Jaybird

 

[Guest]

I've figured out how to use the crosstab query and now realize that this is
not updatable and neither is any form based on it. Is there a workaround?

Jaybird

I thought as much... Thank you for your comments. I'm working on this
solution right now. I'm having a little trouble setting my parameters to one
week at a time, but I've seen several posts dealing with this issue, so I
should be able to figure it out. Mr. Meyer, I really appreciate you and all
of the other Access gurus out there who take the time to answer posts such as
this. I know that your expertise is far more lucrative in a business setting
rather than a public forum. Some of you even have web sites dedicated to
helping out people like me. I just want to say, "Thanks".

Jaybird

The design of your table is wrong, as is the design of the other poster's
table. Typically, think of data as being long, not wide. That means that you
do not store the data with a field for each day, rather store it in a table
this way:

tblHoursWorked
EmpID
Date
Hours

Everything else can be calculated, and you can display the data in a
cross-tab query to show it "wide"

First write a query to show a week of data:

SELECT EmpID, DateField, Hours
FROM Table1
WHERE DateField Between [Forms]![MyForm]![txtStartDate] And
[Forms]![MyForm]![txtEndDate];

Then Crosstab the results of that query:

TRANSFORM Sum(Hours) AS [The Value]
SELECT EmpID, Sum(Hours) AS [Total Of NumberCode]
FROM Query1
GROUP BY EmpID
PIVOT Format([DateField],"Short Date");

There is a CrossTab query wizard that will help you with the crosstab
design.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I'm stuck. I'm trying to design a payroll datasheet form (or tabular form)
that shows my hours by week. My HrsWrkd table is joined to my Employees
table in a way that shows the entire roster of employees and the hours they
worked for each date. This is fine for one day of the week at a time, but I
want the whole week to show up. I thought that the way to go was to create a
query that added calculated fields for "Monday", "Tuesday", etc. where the
condition limited that record to the corresponding day of the week. But, of
course, you can't have multiple records in one field. By illustration, the
form should resemble this layout:

EMPLOYEE MON TUE WED THU FRI SAT SUN

emp#1 8 8 0 6 7 0 0
emp#2 9 7 0 5 7 8 0

The hours and the date worked by each employee are stored in the HrsWrkd
table. Previously, I created an Employee Hours table which had a field for
each day of the week, organized by Monday's date, but I need to be able to
track the hours daily as well as weekly. What's the workaround?

Jaybird

 

[Arvin Meyer [MVP]]

No. Crosstabs are not updateable. Remember, a database is not a spreadsheet.
You may need to display your data like a spreadsheet, but rarely need to
update it that way. You could buy a 3rd party grid control and use it to
read from and write to your table, but that's more of a programmer's method
than good database design.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I've figured out how to use the crosstab query and now realize that this is
not updatable and neither is any form based on it. Is there a workaround?

Jaybird

I thought as much... Thank you for your comments. I'm working on this
solution right now. I'm having a little trouble setting my parameters to one
week at a time, but I've seen several posts dealing with this issue, so I
should be able to figure it out. Mr. Meyer, I really appreciate you and all
of the other Access gurus out there who take the time to answer posts such as
this. I know that your expertise is far more lucrative in a business setting
rather than a public forum. Some of you even have web sites dedicated to
helping out people like me. I just want to say, "Thanks".

Jaybird

The design of your table is wrong, as is the design of the other poster's
table. Typically, think of data as being long, not wide. That means that you
do not store the data with a field for each day, rather store it in a table
this way:

tblHoursWorked
EmpID
Date
Hours

Everything else can be calculated, and you can display the data in a
cross-tab query to show it "wide"

First write a query to show a week of data:

SELECT EmpID, DateField, Hours
FROM Table1
WHERE DateField Between [Forms]![MyForm]![txtStartDate] And
[Forms]![MyForm]![txtEndDate];

Then Crosstab the results of that query:

TRANSFORM Sum(Hours) AS [The Value]
SELECT EmpID, Sum(Hours) AS [Total Of NumberCode]
FROM Query1
GROUP BY EmpID
PIVOT Format([DateField],"Short Date");

There is a CrossTab query wizard that will help you with the crosstab
design.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I'm stuck. I'm trying to design a payroll datasheet form (or tabular
form)
that shows my hours by week. My HrsWrkd table is joined to my Employees
table in a way that shows the entire roster of employees and the hours
they
worked for each date. This is fine for one day of the week at a time, but
I
want the whole week to show up. I thought that the way to go was to
create a
query that added calculated fields for "Monday", "Tuesday", etc. where the
condition limited that record to the corresponding day of the week. But,
of
course, you can't have multiple records in one field. By illustration,
the
form should resemble this layout:

EMPLOYEE MON TUE WED THU FRI SAT SUN

emp#1 8 8 0 6 7 0 0
emp#2 9 7 0 5 7 8 0

The hours and the date worked by each employee are stored in the HrsWrkd
table. Previously, I created an Employee Hours table which had a field
for
each day of the week, organized by Monday's date, but I need to be able to
track the hours daily as well as weekly. What's the workaround?

Jaybird