B
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!
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!