>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.
>
> --
> Arthur Bourgeois Jr
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1
>

>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.
>[quoted text clipped - 12 lines]
>> value under it. I need for everything in the schedule table to show up in
>> rows.

> I'm sorry I forgot to put in Employee ID in the schedule table. The
> relationship links Employee name and Employye ID
>
> Jeff Boyce wrote:
>>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.
>>[quoted text clipped - 12 lines]
>>> value under it. I need for everything in the schedule table to show up
>>> in
>>> rows.
>
> --
> Arthur Bourgeois Jr
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1
>

"abourg8646 via AccessMonster.com" wrote:

> 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 Boyce wrote:
> >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
> >[quoted text clipped - 18 lines]
> >>>> in
> >>>> rows.
>
> --
> Arthur Bourgeois Jr
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1
>
>

>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
>[quoted text clipped - 36 lines]
>> >>>> in
>> >>>> rows.