Returning Multiple Records (Need Advanced Guru Help)

  • Thread starter Thread starter fwhilton
  • Start date Start date
F

fwhilton

I searched hard for this and can not seem to find anything.
This is a hard request.. so could an advanced Access guru help?

I need to return only the records that have 3 days (or more) with the same
(or more) code(s).

EXAMPLE DATA:

Date CODE
1/1/2007 SICK
1/2/2007 SICK
1/3/2007 SICK
1/4/2007 SICK
1/1/2007 S-DIS
1/2/2007 S-DIS
1/1/2007 VAC
1/2/2007 VAC

From the sample data above, I want to only return the SICK code records IF
the SICK code only occurrs 3 days in a row. So, I have to also evaluate the
next reocord's data and code.

But the twist is... I have to also get all the records if there is more then
three.
AND... I must take into account the weekends!!

I believe I will have to do this with VBA in some loop and then store in an
array avriables, then write records to a new table.

Whew!!
This is a hard one!

Thanks for any help.
Frank @ TD Banknorth
 
Frank, you will need a table of working dates. Omit weekends and public
holidays.

You can then create a query that returns, for every date, the previous
working date and the next working date. Assuming a table named tblWorkDate,
with a date/time field named WorkDate, create a query that uses this table.
Drag the WorkDate field into the grid. In the next column, enter this into
the Field row:
PriorWorkDate: (SELECT Max(WorkDate)
FROM tblWorkDate AS Dupe
WHERE Dupe.WorkDate < tblWorkDate.WorkDate)
and in the next column:
NextWorkDate: (SELECT Min(WorkDate)
FROM tblWorkDate AS Dupe
WHERE Dupe.WorkDate > tblWorkDate.WorkDate)
Save the query.

You can now create a query using your existing table + this query, joined on
the WorkDate field, and you can now see the PriorWorkDate and NextWorkDate.
You can therefore write another subquery to see if there was a record with
the same CODE on the previous work day. Similarly for the next work day.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html
 
Thanks Allen,

I'll see if I can get it to work.

Frank

Allen said:
Frank, you will need a table of working dates. Omit weekends and public
holidays.

You can then create a query that returns, for every date, the previous
working date and the next working date. Assuming a table named tblWorkDate,
with a date/time field named WorkDate, create a query that uses this table.
Drag the WorkDate field into the grid. In the next column, enter this into
the Field row:
PriorWorkDate: (SELECT Max(WorkDate)
FROM tblWorkDate AS Dupe
WHERE Dupe.WorkDate < tblWorkDate.WorkDate)
and in the next column:
NextWorkDate: (SELECT Min(WorkDate)
FROM tblWorkDate AS Dupe
WHERE Dupe.WorkDate > tblWorkDate.WorkDate)
Save the query.

You can now create a query using your existing table + this query, joined on
the WorkDate field, and you can now see the PriorWorkDate and NextWorkDate.
You can therefore write another subquery to see if there was a record with
the same CODE on the previous work day. Similarly for the next work day.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html
I searched hard for this and can not seem to find anything.
This is a hard request.. so could an advanced Access guru help?
Click to expand...
[quoted text clipped - 33 lines]
Thanks for any help.
Frank @ TD Banknorth
Click to expand...
Click to expand...
 
Back
Top