Can Access do this?

  • Thread starter Thread starter K. Georgiadis
  • Start date Start date
K

K. Georgiadis

Originally I posted this problem in the Excel newsgroup
but did not yet get any good leads. I'm skeptical that
Access (XP) can do this (except perhaps by iterative
querying) but I see no harm in asking the question:

I have an Excel spreadsheet with the names of States (21
in all) as column headings. The table is organized as
follows:
Column Headings( starting with Column B):
States (21 total) AL AZ AR CA etc

The Years in Column A:
1965
1966
1967 etc
all the way to 2003

In the intersects are the hectares planted.

I'm looking to sort the data for the last three rows of
data (2001, 2002, 2003), determine an average for each of
the 21 states, and displaying the 5 states with the
highest 3-year average.
 
Yes:
a 3 column table with year/state/area

where last three years

group by state, average area

order by (calculated) area

select top 5

Crosstab if necessary.


One query. Hardest part is just getting the data into a
reasonable data format. I guess I would code (VBA) that
part of the operation, rather than trying to create 50
transformation queries (one for each state). I don't
think there is an simple 'untransform' query to normalise
the data.

(david)
 
Short answer: Yes, Access can do it. Qualification: But it looks to me to be
appropriate for Excel.

It's difficult for me to address "sort the data" with no more information.
But, seems to me, you can create a row underneath the 2003 row, set the cell
under the first state to Sum those three years, and copy it underneath the
other states. Do the same thing for the average... Then it ought to be
relatively easy to spot the highest numbers.

Clearly, you could do the same thing in code, but I am not sufficiently
familiar with the Excel object model to address that.

If you want to do it in Access, you first have to get the data into Access.
I'd make a record with state, year, and acreage planted fields. Then you can
do a totals query choosing the years of interest, then do a TOP 3 query to
pick the 3 highest.

Larry Linson
Microsoft Access MVP
 
I was able to resolve this within Excel along the lines
that you suggested. Excel 2002 (my version) allows
sorting of data left to right. In the past I had trouble
keep the column headings and the data together but, once
I took the time to read carefully the online
instructions, I managed to sort the 3-year averages in
decending order.
It would have been a lot more time consuming to do this
in Access because of the organization of my Excel data.

Thanks for pointing me in the right direction.
 
Thanks for the suggestions. I resolved within Excel 2002
(see my response to Larry Linson)
 
Back
Top