Create query against multiple date fields

[fgwiii]

Hello,

I need to create a query that will identify about 10 individual date fields
to see if the dates are: greater than 30 days prior to or 1 day after the
<pdate>.

Any help would be greatly appreciated.

Thanks,

Fred

 

[KARL DEWEY]

If you have 10 date fields then you have a spreadsheet and not a relational
database. You need to revise your structure.
Post what your data collection objective is and someone can suggest a better
way unless your need is actually a spreadsheet then use Excel instaead of
Access.

 

[fgwiii]

While I can see your point, I still need to identify which records contain
dates that meet this criteria. (BTW this is a relational database)

 

[Jerry Whittle]

If you have 10 different date fields, there's a very good chance that your
tables are design wrong, which is causing your difficulty. I'm assuming that
your table looks something like below. If so, it is wrong.

ProjectID pDate StartDate DesignDate ProdDate ShipDate
123 1/1/09 2/1/09 3/1/09 4/1/09 5/1/09

What you should have is something like this:

tblProjectDates
ProjectID DateType ProjectDates
123 pdate 1/1/09
123 Start 2/1/09
123 Design 3/1/09
123 Prod 4/1/09
123 Ship 5/1/09

Then the query would be something simple like:

Select *
From tblProjectDates
Where ProjectDates Between [pdate] -30
And [pdate] +1 ;

Actually the above SQL would need to be a little more complicated if you
what to base it on the pdate for that particular ID.

 

[KARL DEWEY]

I also believe your data is as Jerry thinks so you can use a union query to
re-arrange it.
qryProjDates --
SELECT ProjectID, "pDate" AS DateType, pDate AS ProjectDates
FROM YourTable
UNION ALL SELECT ProjectID, "StartDate" AS DateType, StartDate AS ProjectDates
FROM YourTable
UNION ALL SELECT ProjectID, "DesignDate" AS DateType, DesignDate AS
ProjectDates
FROM YourTable
UNION ALL SELECT ProjectID, "ProdDate" AS DateType, ProdDate AS ProjectDates
FROM YourTable
UNION ALL SELECT ProjectID, "ShipDate" AS DateType, ShipDate AS ProjectDates
FROM YourTable;

SELECT ProjectID, DateType, ProjectDates
FROM YourTable LEFT JOIN qryProjDates ON YourTable.ProjectID =
qryProjDates.ProjectID
WHERE ProjectDates Between (YourTable.pDate - 30) AND (YourTable.pDate + 1);

 

[fgwiii]

This is how the data looks:
The data is laid out like this:
person, dt1, dt2, dt3, dt4, dt5, dt6, pdate,
rec1, 7/6/09, 5/6/09, 4/12/09, 5/5/09, 9/12/09, 5/15/08, 6/6/09
rec2, 7/8/08, 2/3/09, 8/2/08, 9/7/08, 1/12/09, 3/11/09, 2/7/09

Currently there are only 95 records, and I could probably transpose them
(althought I don't know what the cap is for number of columns). But in the
future there could potentially be several thousand records. Hence the reason
we chose access to join the tables and produce queries to identify records.

If you have 10 different date fields, there's a very good chance that your
tables are design wrong, which is causing your difficulty. I'm assuming that
your table looks something like below. If so, it is wrong.

ProjectID pDate StartDate DesignDate ProdDate ShipDate
123 1/1/09 2/1/09 3/1/09 4/1/09 5/1/09

What you should have is something like this:

tblProjectDates
ProjectID DateType ProjectDates
123 pdate 1/1/09
123 Start 2/1/09
123 Design 3/1/09
123 Prod 4/1/09
123 Ship 5/1/09

Then the query would be something simple like:

Select *
From tblProjectDates
Where ProjectDates Between [pdate] -30
And [pdate] +1 ;

Actually the above SQL would need to be a little more complicated if you
what to base it on the pdate for that particular ID.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I need to create a query that will identify about 10 individual date fields
to see if the dates are: greater than 30 days prior to or 1 day after the
<pdate>.

Any help would be greatly appreciated.

Thanks,

Fred

 

[John Spencer]

This should select records where one of the dates is out of range

SELECT person, dt1, dt2, dt3, dt4, dt5, dt6, pdate
FROM [TheTable]
WHERE Dt1 < ("d",-30,Pdate) Or Dt1 > DateAdd("d",1,PDate)
OR Dt2 < ("d",-30,Pdate) Or Dt2 > DateAdd("d",1,PDate)
OR Dt3 < ("d",-30,Pdate) Or Dt3 > DateAdd("d",1,PDate)
OR Dt4 < ("d",-30,Pdate) Or Dt4 > DateAdd("d",1,PDate)
OR Dt5 < ("d",-30,Pdate) Or Dt5 > DateAdd("d",1,PDate)
OR Dt6 < ("d",-30,Pdate) Or Dt6 > DateAdd("d",1,PDate)

If you need to know which of the dates is out of range then you need to modify
the select clause or handle it in a report or on a form using conditional
formatting to highlight the dates that are out of range.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

This is how the data looks:
The data is laid out like this:
person, dt1, dt2, dt3, dt4, dt5, dt6, pdate,
rec1, 7/6/09, 5/6/09, 4/12/09, 5/5/09, 9/12/09, 5/15/08, 6/6/09
rec2, 7/8/08, 2/3/09, 8/2/08, 9/7/08, 1/12/09, 3/11/09, 2/7/09

Currently there are only 95 records, and I could probably transpose them
(althought I don't know what the cap is for number of columns). But in the
future there could potentially be several thousand records. Hence the reason
we chose access to join the tables and produce queries to identify records.

I fully agree with the other respondents; your table structure is wrong and
will lead you into no end of trouble. If you'll be adding more date fields in
the future you're on the wrong track! As suggested, you need two tables in a
one to many relationship, with one date per record in the "many" side table;
"fields are expensive, records are cheap".

That said... while you're restructuring the tables... you can solve your
immediate problem by creating a Query with the criterion

[Enter date:]

under each date field, on *different* rows of the query grid to use OR logic.

 

[Piet Linden]

(BTW this is a relational database)

Relational is as relational does. No, it isn't.