Date Query to Calculate Term 1

[PeanutB7]

Is there a query (or series of querries) I can perform that will:

1.)Look at two columns of info per record from a input data table - first START DATE - second END DATE - and then calculate a "length of term" in months.
2.)Next I would like it to take a TOTAL HOURS column and divide the TOTAL HOURS column by the "length of term" to establish a "monthly unit" per record.
3.)Finally I need it to recognize a time period via a parameter query and include only the applicable "monthly unit" for the desired period.

This may take multiple queries which is fine but it would assist us in taking a snapshot of a demand capacity comparison for different time frames.

Any and all help would be greatly appreciated.
Thank you in advance!

JB

 

[mp9]

For part 1, a query along the lines of:

SELECT DateDiff("m",[NameOfYourStartDateFieldHere],[NameOfYourEndDateFieldHere]) AS DateDifference
FROM NameOfYourTableHere;

HTH

http://www22.brinkster.com/accessory/

 

[PeanutB7]

Thank you for the info. Should the code be placed in the criterea section of the design view Query? I will try this ASAP!

Thanks again.

 

[PeanutB7]

HTH,

Thank you I pasted the code into SQL View and it worked like a dream. Any help on the next parts of the equation would be greatly appreciated!!!!!!

JB

 

[mp9]

To combine part 1 and 2, try this (untested) query:

SELECT DateDiff("m",[NameOfYourStartDateFieldHere],[NameOfYourEndDateFieldHere]) AS DateDifference, ([NameOfYourTotalHoursFieldHere]/DateDifference) AS MonthlyUnit
FROM NameOfYourTableHere;

If thsi works, come back to me and we'll work on part 3.

http://www22.brinkster.com/accessory/

 

[PeanutB7]

HTH,

Again you are a magician kind Sir(or Madam - can't assume.

You have successfully calculated my length of a project in days (I switched to days in order to be more accurate with two week jobts, etc) and divided the days into the total job hours estimated. I now have the time per day unit column that I required. My next issue is to use a parameter query to prompt the reviewer to input a snapshot of time desired to compare the capacity to demand. With only the start dates and end dates to define the timeline of each project I have to collect the projects that require workhours during the desired snapshot. That would include:
A. Projects that started prior to parameter input start date but end prior to parameter input end date.
B. Projects that started after parameter input start date but end prior to parameter input end date.
C. Projects that started after parameter input start date but end after parameter input end date.

It is essential that the query calculate the hours within the parameter start date and end date only and not the entire project estimated hour requirement.

Any projects, in their entirety, that start and end before or after the parameter selection must be disregarded.

I have accomplished the lion's share of this requirement (with your kind assistance) by during a parameter query Between [Start Date] And [End Date] in the End Date column. The only problem I do not think I am capturing all of the records.

Please help me in this last piece of my Access puzzle.

Thank you once again HTH!

JB

 

[mp9]

Yes, a parameter query is the way to go. For your three scenarios, I think you'd want these in your query's WHERE clause:

A: [Start Date] < [Enter the start date] AND [End Date] < [Enter the end date]

B: [Start Date] >= [Enter the start date] AND [End Date] < [Enter the end date]

C: [Start Date] >= [Enter the start date] AND [End Date] >= [Enter the end date]

Obviously you could play around with making < into <= and > into >= if you want to include (or not) the end days of the date range - probably best to try things as shown here and see how the results stack up to what you're expecting. So, for example, the complete query for scenario A might be:

SELECT DateDiff("m",[NameOfYourStartDateFieldHere],[NameOfYourEndDateFieldHere]) AS DateDifference, ([NameOfYourTotalHoursFieldHere]/DateDifference) AS MonthlyUnit
FROM NameOfYourTableHere
WHERE [NameOfYourStartDateFieldHere] < [Enter the start date] AND [NameOfYourEndDateFieldHere] < [Enter the end date];

And yes, I am a sir... though HTH is not my name, merely forum shorthand for "hope this helps".

HTH ;-)

http://www22.brinkster.com/accessory/

 

[PeanutB7]

MP9

Sorry but I am still suffering here. I have included my code for your review. Notice I have connected the Where statements with OR. If I use AND I get zero records. When I use OR I get records but the datdiff field is not accurately counting the days between the parameters specified. I am at the point of screaming! Please help if you have time. Thank you so very much!!

Thanks again,

JB


SELECT DateDiff("d",[Start Date],[End Date]) AS DateDifference, tbl_ProjectInformation.[Start Date], tbl_ProjectInformation.[End Date], [HPB Internal]/[DateDifference] AS [HPBi/d]
INTO tbl_Organ_ProjectInformation_Days1
FROM tbl_ProjectInformation
WHERE [Start Date]<[Select the Start Range Date] And [End Date]<[Select the End Range Date] or [Start Date]>=[Select the Start Range Date] And [End Date]<[Select the End Range Date] or [Start Date]>=[Select the Start Range Date] And [End Date]>=[Select the End Range Date];

 

[PHV]

Why not simply this ?
WHERE [Start Date]<=[Select the End Range Date] AND [End Date]>=[Select the Start Range Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Hmm, sorry, I thought you needed three queries.

Either way, the datediff will not count the days between the parameters. It counts the days between the two date fields. The parameters only serve to limit the rows returned by the query.

But I digress. I think what you want, to cover all the eventualities discussed in this thread, is something like this - note the use of brackets in the WHERE clause:

SELECT DateDiff("d",[Start Date],[End Date]) AS DateDifference, tbl_ProjectInformation.[Start Date], tbl_ProjectInformation.[End Date],  [HPB Internal]/[DateDifference] AS [HPBi/d] 
INTO tbl_Organ_ProjectInformation_Days1
FROM tbl_ProjectInformation
WHERE ([Start Date]<[Select the Start Range Date] And [End Date]<[Select the End Range Date]) OR [Start Date]>=[Select the Start Range Date] And [End Date]<[Select the End Range Date]) OR ([Start Date]>=[Select the Start Range Date] And [End Date]>=[Select the End Range Date]);

Or maybe not?!?! Hope this works for you.

http://www22.brinkster.com/accessory/

 

[mp9]

Oops. A mistake in my last post, missed a bracket. Try again!

SELECT DateDiff("d",[Start Date],[End Date]) AS DateDifference, tbl_ProjectInformation.[Start Date], tbl_ProjectInformation.[End Date],  [HPB Internal]/[DateDifference] AS [HPBi/d] 
INTO tbl_Organ_ProjectInformation_Days1
FROM tbl_ProjectInformation
WHERE ([Start Date]<[Select the Start Range Date] And [End Date]<[Select the End Range Date]) OR ([Start Date]>=[Select the Start Range Date] And [End Date]<[Select the End Range Date]) OR ([Start Date]>=[Select the Start Range Date] And [End Date]>=[Select the End Range Date]);

http://www22.brinkster.com/accessory/