Access Date Range Query 1

[PeanutB7]

Hi Folks,
I am looking for a query to a calculate data within an Access Table. The Table contains project records with specific Start Dtaes and End Dates as columns. With a parmater query I am having the reviewer select a date range. I would like Access to view each project record and state how many days a project falls within that date range selected and populate an additional column with that answer. If the project starts and ends before or after the selected date range than naturally the additional column reads zero. If it starts befor but ends during than it calculates the days that fall within the selected range. If the project starts during but ends after the selected date range then it would calculate the overlap days within the selected date range and so on. Is there a query and / or SQL language that would acomplish my goal Please Please Please.

I am quite new to Access so PLEASE be specific on details if time permits.

Thank you in advance for the help!

JB

 

[dhookom]

Consider creating a table tblDates with a single field theDate. Add records for all dates you might need. You can create this quite easily in Excel and then paste into your table.

Then create a cartesian query like:
SELECT tblProjects.ProjectID, tblProjects.StartDate, tblProjects.EndDate, tblDates.TheDate
FROM tblDates, tblProjects
WHERE tblDates.TheDate Between [StartDate] And [EndDate]
ORDER BY tblProjects.ProjectID, tblDates.TheDate;

Save this query and you will have a resultset that has each project listed as many times as it has dates from beginning to end. You can then query this query to find "project dates" between two other dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PeanutB7]

Duane,

Thank you for the timely response. Regretfully I am having trouble getting it to work, however. I created tbldate and attempted the query but it keeps coming up Syntax Error highlighting Start Date and End Date which is why I put them in quotation marks and then it highlights the WHERE statement.

Can you please suggest something. When this functions will the reviewer be asked to Select a "start range date" and an "end range date". That is my true desire for flexibility sake. Below is my SQL for your review:

SELECT tbl_Organ_ProjectInformation_Days1.ID, tbl_Organ_ProjectInformation_Days1."Start Date", tbl_Organ_ProjectInformation_Days1."End Date", tbldates."TheDate"
FROM tbl_Organ_ProjectInformation_Days1,
WHERE tbldates.TheDate Between [Start Date] And [End Date]
ORDER BY tbl_Organ_ProjectInformation_Days1.ID, tbldates.TheDate;

Thank you so very kindly,

JB

 

[dhookom]

What are your actual table and field names? You shouldn't have any expression in your query that uses ".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PeanutB7]

Duane
tbl_qry_Organ_ProjectInformation_Days1 is my table

Start Date & End Date are my field names.

TheDate will be my insert field showing the results eventually

If I put the Start Date and End Date in [brackets] I still get a syntax error.

Please Help!

Thank you

JB

 

[lespaul]

your query doesn't join into the "new" date table anywhere?

SELECT tbl_Organ_ProjectInformation_Days1.ID, tbl_Organ_ProjectInformation_Days1.Start Date, tbl_Organ_ProjectInformation_Days1.End Date, tbldates.TheDate
FROM tbl_Organ_ProjectInformation_Days1, tblDates
WHERE tbldates.TheDate Between [Start Date] And [End Date]
ORDER BY tbl_Organ_ProjectInformation_Days1.ID, tbldates.TheDate;

Do you have a field named 'TheDate' in the new tblDates table?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Something like this ?
SELECT A.ID, A.[Start Date], A.[End Date], B.TheDate
FROM tbl_Organ_ProjectInformation_Days1 A, tblDates B
WHERE B.TheDate Between A.[Start Date] And A.[End Date]
ORDER BY A.ID, B.TheDate;

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

 

[PeanutB7]

Sorry folks but I am not getting the results i require. First I had to put brackets on the start date and end date for the querry to function. Second, this query resulted in a record with a date in the The Date field for each day within the overlap period. What I truly need is an integer representing how many days fall within the criteria:

WHERE [Start Date] < [Select the Start Range Date] AND [End Date] < [Select the End Range Date],
WHERE [Start Date] >= [Select the Start Range Date] AND [End Date] < [Select the End Range Date],
WHERE [Start Date] >= [Select the Start Range Date] AND [End Date] >= [Select the End Range Date];

The query does define the time period in a reptitive fashion but had some 51000 records as a result.

Please help me I am at the point of screaming!

 

[PHV]

I guess that Duane meant to build an aggregate query ?
SELECT A.ID, Count(*) AS CountOfDays
FROM tbl_Organ_ProjectInformation_Days1 A, tblDates B
WHERE B.TheDate Between A.[Start Date] And A.[End Date]
AND B.TheDate Between [Select the Start Range Date] And [Select the End Range Date]
GROUP BY A.ID

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

 

[dhookom]

PH,
Good assumption. I was leading PeanutB7 through steps and didn't want to get too far until each step was master.

I had hoped that if a resultset was returned with each date of the between start and end that PeanutB7 would be able to figure out how to count the number of record between some begin and end dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PeanutB7]

Thanks for all of the help everyone. All seems to be working great. The only problem I am having and in only one of my three primary queries is a dialog box pops up asking for parameter of Count(*) AS CountOfDays. I have verified that all three queries are identical but this one keeps and labeled all as expression. If you click through without entering anything the querry seems to work fine. It seems to be just a nusance but is a concern.

Also is there any way to prompt all three parameter querries with one set of dialog boxes rathet then repititiously being asked to select start and end dates three times each.

Thanks once again for of the help!

A grateful JB

 

[dhookom]

I recommend avoiding all parameter prompt queries. You should be using controls on forms for your criteria.

WHERE [Start Date] >= Forms!frmDates!txtStart AND [End Date] >= Forms!frmDates!txtEnd


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]