If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
% 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 |
#2
|
|||
|
|||
% query based on dates.
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 |
#3
|
|||
|
|||
% query based on dates.
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 |
#4
|
|||
|
|||
% query based on dates.
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 |
#5
|
|||
|
|||
% query based on dates.
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 |
#6
|
|||
|
|||
% query based on dates.
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 |
#7
|
|||
|
|||
% query based on dates.
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 |
Thread Tools | |
Display Modes | |
|
|