Viewing 3 different reports on one.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three tables (tblPosition, tblVacation, tblWeekday) I also have a
report, thanks to the help of Duane Hookum, that shows what position an
employee has to work and on what date. I also would like to shoe if the
employee is regulary scheduled off (based on tblWeekday) and what days they
have vacation (based on tblVacation). Weekdays are assigned to an employee
if they CAN work on a given weeday, so it would be the days that are not
assigned. Vacations are based on the day an employee wants OFF. Can someone
point me in a direction?
 
Depending on your table structures, you might be able to use a union query
to combine all three tables to create your report.

I somewhat question why you might have 3 fairly similar tables.
 
Thanks! The table aren't really similar. One is for the availability of the
employee, one is the days and shifts that the employee can work, and the
other is the dates that an employee has requested off. Do you think my table
structure is wrong? Basically I am trying to make a schedule that will
visually display if an employee is scheduled and what posistion, if they are
not schedule because of vacation, or because that is not a day the employee
can work. Any suggestions?

I have never used a union query. Could you give me some sort of example?

Thanks again!
 
You haven't provided a legitimate excuse for using multiple tables. Check
Help on union query. The basic format must be typed into the SQL view of
your query design.

SELECT FieldA, FieldB, FieldC
FROM tblA
UNION ALL
SELECT FieldA, FieldB, FieldX
FROM tblB
UNION ALL
SELECT Null, FieldB, FieldC
FROM tblC;
 
When you are talking about multiple tables, are you refering to the report or
to the table structure for the database? If it is for the report, I don't
know how to tell it to display vacation if the employee has one schduled, or
to display an "X" on thier normal scheduled days off. I'm sorry I am very
lost.
 
Maybe you should provide us with a few sample records from each table. Then
explain how these records should appear in a report.
 
Thanks again! Here is what I have. If you cut and paste it into NotePad,
everything should allign correctly.

tblWeekdaysAvailable
WeekDaysAvailableID WeekdayID ShiftID EmployeeID
6 1 1 1
7 2 1 1
8 2 2 1


tblWeekday
WeekDayID WeekDay
1 Sunday
2 Monday


tblVacation
VacationID VacationDate EmployeeID ShiftID
3 9/6/2005 1 2
4 9/7/2005 1 1


tblSchedule
ScheduleID EmployeeID PositionID ShiftID ScheduledTimeIn SchedDate
40 1 2 1 09:00 AM 9/6/2005
43 2 3 2 5:00 PM 9/7/2005
34 2 4 1 10:30 AM 9/8/2005


tblPositionAllowed
PositionAllowedID EmployeeID PositionID ShiftID
6 1 21 1
9 1 22 1
10 1 4 1


tblPosition
PositionID ShiftID PositionName
2 1 Host 1 (Long)
3 2 Host 1 (Long)
4 1 Server 1(Long)


tblDates
ID RecordDate
1 9/6/2005
2 9/7/2005
3 9/8/2005
4 9/9/2005
5 9/10/2005


"X" = a day that is not in tblWeekdaysAvailable for that employee.
"--" = a day that the employee is just not scheduled, but is available to
work on that day.
"Vacation" = a date from tblVacation for that employee.
++++++++++++++++++++++++++++++++++++++Schedule+++++++++++++++++++++++++++++++++++++
Employee MOn 9/6/05 Tue 9/7/05 Wed 9/8/05 Thu 9/9/05 ...
-----------------------------------------------------------------------------------
Smith, Host 1 (Long) Vacation X --
John 9:00 AM

Vacation Server 1(Long) X --
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
Next Employee ...
++++++++++++++++++++++++++++++++++END
Schedule+++++++++++++++++++++++++++++++++++++
 
There is just too much wrapping etc that it is difficult to make
heads-or-tails of your desired display. I would question why you need
tblWeekDay since the weekdays can be calculated. Also, I think the ID field
in tblDates isn't necessary.
 
Back
Top