Normalizing an older table through maketable queries

[BonnieW via AccessMonster.com]

I have a table with about 10000 records. It's set up like this:

tblFrogs_and_Toads
PK
Date_entered
obs_entered
Date_obs
Observer
Route_ID
Site_ID
Species1
Species2
Species3
...
Species10
Comments

In the "Species" fields, a calling level (values of 0-3) was entered. Many
Species fields were left Null, however. Also, users were supposed to enter
only one species' calling level per record: they did not. There are records
which have no species' calling levels entered; there are records which have
3-4 species' calling levels entered. I'm trying to get this a bit more
normalized, for both easier data entry and for easier analysis/reporting. I'd
like to get this done properly, so I've set up make-table queries to pull out
the records which have only one species associated with them. However, I'm
at a loss as to how to do this for the records that have more than one
species. Perhaps my brain's not working straight today, but I'm pretty sure
it's doable... I'd be able to do it manually, but I'd much rather not.

If it helps, this is the query I'm using to pull out the records with only
one species associated:
SELECT Frogs_and_Toads_mod.Observation_ID, Frogs_and_Toads_mod.Date_Entered,
Frogs_and_Toads_mod.Observer_Entry, Frogs_and_Toads_mod.Date_Observed,
Frogs_and_Toads_mod.Observer, Frogs_and_Toads_mod.Route_ID,
Frogs_and_Toads_mod.Site_ID, Frogs_and_Toads_mod.Wood_Frog,
Frogs_and_Toads_mod.Chorus_Frog, Frogs_and_Toads_mod.Spring_Peeper,
Frogs_and_Toads_mod.Cricket_Frog, Frogs_and_Toads_mod.Leopard_Frog,
Frogs_and_Toads_mod.Pickeral_Frog, Frogs_and_Toads_mod.American_Toad,
Frogs_and_Toads_mod.Gray_Treefrog, Frogs_and_Toads_mod.Green_Frog,
Frogs_and_Toads_mod.Bull_Frog, Frogs_and_Toads_mod.Comments INTO
tblGreyTreefrogONLY
FROM Frogs_and_Toads_mod
WHERE (((Frogs_and_Toads_mod.Wood_Frog)=0 Or (Frogs_and_Toads_mod.Wood_Frog)
Is Null) AND ((Frogs_and_Toads_mod.Chorus_Frog)=0 Or (Frogs_and_Toads_mod.
Chorus_Frog) Is Null) AND ((Frogs_and_Toads_mod.Spring_Peeper)=0 Or
(Frogs_and_Toads_mod.Spring_Peeper) Is Null) AND ((Frogs_and_Toads_mod.
Cricket_Frog)=0 Or (Frogs_and_Toads_mod.Cricket_Frog) Is Null) AND (
(Frogs_and_Toads_mod.Leopard_Frog)=0 Or (Frogs_and_Toads_mod.Leopard_Frog) Is
Null) AND ((Frogs_and_Toads_mod.Pickeral_Frog)=0 Or (Frogs_and_Toads_mod.
Pickeral_Frog) Is Null) AND ((Frogs_and_Toads_mod.American_Toad)=0 Or
(Frogs_and_Toads_mod.American_Toad) Is Null) AND ((Frogs_and_Toads_mod.
Gray_Treefrog)>0) AND ((Frogs_and_Toads_mod.Green_Frog)=0 Or
(Frogs_and_Toads_mod.Green_Frog) Is Null) AND ((Frogs_and_Toads_mod.Bull_Frog)
=0 Or (Frogs_and_Toads_mod.Bull_Frog) Is Null));

Is there a less tedious way to do this?

Thanks in advance!

 

[Jeff Boyce]

Bonnie

Rather than "making tables", consider creating queries that return records,
then appending those records to a single, better-normalized table.

You could, for instance, build a query that returned the basic row
information, and checked for Species1 values (only that column, none of the
other Species# columns). By adding the selection criterion "Is Not Null"
(no quotes) under the species column, you'll get a set of records with
non-null Species1. Save this query as qSpecies1.

Make a copy of that query, saved as Species2. Use the design view to change
from the Species1 column to the Species2 column and re-save.

Rinse & repeat until you have your 10 "species'" queries. When you are
satisfied that these 10 queries are "finding" all the observations, convert
each query to an append query. Append from the Species# query to your
permanent tblObservations. Just once for each Species# query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BonnieW via AccessMonster.com]

Jeff,

This is great. I can't believe I didn't think of this! Thank you very much.


-Bonnie

Bonnie

Rather than "making tables", consider creating queries that return records,
then appending those records to a single, better-normalized table.

You could, for instance, build a query that returned the basic row
information, and checked for Species1 values (only that column, none of the
other Species# columns). By adding the selection criterion "Is Not Null"
(no quotes) under the species column, you'll get a set of records with
non-null Species1. Save this query as qSpecies1.

Make a copy of that query, saved as Species2. Use the design view to change
from the Species1 column to the Species2 column and re-save.

Rinse & repeat until you have your 10 "species'" queries. When you are
satisfied that these 10 queries are "finding" all the observations, convert
each query to an append query. Append from the Species# query to your
permanent tblObservations. Just once for each Species# query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with about 10000 records. It's set up like this:

[quoted text clipped - 66 lines]

Thanks in advance!