compiling data from several years.

[Greta]

I am trying to compile several years of data from separate spreadsheets (1
per year) into a single spreadsheet. Each source has lots of info in
addition to the 3 columns I'm interested in, which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into separate tables by year
with the 3 fields of interest and named DCODE as the primary key in each
because it is the only constant. I then created a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected from the 2008 data
table), 2007Count (from the 2007 data table, 2006Count (from the 2006 data
table), etc. It worked fine, EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names (we'd like the most
current name), districts that are new (less than 16 years of data), and
districts that have disappeared (also less than 16 years of data). We'd like
the table to show all data, which means I need to get a comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[PvdG42]

I am trying to compile several years of data from separate spreadsheets (1
per year) into a single spreadsheet. Each source has lots of info in
addition to the 3 columns I'm interested in, which are DCODE,
DistrictName,
and 200xCount. So far we have imported the data into separate tables by
year
with the 3 fields of interest and named DCODE as the primary key in each
because it is the only constant. I then created a Select query that
looked
like: DCODE, DistrictName, 2008Count (these 3 selected from the 2008 data
table), 2007Count (from the 2007 data table, 2006Count (from the 2006 data
table), etc. It worked fine, EXCEPT that it only reported data when all
16
tables had data for the DCODE (at least I surmise that's what happened
since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names (we'd like the
most
current name), districts that are new (less than 16 years of data), and
districts that have disappeared (also less than 16 years of data). We'd
like
the table to show all data, which means I need to get a comprehensive
table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so I'd like a
robust
solution that I can adapt. I'm using Access 2003.

Thanks!

I'd say the first step you should take is to invest in some education in
proper relational database design. You can do it yourself, using free online
articles and/or taking a course at a local college or technical center, or
you can hire somebody to design the database for you.

I make the comment above, because I'm pretty sure having a table for each
year is not the best way to manage your data. I cannot offer specifics, as
you have not provided enough detail on the various fields and datatypes you
need to store. You also need to consider the size of your organization, the
number of potential concurrent database users, and the amount of data to be
stored It's possible that Access won't be the best RDBMS choice for you.

 

[BruceM]

As suggested, you will do best to find out some more about how Access works,
as you are fighting it with your current approach. There is some good
information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near the bottom, too.

To the question at hand, when planning your database you need to think first
in terms of structure. Each entity is in its own table; each table contains
attributes of one kind of entity only. Without knowing more about the
business situation it is difficult to be specific. By way of example, Name
and Address may be thought of as attributes of a person, but work history is
not. A person may have worked in one place for their entire career, or they
could be at their 20th company. Adding a lot of fields to a table so you
can account for 1 to 20 work places (and hoping nobody has 21) is not
effective design. Rather, work history is in a table that is related to the
main People table. I will leave it at that for now. The tutorial and the
links will help you get a handle on this.

When working with data such as you have (a spreadsheet or worksheet for each
year) you probably need to use a Union query to combine the data into what
is typically called a normalized (or more normalized, at least) structure.
You mention a query, but not how it is put together. The best way to do
that is with the query's SQL (View >> SQL, copy what you see there, and post
it into a message. It may be best to limit the query to two or three years.
The idea is the same as with 20 queries, but it is more manageable to read.

If the idea is that you will be keeping this data on spreadsheets and using
it within Access, you will be approaching this differently than if you are
moving the project to Access, but in any case the principles are the same,
so the tutorial and other links are well worth reading.

 

[Greta]

I think perhaps I wasn't clear enough on the background of this issue. We're
trying to compile data from several extensive tables created outside our
organization. We don't need all of the information, just pieces of it. I'm
trying to use Access as a tool to keep our intern from manually taking each
Excel spread sheet, copying the data of interest and then matching it by hand
from year to year. It sounds like the UNION query might be what I need, but
I've played with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME], [AVG_COE2008.2008AVG_COE]
FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved words,
punctuation... So I may have multiple problems. The example I found online
didn't help me. There must be better ones somewhere...I can usually follow
an example! Maybe because I'm using Access 2003?

Thanks!

 

[Bob Quintal]

I think perhaps I wasn't clear enough on the background of this
issue. We're trying to compile data from several extensive tables
created outside our organization. We don't need all of the
information, just pieces of it. I'm trying to use Access as a
tool to keep our intern from manually taking each Excel spread
sheet, copying the data of interest and then matching it by hand
from year to year. It sounds like the UNION query might be what I
need, but I've played with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved
words, punctuation... So I may have multiple problems. The
example I found online didn't help me. There must be better ones
somewhere...I can usually follow an example! Maybe because I'm
using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of the
data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some analysis
using a CrossTab Query.

 

[Greta]

This solved the query errors problem. But I need some help in looking at the
problem anew, because while I got data, it wasn't what I need. Back to the
beginning...

I have 16 tables of data in the database, one per year that we are
interested in. These are excerpts from 16 much larger data tables created
outside our organization. Each of these tables has 3 column headings:
DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all DCODES
appear in all 16 tables. The DISTNAME is sometimes different from table to
table. The column xxxxFACT holds the data for year xxxx.

I want to combine the 16 tables into one table that will have these column
headings: DCODE, DISTNAME, 2008FACT, 2007FACT, 2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

I think perhaps I wasn't clear enough on the background of this
issue. We're trying to compile data from several extensive tables
created outside our organization. We don't need all of the
information, just pieces of it. I'm trying to use Access as a
tool to keep our intern from manually taking each Excel spread
sheet, copying the data of interest and then matching it by hand
from year to year. It sounds like the UNION query might be what I
need, but I've played with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved
words, punctuation... So I may have multiple problems. The
example I found online didn't help me. There must be better ones
somewhere...I can usually follow an example! Maybe because I'm
using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of the
data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some analysis
using a CrossTab Query.

 

[BruceM]

Actually, it could be quite difficult. Access is designed to work with
normalized data (structured according to relational database design
principles). Your data is anything but.

From your example it seems you combined 1992 data with 2008 data. If so,
does the Union query produce a listing of the 2008 data followed by the 1992
data in three columns? You should be able to construct a crosstab query
from the Union query, but as you will not be able to use the query design
grid I will hope somebody else can suggest the necessary SQL. I expect I
could figure it out, but I really can't put a lot of time into it now. One
option may be to create a table from the union query, then create a crosstab
query from the new table.

You may only need to alias the year-specific field ____AVG_COE, since the
others seem to be the same from one table to another.

Do you want the data in a table so you can work with it, manipulate it,
etc., or is the intent to view the data? If the latter, perhaps you could
base a report on the union query, grouped by year, then DNAME.

This solved the query errors problem. But I need some help in looking at
the
problem anew, because while I got data, it wasn't what I need. Back to
the
beginning...

I have 16 tables of data in the database, one per year that we are
interested in. These are excerpts from 16 much larger data tables created
outside our organization. Each of these tables has 3 column headings:
DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all DCODES
appear in all 16 tables. The DISTNAME is sometimes different from table
to
table. The column xxxxFACT holds the data for year xxxx.

I want to combine the 16 tables into one table that will have these column
headings: DCODE, DISTNAME, 2008FACT, 2007FACT, 2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

I think perhaps I wasn't clear enough on the background of this
issue. We're trying to compile data from several extensive tables
created outside our organization. We don't need all of the
information, just pieces of it. I'm trying to use Access as a
tool to keep our intern from manually taking each Excel spread
sheet, copying the data of interest and then matching it by hand
from year to year. It sounds like the UNION query might be what I
need, but I've played with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved
words, punctuation... So I may have multiple problems. The
example I found online didn't help me. There must be better ones
somewhere...I can usually follow an example! Maybe because I'm
using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of the
data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some analysis
using a CrossTab Query.

:

As suggested, you will do best to find out some more about how
Access works, as you are fighting it with your current approach.
There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near the
bottom, too.

To the question at hand, when planning your database you need to
think first in terms of structure. Each entity is in its own
table; each table contains attributes of one kind of entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and Address
may be thought of as attributes of a person, but work history is
not. A person may have worked in one place for their entire
career, or they could be at their 20th company. Adding a lot of
fields to a table so you can account for 1 to 20 work places (and
hoping nobody has 21) is not effective design. Rather, work
history is in a table that is related to the main People table.
I will leave it at that for now. The tutorial and the links will
help you get a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union query
to combine the data into what is typically called a normalized
(or more normalized, at least) structure. You mention a query,
but not how it is put together. The best way to do that is with
the query's SQL (View >> SQL, copy what you see there, and post
it into a message. It may be best to limit the query to two or
three years. The idea is the same as with 20 queries, but it is
more manageable to read.

If the idea is that you will be keeping this data on spreadsheets
and using it within Access, you will be approaching this
differently than if you are moving the project to Access, but in
any case the principles are the same, so the tutorial and other
links are well worth reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has lots of
info in addition to the 3 columns I'm interested in, which are
DCODE, DistrictName,
and 200xCount. So far we have imported the data into separate
tables by year
with the 3 fields of interest and named DCODE as the primary
key in each because it is the only constant. I then created a
Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected from
the 2008 data table), 2007Count (from the 2007 data table,
2006Count (from the 2006 data table), etc. It worked fine,
EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's what
happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names (we'd
like the most
current name), districts that are new (less than 16 years of
data), and districts that have disappeared (also less than 16
years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so I'd
like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[Bob Quintal]

This solved the query errors problem. But I need some help in
looking at the problem anew, because while I got data, it wasn't
what I need. Back to the beginning...

No! you need to move on from where you are, I said in my previous
reply:
read up a little on crosstab queries, and then start a new query.
Select the Union query, and bring down the following fields

DCODE, DISTNAME, TheYear and the 2008AVG_COE fields
Change the query type (it's on the toolbar) to CrossTab Query

This will add two rows to the spreadsheet, one labeled TOTAL, the
second Crosstab. All rows of Total: will be filled in with Group By.
Change the one in the 21008AVG_COE column to Sum, or Avg...
Now click in the DCODE column's crosstab row and select Row Heading
from the dropdown. select Row Heading for the DISTNAME column, Column
heading for the TheYear column and choose Value for the 2008AVG_COE
field.

Run the query, see that you are now pretty close to what you want.

Q

I have 16 tables of data in the database, one per year that we are
interested in. These are excerpts from 16 much larger data tables
created outside our organization. Each of these tables has 3
column headings: DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all
DCODES appear in all 16 tables. The DISTNAME is sometimes
different from table to table. The column xxxxFACT holds the data
for year xxxx.

I want to combine the 16 tables into one table that will have
these column headings: DCODE, DISTNAME, 2008FACT, 2007FACT,
2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

I think perhaps I wasn't clear enough on the background of this
issue. We're trying to compile data from several extensive
tables created outside our organization. We don't need all of
the information, just pieces of it. I'm trying to use Access
as a tool to keep our intern from manually taking each Excel
spread sheet, copying the data of interest and then matching it
by hand from year to year. It sounds like the UNION query
might be what I need, but I've played with the syntax and can't
get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved
words, punctuation... So I may have multiple problems. The
example I found online didn't help me. There must be better
ones somewhere...I can usually follow an example! Maybe
because I'm using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of the
data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.

:

As suggested, you will do best to find out some more about how
Access works, as you are fighting it with your current
approach. There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near the
bottom, too.

To the question at hand, when planning your database you need
to think first in terms of structure. Each entity is in its
own table; each table contains attributes of one kind of
entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and Address
may be thought of as attributes of a person, but work history
is not. A person may have worked in one place for their
entire career, or they could be at their 20th company. Adding
a lot of fields to a table so you can account for 1 to 20 work
places (and hoping nobody has 21) is not effective design.
Rather, work history is in a table that is related to the main
People table. I will leave it at that for now. The tutorial
and the links will help you get a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union
query to combine the data into what is typically called a
normalized (or more normalized, at least) structure. You
mention a query, but not how it is put together. The best way
to do that is with the query's SQL (View >> SQL, copy what you
see there, and post it into a message. It may be best to
limit the query to two or three years. The idea is the same as
with 20 queries, but it is more manageable to read.

If the idea is that you will be keeping this data on
spreadsheets and using it within Access, you will be
approaching this differently than if you are moving the
project to Access, but in any case the principles are the
same, so the tutorial and other links are well worth reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has lots
of info in addition to the 3 columns I'm interested in,
which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into
separate tables by year
with the 3 fields of interest and named DCODE as the primary
key in each because it is the only constant. I then created
a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected from
the 2008 data table), 2007Count (from the 2007 data table,
2006Count (from the 2006 data table), etc. It worked fine,
EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's
what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names
(we'd like the most
current name), districts that are new (less than 16 years of
data), and districts that have disappeared (also less than
16 years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so
I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[BruceM]

One problem is that you can't use the query design grid with a union query,
so making the union query into a crosstab query (I'm assuming it's possible)
would have to be via direct SQL coding. Perhaps if the union query is used
to create a table, that could be used as the basis for a crosstab query.

This solved the query errors problem. But I need some help in
looking at the problem anew, because while I got data, it wasn't
what I need. Back to the beginning...

No! you need to move on from where you are, I said in my previous
reply:
read up a little on crosstab queries, and then start a new query.
Select the Union query, and bring down the following fields

DCODE, DISTNAME, TheYear and the 2008AVG_COE fields
Change the query type (it's on the toolbar) to CrossTab Query

This will add two rows to the spreadsheet, one labeled TOTAL, the
second Crosstab. All rows of Total: will be filled in with Group By.
Change the one in the 21008AVG_COE column to Sum, or Avg...
Now click in the DCODE column's crosstab row and select Row Heading
from the dropdown. select Row Heading for the DISTNAME column, Column
heading for the TheYear column and choose Value for the 2008AVG_COE
field.

Run the query, see that you are now pretty close to what you want.

Q

I have 16 tables of data in the database, one per year that we are
interested in. These are excerpts from 16 much larger data tables
created outside our organization. Each of these tables has 3
column headings: DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all
DCODES appear in all 16 tables. The DISTNAME is sometimes
different from table to table. The column xxxxFACT holds the data
for year xxxx.

I want to combine the 16 tables into one table that will have
these column headings: DCODE, DISTNAME, 2008FACT, 2007FACT,
2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

I think perhaps I wasn't clear enough on the background of this
issue. We're trying to compile data from several extensive
tables created outside our organization. We don't need all of
the information, just pieces of it. I'm trying to use Access
as a tool to keep our intern from manually taking each Excel
spread sheet, copying the data of interest and then matching it
by hand from year to year. It sounds like the UNION query
might be what I need, but I've played with the syntax and can't
get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about reserved
words, punctuation... So I may have multiple problems. The
example I found online didn't help me. There must be better
ones somewhere...I can usually follow an example! Maybe
because I'm using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of the
data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.


:

As suggested, you will do best to find out some more about how
Access works, as you are fighting it with your current
approach. There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near the
bottom, too.

To the question at hand, when planning your database you need
to think first in terms of structure. Each entity is in its
own table; each table contains attributes of one kind of
entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and Address
may be thought of as attributes of a person, but work history
is not. A person may have worked in one place for their
entire career, or they could be at their 20th company. Adding
a lot of fields to a table so you can account for 1 to 20 work
places (and hoping nobody has 21) is not effective design.
Rather, work history is in a table that is related to the main
People table. I will leave it at that for now. The tutorial
and the links will help you get a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union
query to combine the data into what is typically called a
normalized (or more normalized, at least) structure. You
mention a query, but not how it is put together. The best way
to do that is with the query's SQL (View >> SQL, copy what you
see there, and post it into a message. It may be best to
limit the query to two or three years. The idea is the same as
with 20 queries, but it is more manageable to read.

If the idea is that you will be keeping this data on
spreadsheets and using it within Access, you will be
approaching this differently than if you are moving the
project to Access, but in any case the principles are the
same, so the tutorial and other links are well worth reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has lots
of info in addition to the 3 columns I'm interested in,
which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into
separate tables by year
with the 3 fields of interest and named DCODE as the primary
key in each because it is the only constant. I then created
a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected from
the 2008 data table), 2007Count (from the 2007 data table,
2006Count (from the 2006 data table), etc. It worked fine,
EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's
what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names
(we'd like the most
current name), districts that are new (less than 16 years of
data), and districts that have disappeared (also less than
16 years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so
I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[Bob Quintal]

One problem is that you can't use the query design grid with a
union query, so making the union query into a crosstab query (I'm
assuming it's possible) would have to be via direct SQL coding.
Perhaps if the union query is used to create a table, that could
be used as the basis for a crosstab query.

Once you have created the union query and saved it,
you create a crosstab query using the Union Query as the source table
in the design grid. It works perfectly, NO CODING Required

Bob Q.

This solved the query errors problem. But I need some help in
looking at the problem anew, because while I got data, it wasn't
what I need. Back to the beginning...

No! you need to move on from where you are, I said in my previous
reply:

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.

read up a little on crosstab queries, and then start a new query.
Select the Union query, and bring down the following fields

DCODE, DISTNAME, TheYear and the 2008AVG_COE fields
Change the query type (it's on the toolbar) to CrossTab Query

This will add two rows to the spreadsheet, one labeled TOTAL, the
second Crosstab. All rows of Total: will be filled in with Group
By. Change the one in the 21008AVG_COE column to Sum, or Avg...
Now click in the DCODE column's crosstab row and select Row
Heading from the dropdown. select Row Heading for the DISTNAME
column, Column heading for the TheYear column and choose Value
for the 2008AVG_COE field.

Run the query, see that you are now pretty close to what you
want.

Q

I have 16 tables of data in the database, one per year that we
are interested in. These are excerpts from 16 much larger data
tables created outside our organization. Each of these tables
has 3 column headings: DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all
DCODES appear in all 16 tables. The DISTNAME is sometimes
different from table to table. The column xxxxFACT holds the
data for year xxxx.

I want to combine the 16 tables into one table that will have
these column headings: DCODE, DISTNAME, 2008FACT, 2007FACT,
2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

:


I think perhaps I wasn't clear enough on the background of
this issue. We're trying to compile data from several
extensive tables created outside our organization. We don't
need all of the information, just pieces of it. I'm trying
to use Access as a tool to keep our intern from manually
taking each Excel spread sheet, copying the data of interest
and then matching it by hand from year to year. It sounds
like the UNION query might be what I need, but I've played
with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about
reserved words, punctuation... So I may have multiple
problems. The example I found online didn't help me. There
must be better ones somewhere...I can usually follow an
example! Maybe because I'm using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of
the data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.


:

As suggested, you will do best to find out some more about
how Access works, as you are fighting it with your current
approach. There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near
the bottom, too.

To the question at hand, when planning your database you
need to think first in terms of structure. Each entity is
in its own table; each table contains attributes of one kind
of entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and
Address may be thought of as attributes of a person, but
work history is not. A person may have worked in one place
for their entire career, or they could be at their 20th
company. Adding a lot of fields to a table so you can
account for 1 to 20 work places (and hoping nobody has 21)
is not effective design. Rather, work history is in a table
that is related to the main People table. I will leave it at
that for now. The tutorial and the links will help you get
a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union
query to combine the data into what is typically called a
normalized (or more normalized, at least) structure. You
mention a query, but not how it is put together. The best
way to do that is with the query's SQL (View >> SQL, copy
what you see there, and post it into a message. It may be
best to limit the query to two or three years. The idea is
the same as with 20 queries, but it is more manageable to
read.

If the idea is that you will be keeping this data on
spreadsheets and using it within Access, you will be
approaching this differently than if you are moving the
project to Access, but in any case the principles are the
same, so the tutorial and other links are well worth
reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has
lots of info in addition to the 3 columns I'm interested
in, which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into
separate tables by year
with the 3 fields of interest and named DCODE as the
primary key in each because it is the only constant. I
then created a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected
from the 2008 data table), 2007Count (from the 2007 data
table, 2006Count (from the 2006 data table), etc. It
worked fine, EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's
what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names
(we'd like the most
current name), districts that are new (less than 16 years
of data), and districts that have disappeared (also less
than 16 years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so
I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[BruceM]

Oops. I got off track there. I forgot that a union query can be the source
for another query by was of the design grid. I was thinking for some reason
it was necessary to convert the union query directly into a crosstab query.

One problem is that you can't use the query design grid with a
union query, so making the union query into a crosstab query (I'm
assuming it's possible) would have to be via direct SQL coding.
Perhaps if the union query is used to create a table, that could
be used as the basis for a crosstab query.

Once you have created the union query and saved it,
you create a crosstab query using the Union Query as the source table
in the design grid. It works perfectly, NO CODING Required

Bob Q.

This solved the query errors problem. But I need some help in
looking at the problem anew, because while I got data, it wasn't
what I need. Back to the beginning...

No! you need to move on from where you are, I said in my previous
reply:
Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.

read up a little on crosstab queries, and then start a new query.
Select the Union query, and bring down the following fields

DCODE, DISTNAME, TheYear and the 2008AVG_COE fields
Change the query type (it's on the toolbar) to CrossTab Query

This will add two rows to the spreadsheet, one labeled TOTAL, the
second Crosstab. All rows of Total: will be filled in with Group
By. Change the one in the 21008AVG_COE column to Sum, or Avg...
Now click in the DCODE column's crosstab row and select Row
Heading from the dropdown. select Row Heading for the DISTNAME
column, Column heading for the TheYear column and choose Value
for the 2008AVG_COE field.

Run the query, see that you are now pretty close to what you
want.

Q








I have 16 tables of data in the database, one per year that we
are interested in. These are excerpts from 16 much larger data
tables created outside our organization. Each of these tables
has 3 column headings: DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all
DCODES appear in all 16 tables. The DISTNAME is sometimes
different from table to table. The column xxxxFACT holds the
data for year xxxx.

I want to combine the 16 tables into one table that will have
these column headings: DCODE, DISTNAME, 2008FACT, 2007FACT,
2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

:


I think perhaps I wasn't clear enough on the background of
this issue. We're trying to compile data from several
extensive tables created outside our organization. We don't
need all of the information, just pieces of it. I'm trying
to use Access as a tool to keep our intern from manually
taking each Excel spread sheet, copying the data of interest
and then matching it by hand from year to year. It sounds
like the UNION query might be what I need, but I've played
with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about
reserved words, punctuation... So I may have multiple
problems. The example I found online didn't help me. There
must be better ones somewhere...I can usually follow an
example! Maybe because I'm using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of
the data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.


:

As suggested, you will do best to find out some more about
how Access works, as you are fighting it with your current
approach. There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near
the bottom, too.

To the question at hand, when planning your database you
need to think first in terms of structure. Each entity is
in its own table; each table contains attributes of one kind
of entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and
Address may be thought of as attributes of a person, but
work history is not. A person may have worked in one place
for their entire career, or they could be at their 20th
company. Adding a lot of fields to a table so you can
account for 1 to 20 work places (and hoping nobody has 21)
is not effective design. Rather, work history is in a table
that is related to the main People table. I will leave it at
that for now. The tutorial and the links will help you get
a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union
query to combine the data into what is typically called a
normalized (or more normalized, at least) structure. You
mention a query, but not how it is put together. The best
way to do that is with the query's SQL (View >> SQL, copy
what you see there, and post it into a message. It may be
best to limit the query to two or three years. The idea is
the same as with 20 queries, but it is more manageable to
read.

If the idea is that you will be keeping this data on
spreadsheets and using it within Access, you will be
approaching this differently than if you are moving the
project to Access, but in any case the principles are the
same, so the tutorial and other links are well worth
reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has
lots of info in addition to the 3 columns I'm interested
in, which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into
separate tables by year
with the 3 fields of interest and named DCODE as the
primary key in each because it is the only constant. I
then created a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected
from the 2008 data table), 2007Count (from the 2007 data
table, 2006Count (from the 2006 data table), etc. It
worked fine, EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's
what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names
(we'd like the most
current name), districts that are new (less than 16 years
of data), and districts that have disappeared (also less
than 16 years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so
I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!

 

[BruceM]

That was supposed to be "by way of the design grid..."

Oops. I got off track there. I forgot that a union query can be the
source for another query by was of the design grid. I was thinking for
some reason it was necessary to convert the union query directly into a
crosstab query.

One problem is that you can't use the query design grid with a
union query, so making the union query into a crosstab query (I'm
assuming it's possible) would have to be via direct SQL coding.
Perhaps if the union query is used to create a table, that could
be used as the basis for a crosstab query.

Once you have created the union query and saved it,
you create a crosstab query using the Union Query as the source table
in the design grid. It works perfectly, NO CODING Required

Bob Q.

This solved the query errors problem. But I need some help in
looking at the problem anew, because while I got data, it wasn't
what I need. Back to the beginning...

No! you need to move on from where you are, I said in my previous
reply:
Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.

read up a little on crosstab queries, and then start a new query.
Select the Union query, and bring down the following fields

DCODE, DISTNAME, TheYear and the 2008AVG_COE fields
Change the query type (it's on the toolbar) to CrossTab Query

This will add two rows to the spreadsheet, one labeled TOTAL, the
second Crosstab. All rows of Total: will be filled in with Group
By. Change the one in the 21008AVG_COE column to Sum, or Avg...
Now click in the DCODE column's crosstab row and select Row
Heading from the dropdown. select Row Heading for the DISTNAME
column, Column heading for the TheYear column and choose Value
for the 2008AVG_COE field.

Run the query, see that you are now pretty close to what you
want.

Q








I have 16 tables of data in the database, one per year that we
are interested in. These are excerpts from 16 much larger data
tables created outside our organization. Each of these tables
has 3 column headings: DCODE, DISTNAME, xxxxFACT

The DCODE is unique and constant across all tables, but not all
DCODES appear in all 16 tables. The DISTNAME is sometimes
different from table to table. The column xxxxFACT holds the
data for year xxxx.

I want to combine the 16 tables into one table that will have
these column headings: DCODE, DISTNAME, 2008FACT, 2007FACT,
2006FACT, 2005FACT, etc.

It can't be that difficult...

Thanks for your patience!

:


I think perhaps I wasn't clear enough on the background of
this issue. We're trying to compile data from several
extensive tables created outside our organization. We don't
need all of the information, just pieces of it. I'm trying
to use Access as a tool to keep our intern from manually
taking each Excel spread sheet, copying the data of interest
and then matching it by hand from year to year. It sounds
like the UNION query might be what I need, but I've played
with the syntax and can't get it right.

This is what I have so far to test 2 years...

SELECT [AVG_COE2008.DCODE], [AVG_COE2008.DNAME],
[AVG_COE2008.2008AVG_COE] FROM [AVG_COE2008]

UNION SELECT [AVG_COE1992.DCODE], [AVG_COE1992.DNAME],
[AVG_COE1992.1992AVG_COE]
FROM [AVG_COE1992];

I tried with and without brackets; got a message about
reserved words, punctuation... So I may have multiple
problems. The example I found online didn't help me. There
must be better ones somewhere...I can usually follow an
example! Maybe because I'm using Access 2003?

Thanks!

You actually need more brackets.

SELECT [AVG_COE2008].[DCODE], [AVG_COE2008].[DNAME],
[AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT ....

You will also want an additional field to contain the year of
the data.
SELECT "2008" AS theYear, [AVG_COE2008].[DCODE], [AVG_COE2008].
[DNAME], [AVG_COE2008].[2008AVG_COE] FROM [AVG_COE2008]
UNION SELECT "1992" AS theYear, [AVG_COE1992].[DCODE], ...

etc.

Once you have that working, we can proceed to perform some
analysis using a CrossTab Query.


:

As suggested, you will do best to find out some more about
how Access works, as you are fighting it with your current
approach. There is some good information here:
http://allenbrowne.com/casu-22.html

Also, check the tips here:
http://www.allenbrowne.com/tips.html

Look for the Links link on the right side of the page near
the bottom, too.

To the question at hand, when planning your database you
need to think first in terms of structure. Each entity is
in its own table; each table contains attributes of one kind
of entity only.
Without knowing more about the business situation it is
difficult to be specific. By way of example, Name and
Address may be thought of as attributes of a person, but
work history is not. A person may have worked in one place
for their entire career, or they could be at their 20th
company. Adding a lot of fields to a table so you can
account for 1 to 20 work places (and hoping nobody has 21)
is not effective design. Rather, work history is in a table
that is related to the main People table. I will leave it at
that for now. The tutorial and the links will help you get
a handle on this.

When working with data such as you have (a spreadsheet or
worksheet for each year) you probably need to use a Union
query to combine the data into what is typically called a
normalized (or more normalized, at least) structure. You
mention a query, but not how it is put together. The best
way to do that is with the query's SQL (View >> SQL, copy
what you see there, and post it into a message. It may be
best to limit the query to two or three years. The idea is
the same as with 20 queries, but it is more manageable to
read.

If the idea is that you will be keeping this data on
spreadsheets and using it within Access, you will be
approaching this differently than if you are moving the
project to Access, but in any case the principles are the
same, so the tutorial and other links are well worth
reading.

I am trying to compile several years of data from separate
spreadsheets (1
per year) into a single spreadsheet. Each source has
lots of info in addition to the 3 columns I'm interested
in, which are DCODE, DistrictName,
and 200xCount. So far we have imported the data into
separate tables by year
with the 3 fields of interest and named DCODE as the
primary key in each because it is the only constant. I
then created a Select query that looked
like: DCODE, DistrictName, 2008Count (these 3 selected
from the 2008 data table), 2007Count (from the 2007 data
table, 2006Count (from the 2006 data table), etc. It
worked fine, EXCEPT that it only reported data when all 16
tables had data for the DCODE (at least I surmise that's
what happened since
we lost nearly 1/3 of the 2008 rows.

I also now realize that we have districts with new names
(we'd like the most
current name), districts that are new (less than 16 years
of data), and districts that have disappeared (also less
than 16 years of data). We'd like
the table to show all data, which means I need to get a
comprehensive table
of DCODES and a way to get the most current name.

We do this type of data compilation fairly frequently, so
I'd like a robust
solution that I can adapt. I'm using Access 2003.

Thanks!