parameter query needed

[subs]

ozip dzip shipdate
191 202 1/1/2009
191 203 1/4/2009
222 214 3/1/2009
223 125 3/2/2009
191 202 1/27/2009

i have thousands or records like this. i want the data in this below
format . For any particular month and any ozip dzip combination, i
want to calcualte the count of shipments in any week. The user has to
be given option of choosing the month/months in a year like jan or feb
or both. what should be the query for this pls help

ozip dzip week no1 week no2 week no3 weekno4
191 202 2 1

 

[Duane Hookom]

This is a basic crosstab. You might want to use a DateDiff() expression to
calculate the Column Heading value based on the text box where the user has
entered the year/month. If you apply any type of criteria, you may need to
enter the data type of the parameter.

Is this query for a report?

 

[subs]

This is a basic crosstab. You might want to use a DateDiff() expression to
calculate the Column Heading value based on the text box where the user has
entered the year/month. If you apply any type of criteria, you may need to
enter the data type of the parameter.

Is this query for a report?

No this is just a query- i need the results in excel - can you pls
give me SQL query for this. Thanks

 

[John W. Vinson]

No this is just a query- i need the results in excel - can you pls
give me SQL query for this. Thanks

Make a Crosstab Query (using the Crosstab Wizard in the query design window);
use ozip and dzip for Row Headers and

"Week no" & DateDiff("ww", DateSerial(Year([shipdate]), Month([shipdate]), 1),
[shipdate]))

as the column header. Note that some months have five weeks, and that the
definition of "the first week of a month" is a bit mushy: if the month starts
on a Saturday is that one day the "first week"? See the Help for DateDiff's
optional fourth operand for your options.

 

[subs]

No this is just a query- i need the results in excel - can you pls
give me SQL query for this. Thanks

Make a Crosstab Query (using the Crosstab Wizard in the query design window);
use ozip and dzip for Row Headers and

"Week no" & DateDiff("ww", DateSerial(Year([shipdate]), Month([shipdate]), 1),
[shipdate]))

as the column header. Note that some months have five weeks, and that the
definition of "the first week of a month" is a bit mushy: if the month starts
on a Saturday is that one day the "first week"? See the Help for DateDiff's
optional fourth operand for your options.

but the problem is i have 5 columns in addition to ozip and dzip. i
have ozip, dzip, consigee, dcity and dstate. How can i add all these
in row heading

 

[John W. Vinson]

On Jan 21, 6:21 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
This is a basic crosstab. You might want to use a DateDiff() expression to
calculate the Column Heading value based on the text box where the user has
entered the year/month. If you apply any type of criteria, you may need to
enter the data type of the parameter.

Is this query for a report?

:
ozip dzip shipdate
191 202 1/1/2009
191 203 1/4/2009
222 214 3/1/2009
223 125 3/2/2009
191 202 1/27/2009

i have thousands or records like this. i want the data in this below
format . For any particular month and any ozip dzip combination, i
want to calcualte the count of shipments in any week. The user has to
be given option of choosing the month/months in a year like jan or feb
or both. what should be the query for this pls help

ozip dzip week no1 week no2 week no3 weekno4
191 202 2 1
.

No this is just a query- i need the results in excel - can you pls
give me SQL query for this. Thanks

Make a Crosstab Query (using the Crosstab Wizard in the query design window);
use ozip and dzip for Row Headers and

"Week no" & DateDiff("ww", DateSerial(Year([shipdate]), Month([shipdate]), 1),
[shipdate]))

as the column header. Note that some months have five weeks, and that the
definition of "the first week of a month" is a bit mushy: if the month starts
on a Saturday is that one day the "first week"? See the Help for DateDiff's
optional fourth operand for your options.

but the problem is i have 5 columns in addition to ozip and dzip. i
have ozip, dzip, consigee, dcity and dstate. How can i add all these
in row heading

Just specify Row Heading for all of them. I think you can have ten row
headings.

If not, you could concatenate dzip, dcity, and dstate into one text field and
use it.

Once you have the crosstab it can be exported to Excel.

 

[subs]

On Jan 21, 6:21 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
This is a basic crosstab. You might want to use a DateDiff() expression to
calculate the Column Heading value based on the text box where the user has
entered the year/month. If you apply any type of criteria, you may need to
enter the data type of the parameter.
Is this query for a report?
--
Duane Hookom
Microsoft Access MVP
:
ozip dzip shipdate
191 202 1/1/2009
191 203 1/4/2009
222 214 3/1/2009
223 125 3/2/2009
191 202 1/27/2009
i have thousands or records like this. i want the data in this below
format . For any particular month and any ozip dzip combination, i
want to calcualte the count of shipments in any week. The user has to
be given option of choosing the month/months in a year like jan or feb
or both. what should be the query for this pls help
ozip dzip week no1 week no2 week no3 weekno4
191 202 2 1
.
No this is just a query- i need the results in excel - can you pls
give me SQL query for this. Thanks
Make a Crosstab Query (using the Crosstab Wizard in the query design window);
use ozip and dzip for Row Headers and
"Week no" & DateDiff("ww", DateSerial(Year([shipdate]), Month([shipdate]), 1),
[shipdate]))
as the column header. Note that some months have five weeks, and that the
definition of "the first week of a month" is a bit mushy: if the monthstarts
on a Saturday is that one day the "first week"? See the Help for DateDiff's
optional fourth operand for your options.

but the problem is i have 5 columns in addition to ozip and dzip. i
have ozip, dzip, consigee, dcity and dstate. How can i add all these
in row heading

Just specify Row Heading for all of them. I think you can have ten row
headings.

If not, you could concatenate dzip, dcity, and dstate into one text fieldand
use it.

Once you have the crosstab it can be exported to Excel.

Thanks John. It is working ---- now instead of just calculating the
week no, if i need to calculate the month what should be the datediff
function? i want something like this

week no1 Jan , week no2 feb, week no1 feb etc
thanks

 

[John W. Vinson]

Thanks John. It is working ---- now instead of just calculating the
week no, if i need to calculate the month what should be the datediff
function? i want something like this

week no1 Jan , week no2 feb, week no1 feb etc

You wouldn't use datediff to calculate the month but just
Format([datefield], "mmm"). That value could be concatenated to the datediff
expression if you wish.