Simple Query

  • Thread starter Thread starter JOHNNY
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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
Click to expand...

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.


.
Click to expand...
 
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
Click to expand...

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!
 
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


JOHNNY said:
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
Click to expand...

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.


.
Click to expand...
Click to expand...
 
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
Click to expand...
the
month as the
column header.

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


.
Click to expand...
Click to expand...


.
Click to expand...
 
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


JOHNNY said:
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.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top