Cross-tab queries

[Andre Adams]

Is there a way to run a cross-tab query between a specific date range?

 

[Al Campagna]

Andre,
Sure... a Crosstab query can have criteria... just like any other query.
You can have whatever criteria you need to deliver the proper recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open form, or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Andre Adams]

I'm trying to get a Year vs. Year total. I need to tell the database to pull
a specific time in one year and then pull another specific time in the next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and then for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go about
that?

Andre,
Sure... a Crosstab query can have criteria... just like any other query.
You can have whatever criteria you need to deliver the proper recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open form, or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Is there a way to run a cross-tab query between a specific date range?

 

[Al Campagna]

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I'm trying to get a Year vs. Year total. I need to tell the database to
pull
a specific time in one year and then pull another specific time in the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go about
that?

Andre,
Sure... a Crosstab query can have criteria... just like any other
query.
You can have whatever criteria you need to deliver the proper recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Is there a way to run a cross-tab query between a specific date range?

 

[Andre Adams]

I'm with you Al. That works. However, the problem in not being able to get
the date range, it's having 2 different columns show 2 different date ranges
of commission. What access is saying, pretty much, is I'm going to look
within those date ranges and show you all the commission that I see and put
them in the commission column. What I need is for access to do is attach
commission from this date range and put it in one column and then find
commission for another date range and put it in the second column. Is there
a way to do that?

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I'm trying to get a Year vs. Year total. I need to tell the database to
pull
a specific time in one year and then pull another specific time in the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go about
that?

Andre,
Sure... a Crosstab query can have criteria... just like any other
query.
You can have whatever criteria you need to deliver the proper recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Is there a way to run a cross-tab query between a specific date range?

 

[Al Campagna]

Andre,
I wouldn't think so... within the same query. I think the best approach
is to do one query for this year's date range, and another "pass-thru" query
based on last year's date range. I'm not familiar with your data, but you
should be able to align them via some other common (realted) field.
Query2008
Month Year Type Amt
1 2008 CarSales 65,000

Query 2007
Month Year Type Amt
1 2007 CarSales 84,000

Now these 2 queries should combine together, perhaps by Year/MonthNo, or by
Type, etc...
That's what I'd try first... sorry, just don't have time to test.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I'm with you Al. That works. However, the problem in not being able to
get
the date range, it's having 2 different columns show 2 different date
ranges
of commission. What access is saying, pretty much, is I'm going to look
within those date ranges and show you all the commission that I see and
put
them in the commission column. What I need is for access to do is attach
commission from this date range and put it in one column and then find
commission for another date range and put it in the second column. Is
there
a way to do that?

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I'm trying to get a Year vs. Year total. I need to tell the database
to
pull
a specific time in one year and then pull another specific time in the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and
then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go
about
that?

:

Andre,
Sure... a Crosstab query can have criteria... just like any other
query.
You can have whatever criteria you need to deliver the proper
recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open
form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Is there a way to run a cross-tab query between a specific date
range?

 

[Andre Adams]

Thanks for all your help Al! I actually was messing around in the system and
figured it out. What I did was create a query for commission totals, by date
range for 2006 and the another query for 2007 totals by date range. Then I
created a totally separate query and combined the two and it gave me two
separate columns and a chance to enter date ranges for both!

Andre,
I wouldn't think so... within the same query. I think the best approach
is to do one query for this year's date range, and another "pass-thru" query
based on last year's date range. I'm not familiar with your data, but you
should be able to align them via some other common (realted) field.
Query2008
Month Year Type Amt
1 2008 CarSales 65,000

Query 2007
Month Year Type Amt
1 2007 CarSales 84,000

Now these 2 queries should combine together, perhaps by Year/MonthNo, or by
Type, etc...
That's what I'd try first... sorry, just don't have time to test.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I'm with you Al. That works. However, the problem in not being able to
get
the date range, it's having 2 different columns show 2 different date
ranges
of commission. What access is saying, pretty much, is I'm going to look
within those date ranges and show you all the commission that I see and
put
them in the commission column. What I need is for access to do is attach
commission from this date range and put it in one column and then find
commission for another date range and put it in the second column. Is
there
a way to do that?

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I'm trying to get a Year vs. Year total. I need to tell the database
to
pull
a specific time in one year and then pull another specific time in the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and
then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go
about
that?

:

