A
Ardsnard
Hi, I apologize for the length in advance~
I am developing a database to handle a West Nile Virus/Mosquito
surveillance system. One group of people is setting mosquito traps,
then collecting, counting and sorting the trap results based on
species. Another group prepares and submits certain trap samples to a
lab for West Nile testing.
I have a table named TrapEvents for each instance a trap is set. The
primary key is a combination of three fields: TrapDate, TrapLocationID,
and TrapMethodID (Some TrapLocations have multiple types of traps for
catching different species). There are some other fields for
Temperature/Wind/Precip, etc. I also assign a TrapEventID autonumber.
Since many different species can be caught in each trap, I have a
second table called TrapData, where the primary key is a combination of
TrapEventID and Species ID. There is also a field for the # of
mosquitoes of that species caught, and an autonumber field called
TrapDataID.
The forms for entering all this data are working good, so the field
crew can fill them out and send the specimens on the lab prep people no
problem.
The lab prep people can pool up to 50 mosquitoes in each lab sample
(PoolID) to be tested. Sometimes, there will be >300 mosquitoes per
TrapDataID, so it might be broklen into 6 PoolID's. Other times, there
will be maybe 1-5 mosquitoes, so multiple TrapDataID's (spanning
different TrapDates, TrapLocations, TrapMethods, or Species) will be
combined into one PoolID. As a result, I think I will need to create
another table of PoolID results for each TrapDataID.
Question #1: How can I determine if the Lab results for each TrapDataID
are positive,negative, or just contributed in part to a positive pool?
Remember, each PoolID might be made of one or multiple TrapDataID's,
but some TrapDataID's will be split into multiple PoolID's. One
solution is never to combine separate TrapDataID's into one pool, but
financially this is not possible.
Question#2: There are two tables for the lab data aspect. One is a
Pool table, with PoolID as primary key, and some other fields for date
submittal, name of lab, type of test performed, etc. The other is a
PoolMakeup table, with a primary key made up of PoolID and TrapDataID.
There is also an autonumber for each pool fraction.
When a lab prepper Is filling out the pool forms, the TrapDataID number
is just a meaningless number to him. Therefore, I created a series of
cascading combo boxes. The first combo selects the TrapDate, narrowing
the next combo to available TrapLocations from that TrapDate, and so on
through TrapMethod and Species. At this point, the four components
needed to determine the TrapDataID are known. I have all of this
working, but I dont know how to display the TrapDataID in an invisible
text box nor how to record this TrapDataID value into the PoolMakeup
table. Right now, the PoolMakeup form is in single form view, but I
have a feeling it is going to get messier when I add the PoolMakeup
form to the PoolID form as a continuous subform.
Any help is appreciated, and thanks for reading all that junk
I am developing a database to handle a West Nile Virus/Mosquito
surveillance system. One group of people is setting mosquito traps,
then collecting, counting and sorting the trap results based on
species. Another group prepares and submits certain trap samples to a
lab for West Nile testing.
I have a table named TrapEvents for each instance a trap is set. The
primary key is a combination of three fields: TrapDate, TrapLocationID,
and TrapMethodID (Some TrapLocations have multiple types of traps for
catching different species). There are some other fields for
Temperature/Wind/Precip, etc. I also assign a TrapEventID autonumber.
Since many different species can be caught in each trap, I have a
second table called TrapData, where the primary key is a combination of
TrapEventID and Species ID. There is also a field for the # of
mosquitoes of that species caught, and an autonumber field called
TrapDataID.
The forms for entering all this data are working good, so the field
crew can fill them out and send the specimens on the lab prep people no
problem.
The lab prep people can pool up to 50 mosquitoes in each lab sample
(PoolID) to be tested. Sometimes, there will be >300 mosquitoes per
TrapDataID, so it might be broklen into 6 PoolID's. Other times, there
will be maybe 1-5 mosquitoes, so multiple TrapDataID's (spanning
different TrapDates, TrapLocations, TrapMethods, or Species) will be
combined into one PoolID. As a result, I think I will need to create
another table of PoolID results for each TrapDataID.
Question #1: How can I determine if the Lab results for each TrapDataID
are positive,negative, or just contributed in part to a positive pool?
Remember, each PoolID might be made of one or multiple TrapDataID's,
but some TrapDataID's will be split into multiple PoolID's. One
solution is never to combine separate TrapDataID's into one pool, but
financially this is not possible.
Question#2: There are two tables for the lab data aspect. One is a
Pool table, with PoolID as primary key, and some other fields for date
submittal, name of lab, type of test performed, etc. The other is a
PoolMakeup table, with a primary key made up of PoolID and TrapDataID.
There is also an autonumber for each pool fraction.
When a lab prepper Is filling out the pool forms, the TrapDataID number
is just a meaningless number to him. Therefore, I created a series of
cascading combo boxes. The first combo selects the TrapDate, narrowing
the next combo to available TrapLocations from that TrapDate, and so on
through TrapMethod and Species. At this point, the four components
needed to determine the TrapDataID are known. I have all of this
working, but I dont know how to display the TrapDataID in an invisible
text box nor how to record this TrapDataID value into the PoolMakeup
table. Right now, the PoolMakeup form is in single form view, but I
have a feeling it is going to get messier when I add the PoolMakeup
form to the PoolID form as a continuous subform.
Any help is appreciated, and thanks for reading all that junk