Query to seperate field

[PeteN]

I have a table of the following format:
Weather Date
Wet 22/04/2006
Wet 25/04/2006
Wet 05/05/2007
Dry 01/07/2007
Wet 04/07/2007

What I want to do is end up with the following:
Wet Dry Month Year
2 0 April 2006
1 0 May 2007
1 1 July 2007

How do I achieve this?

TIA
Pete

 

[John W. Vinson]

I have a table of the following format:
Weather Date
Wet 22/04/2006
Wet 25/04/2006
Wet 05/05/2007
Dry 01/07/2007
Wet 04/07/2007

What I want to do is end up with the following:
Wet Dry Month Year
2 0 April 2006
1 0 May 2007
1 1 July 2007

How do I achieve this?

TIA
Pete

Use the builtin date functions in a Crosstab query. Create a query based on
the table; include the Weather field and in two vacant field cells type

TheMonth: Format([Date], "mmmm")
TheYear: Year([Date])

Then use the query type icon to change the query to a Crosstab query, using
Weather as the column header, and TheMonth and TheYear as row headers.

Note that Date, Month, and Year are all reserved words and should not be used
as fieldnames (though they can be used as captions, which will be displayed
instead of the fieldname).

John W. Vinson [MVP]

 

[louisjohnphillips]

I have a table of the following format:
Weather Date
Wet 22/04/2006
Wet 25/04/2006
Wet 05/05/2007
Dry 01/07/2007
Wet 04/07/2007

What I want to do is end up with the following:
Wet Dry Month Year
2 0 April 2006
1 0 May 2007
1 1 July 2007

How do I achieve this?

TIA
Pete

The solution comes in two parts:

--identify the weather in the months and years using

SELECT tblWetDry.Weather, format( tblWetDry.DateObserved, 'mmm' ) as
ObserveMonth, format( tblWetDry.DateObserved, 'yyyy' ) as ObserveYear
FROM tblWetDry

This yields at table such as:

Weather ObserveMonth ObserveYear
wet Apr 2006
wet Apr 2006
wet May 2007
dry Jul 2007
wet Apr 2007

The query was saved as Query2.

--sum the days in each month and year combination

SELECT Sum( Instr( Weather, 'dry' ) ) as DryDays,
sum( Instr( Weather, 'wet' )) as WetDays,
ObserveMonth, ObserveYear

From Query2

group by ObserveMonth, ObserveYear

This yields the result set such as:

DryDays WetDays ObserveMonth ObserveYear
0 2 Apr 2006
0 1 Apr 2007
1 0 Jul 2007
0 1 May 2007

This is the basic idea. You can work out the column ordering and
sorting to Observation Date order.

 

[tina]

first, recommend you change the "Date" fieldname. Date is a reserved word in
Access, and reserved words shouldn't be used by themselves for anything that
*you* name in your database.

i put your posted data into the following table, as

