Saving multiple records

[Molly]

I am trying to design a database to record the usage of
traps (all individually designated with an ID number) on
weekly insect trapping routes. Because traps periodically
break and must be rotated in and out of usage, I must
record the exact ID numbers used each week in order to
have good historical data. Is it possible to create a form
that allows me to click one "save" button and save all the
ID numbers for the selected date (ActiveX Calendar
Control) to my table at once? I already have built queries
to call up the currently used numbers to list boxes in the
form. I just want to select all the numbers in the list
box, hit save, and have it create an individual record in
the table for each ID. Obviously, I don't know very much
about Access, so any help is greatly appreciated.

Thanks so much!

 

[Jeff Boyce]

Molly

Why? As in "why do you want a row-per-active-trap-per-date?"

Remember, we don't know your table structure, nor the business need are you
attempting to satisfy.

If you already have a query that can "pick" the active traps, why do you
need to (redundantly) store that info?

More info, please...

Jeff Boyce
<Access MVP>

 

[Molly]

Jeff,
Thanks for your reply!
The story behind this database is that we want to use it
to track disease monitoring routes in our county. Here's a
little background to help you understand: We are on the
lookout for certain mosquito-borne diseases, such as West
Nile and Eastern Equine Encephalitis. One way we do this
is by setting traps out one night a week in six sites
around the county. I have to know which traps have gone
out each week, and which site they went to. I also have to
be able to look back at the end of the year and say with
100% certainty that on the night of May 25 (or any other
night), trap #s 101, 105, 294, 523, 125, and 390 went out.
We only have a limited number of traps, so we assign a
group to each site and put them out in rotation. However,
when enough time goes by that we cycle around to a
particular group again, that group may have changed. Traps
may have broken, been discarded, etc., and new ones put in
their place. Now instead of putting out 101, 105, 294,
523, 125, and 390, we are putting out 101, 107, 404, 523,
125, and 390.

Now, getting back to how this translates into a database:
I am under the impression (please correct me if I'm wrong)
that if the data is only in a query, when I change a trap
in a rotation, the query will "hide" the trap number that
was previously in that slot, thus making it look like the
new number has been there all along. Is this correct?

I would be more than happy to send you a copy of the
database if you would like to look it over and see what
I'm trying to do. When I started this job, I did not know
anything about Access. I was promised a series of training
courses through the local college to get me up to speed,
but when the time came to register, there was no money
left in the budget. So now I have to build this databases
and learn as I go! It's been a steep learning curve.

Thank you so much for taking the time to help me with
this. Please let me know if there is anything I can do in
return.

Molly

 

[Jeff Boyce]

Molly

As for your last comment, you can help someone else out when you have the
opportunity.

Let's see if I can describe your data in my terms and you can still
recognize it <g>!

You have traps, each with it's unique identifier. The unique identifier
doesn't change.

A table to hold that info might look like:

tblTrap
TrapID
YourTrapNumber
...?description/model/...

I'd suggest an Autonumber datatype for TrapID, unless you are ABSOLUTELY
certain that something like a serial number would be unique and there would
never be the situation in which you could have two traps, perhaps from
different manufacturers, both with an identical serial number.


You have sites, so maybe the table looks like:

tblSite
SiteID (see above discussion re: Autonumber)
SiteName
SiteDescription
SiteLongitute
SiteLatitude
...
(I don't have any idea what characteristics you keep about sites.)


You have a collection of traps located at a site on a date. A table for
this would use one row per trap X site X date combination, something like:

trelSiteTrap
SiteTrapID (autonumber, ...)
SiteID (which Site from tblSite)
TrapID (which Trap from tblTrap)
SiteTrapSetDate (assuming that traps are set out at a site for only
one 24 hour period, or that you don't need to monitor begin- and
end-date/time)

Now, to answer your question, "which traps were located at SiteX on May 25,
2003?" ...
Build a query that joins the three tables above, on the SiteID and TrapID.
As criteria, use the Site ("X") and the date, and return all TrapIDs (and
?name, ...).

You mentioned "groups" of traps, but I can't tell if that means you work
with pre-assembled collections, whose membership changes, or if you were
simply saying that a bunch of traps ("a group") goes to a site. If the
former, you'll need to somehow name & identify groups, then build another
junction table ("trelGroupSiteMembers") that associates TrapID with GroupID
from Date1 to Date2.

Good luck!

Jeff Boyce
<Access MVP>