Using if statement to fill a field depending on other records in the table...

[Ketonekevin]

Hello all,

I have a simple table as follows:

Field 1 - Name - text field
Field 2 - Date - Date/time field
Field 3 - Hours Worked - Number field

Now what I would like to do with a query is this. Look at each record, if there is no record for the same name for yesterday, then place the value of 1 in a new field. If there is no record for the same name for tomorrow, then place the value 1 in the new field. If there is a record for the same name both yesterday and tomorrow, then place the value of 2 in the new field.

I work at a facility where it is a week on/week off schedule. Each day a person is to have 2 documented contacts with their supervisor. If its the first day or last day of their week at work, they only work partial days so the number of contacts needs to be just 1. I am trying to write a query that will use this logic to automatically place the number of contacts required in the fourth field of the table.

Thanks in advance for the assistance...any help is appreciated!!!

 

[raskew]

Hi -

Sounds as if you're working at a 'real fun place'. Do they clock bathroom breaks too?

Moving on-

Why/How would there be a record for 'tomorrow'? We don't know 'til we get there.

Or, maybe I just don't understand.

Please clarify - Bob

 

[ACMAIN_CHM]

you can use the DCOUNT()
select [Name],[Date],[Hours],dcount('[Name]','yourTable','[Name]="'&[Name]&'" and ([Date]+1=#'&[Date]&'# OR [Date]-1=#'&[Date]&'#)')+1 newField
from yourTable

or you can standard SQL as


select a.[Name],a.[Date],a.[Hours],count(b.[Name])+1 as newField
from yourTable a left join yourTable b on a.[Name]=b.[Name] and (a.[Date]=b.[Date]-1 or a.[Date]=b.[Date]+1)


best regards
ACMAIN

 

[Ketonekevin]

Hi -

Sounds as if you're working at a 'real fun place'. Do they clock bathroom breaks too?

Moving on-

Why/How would there be a record for 'tomorrow'? We don't know 'til we get there.

Or, maybe I just don't understand.

Please clarify - Bob

Actually, it is a fun place to work...

What this is for is supervisors logging their contacts with their workers as part of the safety program here. At the end of the month, we collect the data from the supervisors. From that pile of data I am trying to calculate a percent compliance for each supervisor in making their daily contacts as part of an overall safety compliance program. So in answer to your second question, the report is not being run daily, only after the end of a month when all the data for the month is available.

Hope this clarification helps...