DIFFICULT Query!

[Andre Adams]

Hey guys,

I asked this question so many times that I'm tired of asking it now. I'm
only posting again because this report is due very soon and I still don't
have a clue. Is there a way to have access pull 3 different date ranges in 3
different columns by Manager?

1st by month?
2nd by Quarter?
3rd by year?

Now, instead of Hard coding this for the 3 categories, I just want to be
able to type in the date range. Is that possible?

 

[KARL DEWEY]

---- UNTESTED ------
Try it this way --
SELECT [Manager], Sum(IIf([DateField] Between [Enter report end date] And
DateAdd("m", -1, [Enter report end date])+1, [SomeField], 0) As
Month_Something, Sum(IIf([DateField] Between [Enter report end date] And
DateAdd("q", -1, [Enter report end date]), [SomeField], 0) As
Quarter_Something, Sum(IIf([DateField] Between [Enter report end date] And
DateAdd("yyyy", -1, [Enter report end date]), [SomeField], 0) As
Year_Something
FROM YourTable
GROUP BY [Manager];

The querterly and yearly may be off by one day.

 

[Steve]

Create an unbound report with three unbound subreports. Use different
recordsources for each subreport to display month data in the first
subreport, quarter data in the second subreport and year data in the third
report.

Steve

 

[Andre Adams]

If what I'm hearing is correct, you guys want me to separate out my years
into disticitive tables. So, I'd have a table for each year? Right now, all
of my data is in one table. What I'm actually asking is for me to be able to
pull the data directly from the one table instead of having to break it up.
The SQL statement below is what I'm using now to just pull the year for the
query that I need, but, I'm missing 2 columns - Month to date Commission and
Quarter to date SHares. Is there a way to tweek this SQL statement to show
that?


SELECT DISTINCTROW tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
Sum(qryChicagoTeachers2008YTDCommission.[SumOfCommission Amount]) AS
[SumOfSumOfCommission Amount1]
FROM qryChicagoTeachers2008YTDCommission INNER JOIN tblIllinoisPlanSponsor2
ON qryChicagoTeachers2008YTDCommission.[Account #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #];

 

[Steve]

Not at all! One table is all you need. I'm suggesting you create three
queries based on that one table. One to get your month data, one to get your
quarter data and one to get your year data. Then create three subreports
where the first one uses your month query for the recordsource, the second
one uses your quarter query for the recordsource and the third one uses your
year query for the recordsource. Finally I am suggesting you create a fourth
unbound (no recordsource) report (with no fields) and inbed your three
subreports in this unbound report.

By the way, I don't see anything in your SQL that returns your data by
manager!

Steve

If what I'm hearing is correct, you guys want me to separate out my years
into disticitive tables. So, I'd have a table for each year? Right now,
all
of my data is in one table. What I'm actually asking is for me to be able
to
pull the data directly from the one table instead of having to break it
up.
The SQL statement below is what I'm using now to just pull the year for
the
query that I need, but, I'm missing 2 columns - Month to date Commission
and
Quarter to date SHares. Is there a way to tweek this SQL statement to
show
that?


SELECT DISTINCTROW tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
Sum(qryChicagoTeachers2008YTDCommission.[SumOfCommission Amount]) AS
[SumOfSumOfCommission Amount1]
FROM qryChicagoTeachers2008YTDCommission INNER JOIN
tblIllinoisPlanSponsor2
ON qryChicagoTeachers2008YTDCommission.[Account #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #];

Create an unbound report with three unbound subreports. Use different
recordsources for each subreport to display month data in the first
subreport, quarter data in the second subreport and year data in the
third
report.

Steve

 

[Andre Adams]

Thanks for clarifying Steve. I do have 3 Queries set up, but I have to put
the date ranges for each of them. For Instance, the first query by month, I
actually have to put in the date range for the query to pull. And so forth
for the other two. First, how do I structure a query to only pull the
current month, quarter and year?

Secondly, I've never used a sub-report. I've used reports all the time,
but, never a sub-report. Can you direct me somewhere to learn this quickly?

Not at all! One table is all you need. I'm suggesting you create three
queries based on that one table. One to get your month data, one to get your
quarter data and one to get your year data. Then create three subreports
where the first one uses your month query for the recordsource, the second
one uses your quarter query for the recordsource and the third one uses your
year query for the recordsource. Finally I am suggesting you create a fourth
unbound (no recordsource) report (with no fields) and inbed your three
subreports in this unbound report.

By the way, I don't see anything in your SQL that returns your data by
manager!

Steve

If what I'm hearing is correct, you guys want me to separate out my years
into disticitive tables. So, I'd have a table for each year? Right now,
all
of my data is in one table. What I'm actually asking is for me to be able
to
pull the data directly from the one table instead of having to break it
up.
The SQL statement below is what I'm using now to just pull the year for
the
query that I need, but, I'm missing 2 columns - Month to date Commission
and
Quarter to date SHares. Is there a way to tweek this SQL statement to
show
that?


SELECT DISTINCTROW tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
Sum(qryChicagoTeachers2008YTDCommission.[SumOfCommission Amount]) AS
[SumOfSumOfCommission Amount1]
FROM qryChicagoTeachers2008YTDCommission INNER JOIN
tblIllinoisPlanSponsor2
ON qryChicagoTeachers2008YTDCommission.[Account #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #];

Create an unbound report with three unbound subreports. Use different
recordsources for each subreport to display month data in the first
subreport, quarter data in the second subreport and year data in the
third
report.

Steve



Hey guys,

I asked this question so many times that I'm tired of asking it now.
I'm
only posting again because this report is due very soon and I still
don't
have a clue. Is there a way to have access pull 3 different date
ranges
in 3
different columns by Manager?

1st by month?
2nd by Quarter?
3rd by year?

Now, instead of Hard coding this for the 3 categories, I just want to
be
able to type in the date range. Is that possible?

 

[Steve]

Re: Date Ranges
Create an unbound pop-up form named PFrmDateRange with two textboxes named
StartDate and EndDate. In the date field of each query put this expression
in the criteria:
Between Forms!PFrmDateRange!StartDate And Forms!PFrmDateRange!StartDate
Add a button on PFrmDateRange and put the following code in the Click event:
DoCmd.OpenReport "NameOfYourReport"
DoCmd.Close "PFrmDateRange"

Re:Current month
Assuming your date field name is MyDate, add two calculated fields to your
query:
YearOfMyDate:Year([MyDate])
MonthOfMyDate:Month([MyDate])
Put this expression in the criteria of YearOfMyDate:
Year(Date())
Then put this expression in the criteria of MonthOfMyDate:
Month(Date())

Re:Current Quarter
Assuming your date field name is MyDate, add two calculated fields to your
query:
YearOfMyDate:Year([MyDate])
QuarterOfMyDate:Switch(Month([MyDate])
Put this expression in the criteria of YearOfMyDate:
Year(Date())
Put this expression in the criteria of QuarterOfMyDate:
Switch(Month(Date())<=3,1,Month(Date())<=6,2,Month(Date())<=9,3,Month(Date())<=12,4)

Re:Current Year
Assuming your date field name is MyDate, add a calculated field to your
query:
YearOfMyDate:Year([MyDate])
Put this expression in the criteria of YearOfMyDate:
Year(Date())

Re:Learn about subreports
Look in Help for subreports.
They are created just like subforms are created in forms.

Steve

Thanks for clarifying Steve. I do have 3 Queries set up, but I have to
put
the date ranges for each of them. For Instance, the first query by month,
I
actually have to put in the date range for the query to pull. And so
forth
for the other two. First, how do I structure a query to only pull the
current month, quarter and year?

Secondly, I've never used a sub-report. I've used reports all the time,
but, never a sub-report. Can you direct me somewhere to learn this
quickly?

Not at all! One table is all you need. I'm suggesting you create three
queries based on that one table. One to get your month data, one to get
your
quarter data and one to get your year data. Then create three subreports
where the first one uses your month query for the recordsource, the
second
one uses your quarter query for the recordsource and the third one uses
your
year query for the recordsource. Finally I am suggesting you create a
fourth
unbound (no recordsource) report (with no fields) and inbed your three
subreports in this unbound report.

By the way, I don't see anything in your SQL that returns your data by
manager!

Steve

If what I'm hearing is correct, you guys want me to separate out my
years
into disticitive tables. So, I'd have a table for each year? Right
now,
all
of my data is in one table. What I'm actually asking is for me to be
able
to
pull the data directly from the one table instead of having to break it
up.
The SQL statement below is what I'm using now to just pull the year for
the
query that I need, but, I'm missing 2 columns - Month to date
Commission
and
Quarter to date SHares. Is there a way to tweek this SQL statement to
show
that?


SELECT DISTINCTROW tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
Sum(qryChicagoTeachers2008YTDCommission.[SumOfCommission Amount]) AS
[SumOfSumOfCommission Amount1]
FROM qryChicagoTeachers2008YTDCommission INNER JOIN
tblIllinoisPlanSponsor2
ON qryChicagoTeachers2008YTDCommission.[Account #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY tblIllinoisPlanSponsor2.[Account Name],
tblIllinoisPlanSponsor2.[Account #];

:

Create an unbound report with three unbound subreports. Use different
recordsources for each subreport to display month data in the first
subreport, quarter data in the second subreport and year data in the
third
report.

Steve



Hey guys,

I asked this question so many times that I'm tired of asking it now.
I'm
only posting again because this report is due very soon and I still
don't
have a clue. Is there a way to have access pull 3 different date
ranges
in 3
different columns by Manager?

1st by month?
2nd by Quarter?
3rd by year?

Now, instead of Hard coding this for the 3 categories, I just want
to
be
able to type in the date range. Is that possible?

 

[John W. Vinson]

Thanks for clarifying Steve. I do have 3 Queries set up, but I have to put
the date ranges for each of them. For Instance, the first query by month, I
actually have to put in the date range for the query to pull. And so forth
for the other two. First, how do I structure a query to only pull the
current month, quarter and year?

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND Date()

will return the current month to date;

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

the entire current month.

BETWEEN DateSerial(Year(Date()), 3*((Month(Date()) -1)\3), 1) AND Date()

for the quarter to date, and

BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()

will work for year to date.

 

[Andre Adams]

Thanks guys. I'm going to try this now although I'm not very good with SQL
queries.

 

[John W. Vinson]

Thanks guys. I'm going to try this now although I'm not very good with SQL
queries.

Well... ALL queries are SQL queries. The query grid is nothing but a tool to
make it easier to construct SQL. You might want to toggle some of your queries
from grid view into SQL view to see how they are structured.

My suggested criteria can be put into the Criteria cell in the grid, you don't
need to go into SQL view to enter them. It's just that it's possible to post
readable SQL text to newsgroup messages, but much harder to post a
representation of the query grid.