Generating a date into the future

[Dean Mattoon]

Hello all,

I have an Access database which I created to handle all of our stormwater
permiting requirements and there are a couple of things I am struggling with
that maybe some of you access gurus can help me with. The first one is this:
I have a table for inspections which I have to perform at certain intervals
(i.e. once per week, once per month, etc) and I am trying to set up a field
or query or something that will automatically generate this next inspection
date for me. I have already made the base data on the site, including how
often I need to inspect the site, and there is a column for showing the next
inspection date that I manually replace once and inspection is done. I also
have a query built that just shows the name of the place, how often it is
inspected and the next inspection date. It is this query that I would like to
have an automatic date generator on. Is this possible?

Thanks so much in advance!

 

[Dale_Fye via AccessMonster.com]

Sure, it is possible.

The way I handle this on my applications that have inspection schedules is
that I have a table that indicates the type of inspection with the following
data fields(Insp_ID - Autonumber, Insp_Type - usually a text field;
Insp_IntervalType - text field using the same formats that are used by the
DateAdd function for week, month, year); and Insp_Interval - the number of
intervals between inspections). Then, for each permit, I would store the
Insp_ID field, which would allow me to create a query that looks something
like:

SELECT Permit.ID, Permit.IssuedTo, Permit.LastInspection,
DateAdd(NZ(InspType.Insp_IntervalType, "d"),
NZ(InspType.Insp_Interval, 0),
Permit.LastInspection) as NextInspection
FROM Permit
LEFT JOIN InspType
ON Permit.Insp_ID = InspType.Insp_ID

I used the Left Join for those instances where an Insp_ID is not recorded for
a particular permit, which would just generate a Next Inspection that is the
same as the Last Inspection. Then, in the report that uses this query, I'd
use conditional formatting to color code those records where the
NextInspection is the same as the LastInspection. This would clue you or
someone else that they need to update the permit to indicate the type of
inspection.

HTH
Dale

 

[Dean Mattoon]

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

 

[Dale_Fye via AccessMonster.com]

There is no coding, only a SQL statement for the query.

I did use the DateAdd( ) function, which requires three parameters, an
interval type (in this case I used "d" for days), a number of intervals (I
used 0 to ensure that the nextDate would be the same as the last data), and a
StartDate).

I used the NZ( ) function within the DateAdd function to ensure that if no
Insp_ID was recorded for a particular permit, that the NULL values returned
as a result of the LEFT JOIN would not cause an error within the DateAdd
function.

HTH
Dale

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

Sure, it is possible.

[quoted text clipped - 42 lines]

 

[Dean Mattoon]

Dale,

Do you keep the last inspection date within the main table for the site or
the table for the site inspection. In my database, I have a table for
inspections linked to the table of the main site data. Also, when I added
this date add to the query of sites, it added the same date to all of the
sites regardless of what all the other site dates said...

 

[Dean Mattoon]

Wow, I must be a lot less proficient in access than I originally thought,
because I don't understand how to do any of this. Thanks so much for the
posts. I guess at least I know it can be done somehow.

There is no coding, only a SQL statement for the query.

I did use the DateAdd( ) function, which requires three parameters, an
interval type (in this case I used "d" for days), a number of intervals (I
used 0 to ensure that the nextDate would be the same as the last data), and a
StartDate).

I used the NZ( ) function within the DateAdd function to ensure that if no
Insp_ID was recorded for a particular permit, that the NULL values returned
as a result of the LEFT JOIN would not cause an error within the DateAdd
function.

HTH
Dale

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

Sure, it is possible.

[quoted text clipped - 42 lines]

Thanks so much in advance!

 

[Dale_Fye via AccessMonster.com]

Well, I simplified it because I was not sure of your data structure.

Actually, I would probably have a Permits table that contains the basic
information for each permit.

Then, I'd have an Inspections table, which uses the PermitID as the foreign
key to the permits table, and contains each InspectionDate, and the results
(and maybe notes) from each inspection. From this table, I would probably
create a query to give me the date of the last inspection, something like:

