HEEEELLLLPPPPP!!!!!

[Andre Adams]

Wow. I've been posting this same question in the "New Users" for 2 weeks now
and every answer is so complicated that I just get confused and post again.
I really need help, this is due within the week and my ass is on the line
now. I need a report that will show the following.

"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?"

Please help!

 

[Golfinray]

There are probably two ways you could go about this. Most of the responders
have probably been giving you the Datediff way of doing it. like:
Datediff("d",[the first field],[the second field])
If you have difficulty doing that you could just put in the query criteria:
Between 1/1/2008 and 2/1/2008 if you wanted January result. For a quarter,
you could use Between 1/1/2008 and 3/31/2008 and that would return the whole
quarter.

 

[Andre Adams]

Hey. Thanks for the reply. The only problem is, I need 3 different columns
where I would need to pull the date. So, for column 1, I have no problem
pulling the date ranged information. But because it only has certain
accounts that I'm pulling it for, when I go to pull -say- the quarter in the
next column, it doesn't work. So this is how I want the information to look.

Account Name MTD Comm QTD Shares YTD Comm

Northern Trust $130.00 15,000
$20,138.00

Please Advise.

There are probably two ways you could go about this. Most of the responders
have probably been giving you the Datediff way of doing it. like:
Datediff("d",[the first field],[the second field])
If you have difficulty doing that you could just put in the query criteria:
Between 1/1/2008 and 2/1/2008 if you wanted January result. For a quarter,
you could use Between 1/1/2008 and 3/31/2008 and that would return the whole
quarter.

Wow. I've been posting this same question in the "New Users" for 2 weeks now
and every answer is so complicated that I just get confused and post again.
I really need help, this is due within the week and my ass is on the line
now. I need a report that will show the following.

"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?"

Please help!

 

[Golfinray]

Well, one option would be to create three queries, one to do months, one for
quarters, one for years and then bring the three into one query. Other than
that, you would probably have to use the Datediff command, which is not THAT
difficult. On three new fields of your query, type (for days)
Daysatediff("d",[mystartdatefield],[myenddatefield])
On the next field:
Quartersatediff("q",[mystartdatefield],[myenddatefield])
Onthe next field:
Yearsatediff("YY",[mystartdatefield],[myenddatefield])

Hey. Thanks for the reply. The only problem is, I need 3 different columns
where I would need to pull the date. So, for column 1, I have no problem
pulling the date ranged information. But because it only has certain
accounts that I'm pulling it for, when I go to pull -say- the quarter in the
next column, it doesn't work. So this is how I want the information to look.

Account Name MTD Comm QTD Shares YTD Comm

Northern Trust $130.00 15,000
$20,138.00

Please Advise.

There are probably two ways you could go about this. Most of the responders
have probably been giving you the Datediff way of doing it. like:
Datediff("d",[the first field],[the second field])
If you have difficulty doing that you could just put in the query criteria:
Between 1/1/2008 and 2/1/2008 if you wanted January result. For a quarter,
you could use Between 1/1/2008 and 3/31/2008 and that would return the whole
quarter.

Wow. I've been posting this same question in the "New Users" for 2 weeks now
and every answer is so complicated that I just get confused and post again.
I really need help, this is due within the week and my ass is on the line
now. I need a report that will show the following.

"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?"

Please help!

 

[Duane Hookom]

What are you aggregating by manager? Are you summing total sales or counting
the number of times they say "right-sizing" or what? Do you have table and
field names?

I would start by creating a date range table with the following structure:
tblDateRanges
==================
datFrom date/time
datTo date/time
RangeName text
You could then enter three records with the from and to dates as well as
give the range a name.

Then you would create a crosstab query with your table and the above table.
You would not create any joins between the tables. You would set a criteria
under your date field of:
Criteria: Between datFrom and datTo
Set the Manager as the Row Heading, RangeName as the Column Heading, and
aggregate whatever you want in the Value.

You have complete freedom over your date ranges and what you want to call
them.