Table Set-up

  • Thread starter abourg8646 via AccessMonster.com
  • Start date
A

abourg8646 via AccessMonster.com

I have 2 tables. The 1st is employee and the 2nd is schedule. The employee
fields are.
Employee name, Shift, Time, Post, Wk Location,
The Schedule table fields are
Date wkd,Time Wkd, Hrs Worked, Contract Hrs, Hol, Vac, LWOP, Mil, Brev, EKV,
O/T
When I need to run a report then I need for all this data in the fields to
show up in rows and not columns. Is there another way to set up my tables so
that the data will be easyer to set up in rows. Right know I have a crosstab
query that will change the date to column headers but it will only show one
value under it. I need for everything in the schedule table to show up in
rows.
 
J

Jeff Boyce

Arthur

I didn't notice a 'common field' in your description. How does Access know
which record in your first table is related to which record (?records) in
your second table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

abourg8646 via AccessMonster.com

I'm sorry I forgot to put in Employee ID in the schedule table. The
relationship links Employee name and Employye ID

Jeff said:
Arthur

I didn't notice a 'common field' in your description. How does Access know
which record in your first table is related to which record (?records) in
your second table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
I have 2 tables. The 1st is employee and the 2nd is schedule. The employee
fields are.
Click to expand...
[quoted text clipped - 12 lines]
value under it. I need for everything in the schedule table to show up in
rows.
Click to expand...
Click to expand...
 
J

Jeff Boyce

Both? Wouldn't EmployeeID be sufficient?

Regards

Jeff Boyce
Microsoft Office/Access MVP


abourg8646 via AccessMonster.com said:
I'm sorry I forgot to put in Employee ID in the schedule table. The
relationship links Employee name and Employye ID

Jeff said:
Arthur

I didn't notice a 'common field' in your description. How does Access
know
which record in your first table is related to which record (?records) in
your second table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
I have 2 tables. The 1st is employee and the 2nd is schedule. The
employee
fields are.
Click to expand...
[quoted text clipped - 12 lines]
value under it. I need for everything in the schedule table to show up
in
rows.
Click to expand...
Click to expand...
Click to expand...
 
A

abourg8646 via AccessMonster.com

yes. I already have the database set up and I have a month of data in it. My
real problem is when I go to run a report it needs to look like this
Name 01-May-09 02-May-09 03-May-09 04-May-09 05-May-09
Beishir Time Off Off VAC 0600-1400 0600-1400
Hrs Worked 0.00 0.00 0.00 8.00 8.00
Contract Hrs 0.00 0.00 8.00 8.00 8.00
Hol 0.00 0.00 0.00 0.00 0.00
Vac 0.00 0.00 8.00 0.00 0.00
LWOP 0.00 0.00 0.00 0.00 0.00
Mil 0.00 0.00 0.00 0.00 0.00
Brev 0.00 0.00 0.00 0.00 0.00
EKV 0.00 0.00 0.00 0.00 0.00
O/T 0.00 0.00 0.00 0.00 0.00
Biffle Time 0600-1400 Off Off 0600-1400 0600-1400
Hrs Worked 8.00 0.00 0.00 8.00 8.00
Contract Hrs 8.00 0.00 0.00 8.00 8.00
Hol 0.00 0.00 0.00 0.00 0.00
Vac 0.00 0.00 0.00 0.00 0.00
LWOP 0.00 0.00 0.00 0.00 0.00
Mil 0.00 0.00 0.00 0.00 0.00
Brev 0.00 0.00 0.00 0.00 0.00
EKV 0.00 0.00 0.00 0.00 0.00
O/T 0.00 0.00 0.00 0.00 0.00
But when I do a crosstab to get the date to go from left to right I can only
pull in one value. That part works great thanks to Duane Hookom. I was
thinking that if the table was set up different then I would be able to pull
in all this data.

Jeff said:
Both? Wouldn't EmployeeID be sufficient?

Regards

Jeff Boyce
Microsoft Office/Access MVP
I'm sorry I forgot to put in Employee ID in the schedule table. The
relationship links Employee name and Employye ID
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
D

Duane Hookom

I think this can be done by first creating a normalizing union query of the
Schedule table. Something like:
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hrs Worked] As Hrs, "Hours Worked" as TimeType
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Contract Hrs], "Contract Hours"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hol], "Holiday"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Vac], "Vacation"
FROM Schedule
--- etc ---
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[O/T], "OT"
FROM Schedule;

You can then create a crosstab from this that uses the Employee info as Row
Headings, DateWkd as column headings, and Sum(Hrs) as the Value.

I'm not sure how to handle the Time Wkd since it looks to be text while
other values are numeric. I would also report the column headings as relative
dates rather than actual dates.

--
Duane Hookom
Microsoft Access MVP


abourg8646 via AccessMonster.com said:
yes. I already have the database set up and I have a month of data in it. My
real problem is when I go to run a report it needs to look like this
Name 01-May-09 02-May-09 03-May-09 04-May-09 05-May-09
Beishir Time Off Off VAC 0600-1400 0600-1400
Hrs Worked 0.00 0.00 0.00 8.00 8.00
Contract Hrs 0.00 0.00 8.00 8.00 8.00
Hol 0.00 0.00 0.00 0.00 0.00
Vac 0.00 0.00 8.00 0.00 0.00
LWOP 0.00 0.00 0.00 0.00 0.00
Mil 0.00 0.00 0.00 0.00 0.00
Brev 0.00 0.00 0.00 0.00 0.00
EKV 0.00 0.00 0.00 0.00 0.00
O/T 0.00 0.00 0.00 0.00 0.00
Biffle Time 0600-1400 Off Off 0600-1400 0600-1400
Hrs Worked 8.00 0.00 0.00 8.00 8.00
Contract Hrs 8.00 0.00 0.00 8.00 8.00
Hol 0.00 0.00 0.00 0.00 0.00
Vac 0.00 0.00 0.00 0.00 0.00
LWOP 0.00 0.00 0.00 0.00 0.00
Mil 0.00 0.00 0.00 0.00 0.00
Brev 0.00 0.00 0.00 0.00 0.00
EKV 0.00 0.00 0.00 0.00 0.00
O/T 0.00 0.00 0.00 0.00 0.00
But when I do a crosstab to get the date to go from left to right I can only
pull in one value. That part works great thanks to Duane Hookom. I was
thinking that if the table was set up different then I would be able to pull
in all this data.

Jeff said:
Both? Wouldn't EmployeeID be sufficient?

Regards

Jeff Boyce
Microsoft Office/Access MVP
I'm sorry I forgot to put in Employee ID in the schedule table. The
relationship links Employee name and Employye ID
Click to expand...
[quoted text clipped - 18 lines]
in
rows.
Click to expand...
Click to expand...
Click to expand...
 
A

abourg8646 via AccessMonster.com

Thank you. It worked great.

Duane said:
I think this can be done by first creating a normalizing union query of the
Schedule table. Something like:
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hrs Worked] As Hrs, "Hours Worked" as TimeType
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Contract Hrs], "Contract Hours"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hol], "Holiday"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Vac], "Vacation"
FROM Schedule
--- etc ---
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[O/T], "OT"
FROM Schedule;

You can then create a crosstab from this that uses the Employee info as Row
Headings, DateWkd as column headings, and Sum(Hrs) as the Value.

I'm not sure how to handle the Time Wkd since it looks to be text while
other values are numeric. I would also report the column headings as relative
dates rather than actual dates.
yes. I already have the database set up and I have a month of data in it. My
real problem is when I go to run a report it needs to look like this
Click to expand...
[quoted text clipped - 36 lines]
Click to expand...
 
Top