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!

 

[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!

 

[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!

 

[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!

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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!

--
HTH

Dale Fye

Message posted via AccessMonster.com