% query based on dates.

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 a -

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

Anyone able to help?

Thanks,
Andy.


--
Message posted via http://www.accessmonster.com

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

Andy.


--
Murdoc

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200703/1

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
--
Email address is not valid.
Please reply to newsgroup only.


"MurdocUK" wrote:

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 a -

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

Anyone able to help?

Thanks,
Andy.


--
Message posted via http://www.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.

--
Murdoc

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200703/1

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

--
Email address is not valid.
Please reply to newsgroup only.


"MurdocUK via AccessMonster.com" wrote:

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.

--
Murdoc

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200703/1

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.

--
Murdoc

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200703/1

Perhaps the current SQL will help?

SELECT dbo_Enforcements_SiteVisits.ID, dbo_enforcements.DateRecd,
dbo_Enforcements_SiteVisits.Date_R, dbo_Enforcements_SiteVisits.Officer,
dbo_enforcements.Year, dbo_enforcements.Case_No
FROM dbo_Enforcements_SiteVisits INNER JOIN dbo_enforcements ON
dbo_Enforcements_SiteVisits.Ref = dbo_enforcements.Ref
WHERE (((dbo_enforcements.DateRecd) Is Not Null And (dbo_enforcements.
DateRecd) Like [Please enter the month as */(Month)/(Year)] & "*") AND (
(dbo_Enforcements_SiteVisits.Date_R) Is Not Null) AND (
(dbo_Enforcements_SiteVisits.Officer)=[Case Officer]));

Andy.

--
Murdoc

Message posted via http://www.accessmonster.com