How to Calculating the time?

Question

Using MS Access 2007

Table1

ID Date Time

001, 20091111, 141625
001, 20091112, 122345
001, 20091112, 180000
001, 20091113, 100000
001, 20091113, 120000
001, 20091113, 180000
001, 20091113, 160000
...,

From the above table i want to add one more column as a status. Status Should be In or Out. which Means For 20091111 - Only one time, So It should be 141625 - In For 20091112 - 2 times, So It should be 122345 - In, 180000 - Out For 20091113 - 4 times, So 10000 - In, 120000- Out, 180000 - Out, 160000 - In

Which means

Date with one time means, the time should be In, 
Date with two times means, the First time should be In, Second time should be out
Date with three times means, the first time should be In, Second time should be out,
third time should be In.
Date with four times means, the first time should be In, Second time should be out,
third time should be In, Fourth time should be out.

For Example

Date Time 

20091010 100000
20091010 180000
20091010 120000

It should order the date by ascending, then it will display First one In, Second One is Out, Third One is In.

Output.

Date Time Status

20091010 100000 In
20091010 180000 In
20091010 120000 Out

Expected Output

ID Date Time status

001, 20091111, 141625 In
001, 20091112, 122345 In
001, 20091112, 180000 Out
001, 20091113, 100000 In
001, 20091113, 120000 Out
001, 20091113, 180000 Out
001, 20091113, 160000 In
...,

How to make a access query for this condition?

Need Query Help

Answer 1

Sometimes being unable to write a simple query (bad SQL DML code...) against a schema is a code 'smell'. In this case your design is flawed (...caused by bad SQL DDL code).

Your design has update anomalies. Consider that deleting an 'out' row would cause subsequent rows for the entity to be implicitly (and erroneously) inverted.

You should have columns for both in_date and out_date on the same row. You then need all the constraints that go with a temporal database design i.e. out_date cannot be before in_date, no overlapping periods for the same entity, etc.

Answer 2

0) Set status to Null for all records

update table1 set status=Null;

1) assign 'In' to the first record for each date:

update Table1 as tab_outer set status='In' where Time=
 (select min(Time) from Table1 as tab_inner 
 where tab_inner.date=tab_outer.date);

2) assign 'Out' to the first record for each date that has no status:

update Table1 as tab_outer set status='Out' where Time=
 (select min(Time) from Table1 as tab_inner 
 where tab_inner.date=tab_outer.date and Time is null);

3) assign 'In' to the first record for each date that has no status:

update Table1 as tab_outer set status='In' where Time=
 (select min(Time) from Table1 as tab_inner 
 where tab_inner.date=tab_outer.date and Time is null);

4) assign 'Out' to the first record for each date that has no status:

update Table1 as tab_outer set status='Out' where Time=
 (select min(Time) from Table1 as tab_inner 
 where tab_inner.date=tab_outer.date and Time is null);

Answer 3

You will need subselects to achieve this. I'm not familiar with MS Access 2007 SQL but maybe you are able to convert it to correct syntax:

SELECT ID, Date, Time, 
CASE ((SELECT count(*) FROM table1 as subQuery WHERE subQuery.Date = outerQuery.Date AND subQuery.Time > outerQuery.Time order by Time) % 2)
  WHEN 0 THEN 'In'  
  ELSE 'Out'
END as Status
FROM table1 AS outerQuery

The subselect will query the number of predecessors rows for the outer selects Time and Date. The CASE statement will then evaluate this ranking with the modulo operator to ether print In if the result is 0 (even number of predecessors) or Out if 1 (odd number of predecessors).

Hope that helps...