tblWeather
wID (primary key, Autonumber)
aDate (notice the "a" added to the name, so it's no longer a reserved word)
Weather

and created a cross-tab query, as

TRANSFORM Count(tblWeather.wID) AS CountOfwID
SELECT Year([aDate]) AS [Year], Format([aDate],"mmmm") AS [Month]
FROM tblWeather
GROUP BY Year([aDate]), Month([aDate]), Format([aDate],"mmmm")
ORDER BY Year([aDate]), Month([aDate])
PIVOT tblWeather.Weather;

you can substitute your "real" table and field names in the above SQL
statement, then paste it into a query's SQL pane and see if it gives you
what you need.

hth

 

[PeteN via AccessMonster.com]

John,
Thanks for your reply.
I am a very amateur Access programmer, so bear with me please.
When I try to run the Crosstab Query, it asks for a Value option, what do I
set this to?
My Query has the fields Weather, aDate (renamed Date) plus the two that you
said to add.

Pete

I have a table of the following format:
Weather Date

[quoted text clipped - 14 lines]

TIA
Pete

Use the builtin date functions in a Crosstab query. Create a query based on
the table; include the Weather field and in two vacant field cells type

TheMonth: Format([Date], "mmmm")
TheYear: Year([Date])

Then use the query type icon to change the query to a Crosstab query, using
Weather as the column header, and TheMonth and TheYear as row headers.

Note that Date, Month, and Year are all reserved words and should not be used
as fieldnames (though they can be used as captions, which will be displayed
instead of the fieldname).

John W. Vinson [MVP]

 

[John Spencer]

Try Count on the weather field

TRANSFORM Count(Weather) as Times
SELECT Format([Date], "mmmm") as TheMonth, Year([Date]) TheYear
FROM TheTable
GROUP BY Format([Date], "mmmm"), Year([Date])
PIVOT Weather

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,
Thanks for your reply.
I am a very amateur Access programmer, so bear with me please.
When I try to run the Crosstab Query, it asks for a Value option, what do
I
set this to?
My Query has the fields Weather, aDate (renamed Date) plus the two that
you
said to add.

Pete

I have a table of the following format:
Weather Date

[quoted text clipped - 14 lines]

TIA
Pete

Use the builtin date functions in a Crosstab query. Create a query based
on
the table; include the Weather field and in two vacant field cells type

TheMonth: Format([Date], "mmmm")
TheYear: Year([Date])

Then use the query type icon to change the query to a Crosstab query,
using
Weather as the column header, and TheMonth and TheYear as row headers.

Note that Date, Month, and Year are all reserved words and should not be
used
as fieldnames (though they can be used as captions, which will be
displayed
instead of the fieldname).

John W. Vinson [MVP]

 

[PeteN via AccessMonster.com]

John,
Thanks for your reply.
Got it to work after adding 'as' before 'TheYear, but the months are coming
out in the wrong order.
Apr/Aug/Dec/Feb/Jan/Jul/Jun/Mar/May/Nov/Oct/Sep, tried sorting these
ascending but it has no effect.
Can I call on your help again to sort this 'last' problem?
Thanks Again
Pete

Try Count on the weather field

TRANSFORM Count(Weather) as Times
SELECT Format([Date], "mmmm") as TheMonth, Year([Date]) TheYear
FROM TheTable
GROUP BY Format([Date], "mmmm"), Year([Date])
PIVOT Weather

John,
Thanks for your reply.

[quoted text clipped - 32 lines]

 

[John Spencer]

In order to sort the months in order by month number, you must include the
month number in the query. You can do that this way

TRANSFORM Count(Weather) as Times
SELECT Format([Date], "mmmm") as TheMonth, Year([Date]) as TheYear
FROM TheTable
GROUP BY Month([Date]) , Format([Date], "mmmm"), Year([Date])
ORDER BY Month([Date])
PIVOT Weather

Or this way
TRANSFORM Count(Weather) as Times
SELECT Month([Date]) as TheMonthNumber , Year([Date]) as TheYear
FROM TheTable
GROUP BY Month([Date]) , Year([Date])
ORDER BY Month([Date])
PIVOT Weather

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,
Thanks for your reply.
Got it to work after adding 'as' before 'TheYear, but the months are
coming
out in the wrong order.
Apr/Aug/Dec/Feb/Jan/Jul/Jun/Mar/May/Nov/Oct/Sep, tried sorting these
ascending but it has no effect.
Can I call on your help again to sort this 'last' problem?
Thanks Again
Pete

Try Count on the weather field

TRANSFORM Count(Weather) as Times
SELECT Format([Date], "mmmm") as TheMonth, Year([Date]) TheYear
FROM TheTable
GROUP BY Format([Date], "mmmm"), Year([Date])
PIVOT Weather

John,
Thanks for your reply.

[quoted text clipped - 32 lines]

John W. Vinson [MVP]

 

[PeteN via AccessMonster.com]

Thanks John, its working OK now.

Pete

In order to sort the months in order by month number, you must include the
month number in the query. You can do that this way

TRANSFORM Count(Weather) as Times
SELECT Format([Date], "mmmm") as TheMonth, Year([Date]) as TheYear
FROM TheTable
GROUP BY Month([Date]) , Format([Date], "mmmm"), Year([Date])
ORDER BY Month([Date])
PIVOT Weather

Or this way
TRANSFORM Count(Weather) as Times
SELECT Month([Date]) as TheMonthNumber , Year([Date]) as TheYear
FROM TheTable
GROUP BY Month([Date]) , Year([Date])
ORDER BY Month([Date])
PIVOT Weather

John,
Thanks for your reply.

[quoted text clipped - 20 lines]