Need help with complex Query

[drnld]

Hi,



I have a table that has the following:

ID
Program Year
Program
Start Date
Midpoint Date
Internship Date
End Date

I have successfully created queries to create reports showing all Start Dates by Month no matter what program and similar reports for all the other date queries.

What I need it to do is list everything happening within a month and sort them by category. How do I do that? I've attached the database so you can see my queries as well as the main switchboard reports associated.

Thanks in advance for your help.

Dottie

[rpeare]

just put your criteria in each of the date fields on the OR lines

so on the first criteria line you would have

between [Enter the Start Date] and [Enter the End Date]
go down one line to the next criteria and over to your midpoint date field put in the same criteria, and so on.

that should give you a list of any item that had an 'activity' date within the selected range.

[drnld]

[QUOTE=rpeare;256305]just put your criteria in each of the date fields on the OR lines

so on the first criteria line you would have

between [Enter the Start Date] and [Enter the End Date]
go down one line to the next criteria and over to your midpoint date field put in the same criteria, and so on.

that should give you a list of any item that had an 'activity' date within the selected range.[/QUOTE

Hi RPEARE:

I did as you said and although the query gives me everything in the date range, I can't get the report to ONLY show me the dates by month.

What I want it to show me is:

January

Program Cycle Start dates all in January
Program Cycle Midpoints all in January
Program Cycle Internships all in January
Program Cycle End Dates all in January

February

Program Cycle Start dates all in February
Program Cycle Midpoints all in February
Program Cycle Internships all in February
Program Cycle End Dates all in February

I tried sorting by those dates, but it still was not only giving me the corresponding month dates. I might not be able to make it do this, but I thought I'd try.

[rpeare]

The query I explained will pull any record that has a date within a date range whether it be start, mid, intern or end date.


This is my test table

ID	TESTFIELD	SDate	MDate	IDate	EDate
1	AAA	1/1/2014	2/1/2014	3/1/2014	4/1/2014
2	BBB	2/1/2014	3/1/2014	4/1/2014	5/1/2014
3	CCC	3/1/2014	4/1/2014	5/1/2014	6/1/2014
4	DDD	4/1/2014	5/1/2014	6/1/2014	7/1/2014
5	EEE	5/1/2014	6/1/2014	7/1/2014	8/1/2014

This is my query (looking for any record that has a date in april of 2014)

SELECT *
FROM tblTEST
WHERE (((tblTEST.[SDATE]) Between #4/1/2014# And #4/30/2014#)) OR (((tblTEST.[MDATE]) Between #4/1/2014# And #4/30/2014#)) OR (((tblTEST.[IDATE]) Between #4/1/2014# And #4/30/2014#)) OR (((tblTEST.[EDATE]) Between #4/1/2014# And #4/30/2014#));

This is my result

ID	TESTFIELD	SDate	MDate	IDate	EDate
1	AAA	1/1/2014	2/1/2014	3/1/2014	4/1/2014
2	BBB	2/1/2014	3/1/2014	4/1/2014	5/1/2014
3	CCC	3/1/2014	4/1/2014	5/1/2014	6/1/2014
4	DDD	4/1/2014	5/1/2014	6/1/2014	7/1/2014

Notice record 5 is not showing because none of the dates on that record occur in april of 2014

[rpeare]

hit post too soon.

If you are trying to run this query for a year long range and show the same record in each month that it occurred you would likely need to use a union query to artificially normalize your data

So for instance if you wanted for record AAA to show in january, feburary, march and april of 2014 with an indicator you would have to use a union query like:

SELECT tblTest.ID, tblTest.TESTFIELD, tblTest.SDate, "Start Dates" AS Label
FROM tblTest
WHERE (((tblTest.SDate) Between [Enter the Start Date] And [Enter the End Date]))
UNION ALL
SELECT tblTest.ID, tblTest.TESTFIELD, tblTest.MDate, "Midpoint Dates" AS Label
FROM tblTest
WHERE (((tblTest.MDate) Between [Enter the Start Date] And [Enter the End Date]))
UNION ALL
SELECT tblTest.ID, tblTest.TESTFIELD, tblTest.IDate, "Internship Dates" AS Label
FROM tblTest
WHERE (((tblTest.IDate) Between [Enter the Start Date] And [Enter the End Date]))
UNION ALL
SELECT tblTest.ID, tblTest.TESTFIELD, tblTest.EDate, "Midpoint Dates" AS Label
FROM tblTest
WHERE (((tblTest.EDate) Between [Enter the Start Date] And [Enter the End Date]))