SELECT Permit.Permit_ID, Permit.Insp_Type_ID, Max(InspectionDate) as
LastInspection
FROM Permit
INNER JOIN Inspections
ON Permit.Permit_ID = Inspections.Permit_ID
GROUP BY Permit_ID

Then, I would join this query to the InspectionTypes table

Can you post the SQL of your query so I can take a look at it?

Dale

Dale,

Do you keep the last inspection date within the main table for the site or
the table for the site inspection. In my database, I have a table for
inspections linked to the table of the main site data. Also, when I added
this date add to the query of sites, it added the same date to all of the
sites regardless of what all the other site dates said...

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

[quoted text clipped - 45 lines]

 

[Dean Mattoon]

Thanks, yes I will try to do that. Actually it is a bit more complicated for
me because I am using GPS / GIS within the data so everything is set up in
Arc Catalog. Then I am generating next inspection and reporting data from the
access database. Not sure if that makes any difference. I got the day and
month to work, although I would have to generate two separate queries because
some of my inspections for sites are weekly while some are quarterly. I think
right now looking at the SQL is confusing me the most because I don't know
how your tables relate to mine.

Well, I simplified it because I was not sure of your data structure.

Actually, I would probably have a Permits table that contains the basic
information for each permit.

Then, I'd have an Inspections table, which uses the PermitID as the foreign
key to the permits table, and contains each InspectionDate, and the results
(and maybe notes) from each inspection. From this table, I would probably
create a query to give me the date of the last inspection, something like:

SELECT Permit.Permit_ID, Permit.Insp_Type_ID, Max(InspectionDate) as
LastInspection
FROM Permit
INNER JOIN Inspections
ON Permit.Permit_ID = Inspections.Permit_ID
GROUP BY Permit_ID

Then, I would join this query to the InspectionTypes table

Can you post the SQL of your query so I can take a look at it?

Dale

Dale,

Do you keep the last inspection date within the main table for the site or
the table for the site inspection. In my database, I have a table for
inspections linked to the table of the main site data. Also, when I added
this date add to the query of sites, it added the same date to all of the
sites regardless of what all the other site dates said...

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

[quoted text clipped - 45 lines]

Thanks so much in advance!

 

[Dean Mattoon]

would it be easier for me to send you my access database for you to look at?
or at least the two linked tables?

Well, I simplified it because I was not sure of your data structure.

Actually, I would probably have a Permits table that contains the basic
information for each permit.

Then, I'd have an Inspections table, which uses the PermitID as the foreign
key to the permits table, and contains each InspectionDate, and the results
(and maybe notes) from each inspection. From this table, I would probably
create a query to give me the date of the last inspection, something like:

SELECT Permit.Permit_ID, Permit.Insp_Type_ID, Max(InspectionDate) as
LastInspection
FROM Permit
INNER JOIN Inspections
ON Permit.Permit_ID = Inspections.Permit_ID
GROUP BY Permit_ID

Then, I would join this query to the InspectionTypes table

Can you post the SQL of your query so I can take a look at it?

Dale

Dale,

Do you keep the last inspection date within the main table for the site or
the table for the site inspection. In my database, I have a table for
inspections linked to the table of the main site data. Also, when I added
this date add to the query of sites, it added the same date to all of the
sites regardless of what all the other site dates said...

Thanks! I am not sure I understood all of the coding, but I think I do and
that is excactly what I am looking for. Thank you so much!

[quoted text clipped - 45 lines]

Thanks so much in advance!

 

[Dale_Fye via AccessMonster.com]

Prefer not to do that, but if you can list the table names, field names, and
data types (at least of the tables and fields that are relevant) then I can
probably figure it out.

Dale

would it be easier for me to send you my access database for you to look at?
or at least the two linked tables?

Well, I simplified it because I was not sure of your data structure.

[quoted text clipped - 32 lines]