Laying out fields Hoziontally

Hello all,

Ok I'm having a mental block. I'm designing a rostering database, the table
Work has these fields

ID, Name, WeekComencing, WorkDay, StartTime, EndTime

I have a form an un-bound form that has the days of the week across the top,
a field for a name and then start / end times. I then have a little coding
that transfers that data into the table.

What I want to do is then show that data in a normal roster like view;

Name Monday Tuseday Wednesday ....
Name Start End Start End Start End

I'm sure you get the idea. Now to my problem Without adding to the table
fields for each day of the week (start / end / annual leave) I can not for
the life of me get one line per person with data corisponding to the days of
the week.

I have tried having the "Name" field be a Query (grouped) and then having
each field as a Dlookup("[Start]", "work", "[workday]=" & (work day
corrisponding to the day) AND "Name" =" & (Name Field) ) but this just picked
up the first entry in the table.

Any ideas??

Pnut

hi pnut,

On 31.01.2010 18:44, thepnut wrote:
I have a form an un-bound form that has the days of the week across the top,
a field for a name and then start / end times. I then have a little coding
that transfers that data into the table.

What I want to do is then show that data in a normal roster like view;

Name Monday Tuseday Wednesday ....
Name Start End Start End Start End
You may take a look at the Crosstab Query Wizard.

I'm sure you get the idea. Now to my problem Without adding to the table
fields for each day of the week (start / end / annual leave) I can not for
the life of me get one line per person with data corisponding to the days of
the week.
I'm not quite sure what you exactly mean, especially WeekComencing,
WorkDay makes no sense to me. But if you have such a form layout for
editing data, you can use a SQL like

UPDATE yourTable
SET Start = value
WHERE Name = current AND WorkDay = current

in the After Update event of the TextBoxes.

Any ideas??
Maybe I need a more precise description, as I'm not sure that I really
understand your problem.


mfG
-- stefan --

Hi Stefan,

Sorry for being so wooly LOL

Let me true again, each record in the TB_Work is a

ID is primary key (AutoNumber)
StaffName - ID number from the Staff Table
WeekComencing - We report everything from a Monday by a week (so this is the
week begining date)
WorkDate - is the date of the day the staff member is working
Start Time
End Time
Then there are a couple of other tickbox's that allow for a description if
the staff member is not in.

Everytime we enter a staff members week of work, I want the sub form to show
a "table" of everyone working in that week, separated by day (showing end and
start times) arranged Mon Tue Wed Thur Frid Sat Sun horizontally.

The problem with a Crosstab query is, I can show a staff member name and the
days of the week, but only the start time or end time, not both.

"Stefan Hoffmann" wrote:

hi pnut,

On 31.01.2010 18:44, thepnut wrote:
I have a form an un-bound form that has the days of the week across the top,
a field for a name and then start / end times. I then have a little coding
that transfers that data into the table.

What I want to do is then show that data in a normal roster like view;

Name Monday Tuseday Wednesday ....
Name Start End Start End Start End
You may take a look at the Crosstab Query Wizard.

I'm sure you get the idea. Now to my problem Without adding to the table
fields for each day of the week (start / end / annual leave) I can not for
the life of me get one line per person with data corisponding to the days of
the week.
I'm not quite sure what you exactly mean, especially WeekComencing,
WorkDay makes no sense to me. But if you have such a form layout for
editing data, you can use a SQL like

UPDATE yourTable
SET Start = value
WHERE Name = current AND WorkDay = current

in the After Update event of the TextBoxes.

Any ideas??
Maybe I need a more precise description, as I'm not sure that I really
understand your problem.


mfG
-- stefan --
.

I'd suggest using 7 separate instances of the same continuous forms view
subform, each restricted to one day of the week. You can do this by linking
the first to WeekCommencing, the others to 6 hidden text boxes, the first
with a ControlSource of:

=DateAdd("d",1,[WeekCommencing])

and so on until the last with a ControlSource of:

=DateAdd("d",6,[WeekCommencing])

I've done this using subforms arranged vertically for a 'wall calendar' type
form, but you'd just have to arrange the subforms side by side across the
parent form to give the layout you want.

Ken Sheridan
Stafford, England

thepnut wrote:
Hi Stefan,

Sorry for being so wooly LOL

Let me true again, each record in the TB_Work is a

ID is primary key (AutoNumber)
StaffName - ID number from the Staff Table
WeekComencing - We report everything from a Monday by a week (so this is the
week begining date)
WorkDate - is the date of the day the staff member is working
Start Time
End Time
Then there are a couple of other tickbox's that allow for a description if
the staff member is not in.

Everytime we enter a staff members week of work, I want the sub form to show
a "table" of everyone working in that week, separated by day (showing end and
start times) arranged Mon Tue Wed Thur Frid Sat Sun horizontally.

The problem with a Crosstab query is, I can show a staff member name and the
days of the week, but only the start time or end time, not both.

hi pnut,

[quoted text clipped - 29 lines]
-- stefan --
.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

Hey Ken,

I'll give it ago tomorrow

Thanks

"KenSheridan via AccessMonster.com" wrote:

I'd suggest using 7 separate instances of the same continuous forms view
subform, each restricted to one day of the week. You can do this by linking
the first to WeekCommencing, the others to 6 hidden text boxes, the first
with a ControlSource of:

=DateAdd("d",1,[WeekCommencing])

and so on until the last with a ControlSource of:

=DateAdd("d",6,[WeekCommencing])

I've done this using subforms arranged vertically for a 'wall calendar' type
form, but you'd just have to arrange the subforms side by side across the
parent form to give the layout you want.

Ken Sheridan
Stafford, England

thepnut wrote:
Hi Stefan,

Sorry for being so wooly LOL

Let me true again, each record in the TB_Work is a

ID is primary key (AutoNumber)
StaffName - ID number from the Staff Table
WeekComencing - We report everything from a Monday by a week (so this is the
week begining date)
WorkDate - is the date of the day the staff member is working
Start Time
End Time
Then there are a couple of other tickbox's that allow for a description if
the staff member is not in.

Everytime we enter a staff members week of work, I want the sub form to show
a "table" of everyone working in that week, separated by day (showing end and
start times) arranged Mon Tue Wed Thur Frid Sat Sun horizontally.

The problem with a Crosstab query is, I can show a staff member name and the
days of the week, but only the start time or end time, not both.

hi pnut,

[quoted text clipped - 29 lines]
-- stefan --
.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

.

Pnut,

You could use a flex grid control which can do what you want. See :-

http://www.rogersaccesslibrary.com/f...22 893c7a6f24

for an example (click on Projects - Time Sheet Management).

HTH

Peter Hibbs.

On Sun, 31 Jan 2010 16:18:01 -0800, thepnut
wrote:

Hey Ken,

I'll give it ago tomorrow

Thanks

"KenSheridan via AccessMonster.com" wrote:

I'd suggest using 7 separate instances of the same continuous forms view
subform, each restricted to one day of the week. You can do this by linking
the first to WeekCommencing, the others to 6 hidden text boxes, the first
with a ControlSource of:

=DateAdd("d",1,[WeekCommencing])

and so on until the last with a ControlSource of:

=DateAdd("d",6,[WeekCommencing])

I've done this using subforms arranged vertically for a 'wall calendar' type
form, but you'd just have to arrange the subforms side by side across the
parent form to give the layout you want.

Ken Sheridan
Stafford, England

thepnut wrote:
Hi Stefan,

Sorry for being so wooly LOL

Let me true again, each record in the TB_Work is a

ID is primary key (AutoNumber)
StaffName - ID number from the Staff Table
WeekComencing - We report everything from a Monday by a week (so this is the
week begining date)
WorkDate - is the date of the day the staff member is working
Start Time
End Time
Then there are a couple of other tickbox's that allow for a description if
the staff member is not in.

Everytime we enter a staff members week of work, I want the sub form to show
a "table" of everyone working in that week, separated by day (showing end and
start times) arranged Mon Tue Wed Thur Frid Sat Sun horizontally.

The problem with a Crosstab query is, I can show a staff member name and the
days of the week, but only the start time or end time, not both.

hi pnut,

[quoted text clipped - 29 lines]
-- stefan --
.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

.