Andre,
Sure... a Crosstab query can have criteria... just like any other
query.
You can have whatever criteria you need to deliver the proper
recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is your
decision. (ex. a parameter in the query, or a value from an open
form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Is there a way to run a cross-tab query between a specific date
range?

 

[Al Campagna]

Andre,
That's what I suggested...
Glad you worked it out...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for all your help Al! I actually was messing around in the system
and
figured it out. What I did was create a query for commission totals, by
date
range for 2006 and the another query for 2007 totals by date range. Then
I
created a totally separate query and combined the two and it gave me two
separate columns and a chance to enter date ranges for both!

Andre,
I wouldn't think so... within the same query. I think the best
approach
is to do one query for this year's date range, and another "pass-thru"
query
based on last year's date range. I'm not familiar with your data, but
you
should be able to align them via some other common (realted) field.
Query2008
Month Year Type Amt
1 2008 CarSales 65,000

Query 2007
Month Year Type Amt
1 2007 CarSales 84,000

Now these 2 queries should combine together, perhaps by Year/MonthNo, or
by
Type, etc...
That's what I'd try first... sorry, just don't have time to test.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I'm with you Al. That works. However, the problem in not being able
to
get
the date range, it's having 2 different columns show 2 different date
ranges
of commission. What access is saying, pretty much, is I'm going to
look
within those date ranges and show you all the commission that I see and
put
them in the commission column. What I need is for access to do is
attach
commission from this date range and put it in one column and then find
commission for another date range and put it in the second column. Is
there
a way to do that?

:

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I'm trying to get a Year vs. Year total. I need to tell the
database
to
pull
a specific time in one year and then pull another specific time in
the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and
then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go
about
that?

:

Andre,
Sure... a Crosstab query can have criteria... just like any
other
query.
You can have whatever criteria you need to deliver the proper
recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is
your
decision. (ex. a parameter in the query, or a value from an open
form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
Is there a way to run a cross-tab query between a specific date
range?

 

[Andre Adams]

You are absolutely right Al! I think some of what you said stuck and I ran
with the idea. My apologies, but, when I saw the format that you set the
data in, I didn't think it was anything like what I was looking for. It
actually turned out very good! Thanks for all your help man and I appreciate
it!

Andre

Andre,
That's what I suggested...
Glad you worked it out...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for all your help Al! I actually was messing around in the system
and
figured it out. What I did was create a query for commission totals, by
date
range for 2006 and the another query for 2007 totals by date range. Then
I
created a totally separate query and combined the two and it gave me two
separate columns and a chance to enter date ranges for both!

Andre,
I wouldn't think so... within the same query. I think the best
approach
is to do one query for this year's date range, and another "pass-thru"
query
based on last year's date range. I'm not familiar with your data, but
you
should be able to align them via some other common (realted) field.
Query2008
Month Year Type Amt
1 2008 CarSales 65,000

Query 2007
Month Year Type Amt
1 2007 CarSales 84,000

Now these 2 queries should combine together, perhaps by Year/MonthNo, or
by
Type, etc...
That's what I'd try first... sorry, just don't have time to test.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I'm with you Al. That works. However, the problem in not being able
to
get
the date range, it's having 2 different columns show 2 different date
ranges
of commission. What access is saying, pretty much, is I'm going to
look
within those date ranges and show you all the commission that I see and
put
them in the commission column. What I need is for access to do is
attach
commission from this date range and put it in one column and then find
commission for another date range and put it in the second column. Is
there
a way to do that?

:

Andre,
Use 2 date ranges with an OR.

Between #1/1/2006# And #2/1/2006# Or Between #1/1/2007# And #2/1/2007#
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I'm trying to get a Year vs. Year total. I need to tell the
database
to
pull
a specific time in one year and then pull another specific time in
the
next
year. So, date range for 2006 would be 01/01/2006 - 01/31/2006 and
then
for
2007 it would be 01/01/2007 - 01/31/2007. What's the best way to go
about
that?

:

Andre,
Sure... a Crosstab query can have criteria... just like any
other
query.
You can have whatever criteria you need to deliver the proper
recordset.
= Between [YourStartDate] AND [YourEndDate]

How you deliver YourStartDate and YourEndDate to the query is
your
decision. (ex. a parameter in the query, or a value from an open
form,
or
from Input boxes)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
Is there a way to run a cross-tab query between a specific date
range?