no iifs or buts???

[smehnert]

I am using Access 2000 on an ME...

I am trying to create columns from my rows based on date...

EmployeeNo
EmployeeName
ShiftDate
MinsWorked

There is a user entered start date called [Entered Date]

iif(ShiftDate = [Entered Date],(MinsWorked/60),0)

this works on the query, but then I want to do the same for day 2 day 3 etc.. for example ;

iif(ShiftDate = ([Entered Date]+1),(MinsWorked/60),0)

but I am being told that this is to complicated... is there any solution to this... I want to do this for a 7 day week?

Any help would be appreciated

 

[ColinEssex]

If you want to calculate days/hours/mins then you need a function. Search about, its been posted many times.

Dividing by 60 will only give hours and a decimal of an hour not the remainder minutes.

Col

 

[smehnert]

thanks Colin... but NO!

I want to have the employee name on a row, and the the date as columns...
e.g. Day0, Day1, Day2 etc...
and then Names as rows and the values would be the hours...

etc...

 

[ColinEssex]

thanks Colin... but NO!

I want to have the employee name on a row, and the the date as columns...
e.g. Day0, Day1, Day2 etc...

Thats a spreadsheet then, not a relational database. In a database you don't have a new column per day (like you do in Excel)

Col

 

[neileg]

Try using the DateAdd() function.

 

[gemma-the-husky]

if you want to do this for a week at a time say, access has a ready made function called partition which will split your data into defineable bands - in your case a day at a time - that might help