% query based on dates.

[MurdocUK]

Hi,

I need to run a query to find out % of site visits made within 10 days of
receipt of a case. Both fields are of the date type and the site visit date
needs to contain no NULL values.

The fields are: -

DateRecd = date of receipt of case
DateR = date of site visit

Anyone able to help?

Thanks,
Andy.
(e-mail address removed)

 

[MurdocUK]

Sorry, the DateR field is a text field and not a date field for some reason.

Andy.
(e-mail address removed)

 

[Dale Fye]

I assume, based on your question, that there are site visits that are made
more than 10 days after the receipt of the case, and you want to know what
percentage of the visits to a site (I assume you have a SiteID in your table)
after receipt of a case (and before receipt of another case for the same
site?) fall within the 10 day period.

Is there a chance that you will have multiple cases for a particular site?
If so, how do you want to handle this situation?

What is the format of your "DateRecd" field that is text (per your follow-on
note)?

Dale

 

[MurdocUK via AccessMonster.com]

Thanks for getting back to me.

The "DateRecd" filed is an access standard date field i.e. 09/03/2007.
Whilst the Date_R is in text format, we use the same as "DateRecd".

There can be site visits made after the "DateRecd" and you are correct in
stating I want to find the % of visits within 10 days of "DateRecd".

It is possible for a 2nd site visit but I would only be interested in the
first (earliest).

Andy.

 

[Dale Fye]

Andy,

What is your table structure?

For this type of a situation, I would probably have two tables, one to
identify basic case information, and another to track site visits. This
second one would also contain an Case_ID field to link it back to the Cases
table. If you can give me some more info about your structure, I'd be glad
to help.

Dale

 

[MurdocUK via AccessMonster.com]

Dale,

One table is called dbo_enforcements and has only 3 fields used in the Query:
-

Year (Number, Long Int.) Case_No (Text) and Date_Recd (Date/Time)

The other table is called dbo_Enforcements_SiteVisits and the following
fields are used in the Query: -

ID (AutoNumber) Date_R (Text) Officer (Text)

Date_Recd is the date the case was recorded on the db and Date_R the date of
the site visit. There may be more site visits for each case however I would
only be interested in the first.

Thanks,
Andy.