Simple Query

[JOHNNY]

I am extremely new to this, but I have one main table. I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

 

[John Vinson]

I am extremely new to this, but I have one main table. I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

This is called a "Crosstab" query. You can put a calculated field in
the query

RespMonth: Month([datefield])

Use the Crosstab Query Wizard to create a query using the month as the
column header.

Typically one would NOT save this result in a table - you can base a
Report or a (non-updateable) Form directly on the query.

 

[JOHNNY]

Thanks a lot, that does look better. I was wondering now
that I have the months listed correctly, is there a way to
show all the information instead of just a count, or any
of the functions that are shown in the Crosstab list?

For instance my main table is laid out something like this

Customer Name Scale 1 Date
XYZ 1 1/1/2004
ZYX 2 1/5/2004
ZZZ 1 1/7/2004
YYY 4 2/4/2004

and so on. I wanted to be able to create the query so
that it pulls all the information for January and shows
all the Scale 1 responses, and in the next column show all
February responses.

January February
1 4
2
1

Something like that. Thanks a million!!!!

-----Original Message-----

I am extremely new to this, but I have one main table. I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

This is called a "Crosstab" query. You can put a calculated field in
the query

RespMonth: Month([datefield])

Use the Crosstab Query Wizard to create a query using the month as the
column header.

Typically one would NOT save this result in a table - you can base a
Report or a (non-updateable) Form directly on the query.


.

 

[John Vinson]

I wanted to be able to create the query so
that it pulls all the information for January and shows
all the Scale 1 responses, and in the next column show all
February responses.

January February
1 4
2
1

I think you can do this using the Scale as the "row header" property
of the Crosstab - though I'm anything but an expert on crosstabs!

 

[Duane Hookom]

If all of your customer names are different, then how would you get a row
returned with January=1 and February = 4?

--
Duane Hookom
MS Access MVP

Thanks a lot, that does look better. I was wondering now
that I have the months listed correctly, is there a way to
show all the information instead of just a count, or any
of the functions that are shown in the Crosstab list?

For instance my main table is laid out something like this

Customer Name Scale 1 Date
XYZ 1 1/1/2004
ZYX 2 1/5/2004
ZZZ 1 1/7/2004
YYY 4 2/4/2004

and so on. I wanted to be able to create the query so
that it pulls all the information for January and shows
all the Scale 1 responses, and in the next column show all
February responses.

January February
1 4
2
1

Something like that. Thanks a million!!!!

-----Original Message-----

I am extremely new to this, but I have one main table. I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

This is called a "Crosstab" query. You can put a calculated field in
the query

RespMonth: Month([datefield])

Use the Crosstab Query Wizard to create a query using the month as the
column header.

Typically one would NOT save this result in a table - you can base a
Report or a (non-updateable) Form directly on the query.


.

 

[JOHNNY]

The customer name doesn't really matter. All I am looking
for is the scale responses grouped by month. It doesn't
matter which customer gave the response at all. Is it
possible to create a query that would group and display
all the scale responses in one column under January, and
then in the next column all the scale responses for
February and so on?

-----Original Message-----
If all of your customer names are different, then how would you get a row
returned with January=1 and February = 4?

--
Duane Hookom
MS Access MVP

Thanks a lot, that does look better. I was wondering now
that I have the months listed correctly, is there a way to
show all the information instead of just a count, or any
of the functions that are shown in the Crosstab list?

For instance my main table is laid out something like this

Customer Name Scale 1 Date
XYZ 1 1/1/2004
ZYX 2 1/5/2004
ZZZ 1 1/7/2004
YYY 4 2/4/2004

and so on. I wanted to be able to create the query so
that it pulls all the information for January and shows
all the Scale 1 responses, and in the next column show all
February responses.

January February
1 4
2
1

Something like that. Thanks a million!!!!

-----Original Message-----
On Mon, 26 Jan 2004 14:47:49 -0800, "JOHNNY"

I am extremely new to this, but I have one main table. I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

This is called a "Crosstab" query. You can put a calculated field in
the query

RespMonth: Month([datefield])

Use the Crosstab Query Wizard to create a query using

the
month as the

column header.

Typically one would NOT save this result in a table -

you
can base a

Report or a (non-updateable) Form directly on the query.


.

.

 

[Duane Hookom]

Create a crosstab query with a calculated column

Cust:"All Customers"
Group By
Row Heading

Format([DateField],"mmm")
Group By
Column Heading

Scale
Count
Value

--
Duane Hookom
Microsoft Access MVP

The customer name doesn't really matter. All I am looking
for is the scale responses grouped by month. It doesn't
matter which customer gave the response at all. Is it
possible to create a query that would group and display
all the scale responses in one column under January, and
then in the next column all the scale responses for
February and so on?

-----Original Message-----
If all of your customer names are different, then how would you get a row
returned with January=1 and February = 4?

--
Duane Hookom
MS Access MVP

Thanks a lot, that does look better. I was wondering now
that I have the months listed correctly, is there a way to
show all the information instead of just a count, or any
of the functions that are shown in the Crosstab list?

For instance my main table is laid out something like this

Customer Name Scale 1 Date
XYZ 1 1/1/2004
ZYX 2 1/5/2004
ZZZ 1 1/7/2004
YYY 4 2/4/2004

and so on. I wanted to be able to create the query so
that it pulls all the information for January and shows
all the Scale 1 responses, and in the next column show all
February responses.

January February
1 4
2
1

Something like that. Thanks a million!!!!

-----Original Message-----
On Mon, 26 Jan 2004 14:47:49 -0800, "JOHNNY"

I am extremely new to this, but I have one main table.
I
want to create a query that will pull one column of
information from a table based on a range of dates. for
instance, my main table has customer responses with
different dates throughout the year. I want to create a
query that will group all the january responses in one
group, all the feb. in another group and so on. I want
the query to place all of the information in different
columns on the table the query creates. If anyone can
help it would be greatly appreciated, thanks

This is called a "Crosstab" query. You can put a
calculated field in
the query

RespMonth: Month([datefield])

Use the Crosstab Query Wizard to create a query using the
month as the
column header.

Typically one would NOT save this result in a table - you
can base a
Report or a (non-updateable) Form directly on the query.


.

.