Auto generate an 'entry' number from different tables...

[captainfargon]

Howdy folks. I'm trying to set up a database for a charit
walking event. This event has 3 categories of walking events (1, 5 & 1
mile events). Each category has it's own individual walk numbers tha
need to be auto-generated by the database, for example, on the da
there will be 3 different people with a walk number of say ‘15’ (bu
they’ll have different coloured bibs to indicate which event they’r
in).

So what I am trying to set up is one master database that has a tabl
for the walkers (we’ll call it ‘Walkers’) which holds all of thei
personal details and their walk number. The walk number needs to com
from [B}separate tables[/B] that we’ll call ‘1mileWalk’, ‘5mileWalk’
‘10mileWalk’ which will have fields for ‘Ref_ID’ (taken from th
‘Walkers’ table) and a ‘WalkNo’ field (which, I assume, will be set t
AutoNumber).

So when a walker is entered into the 'Walkers' table their ‘Ref_ID
will be transfer across into whichever table is appropriate (dependin
on whether the ‘Distance’ field is entered as either 1,5 or 10 miles)
Their ‘WalkNo’ is then automatically generated and put back into th
Walkers table (& the world is a happier place or at least mine i
anyway…).

I’ve tried all sorts of joining tables but nothing seems to work. I
just won’t get a walk number out of the respective table. Does anyon
know of a way of doing this?



p.s. I also need to do the same thing for teams numbers i.e. th
various teams will need sequential numbers as well such a
TeamFamily01, TeamFamily02 etc.... But this should be resolved usin
the same procedure that cures the walk number problem stated above...
[/FONT

 

[Jeff Boyce]

(see in-line below)

Howdy folks. I'm trying to set up a database for a charity
walking event. This event has 3 categories of walking events (1, 5 & 10
mile events). Each category has it's own individual walk numbers that
need to be auto-generated by the database, for example, on the day
there will be 3 different people with a walk number of say '15' (but
they'll have different coloured bibs to indicate which event they're
in).

You DON'T want to fall into the trap of thinking that the Access Autonumber
data type and your "auto-generated" walk numbers are, in any way, related.
You will have to write code to ensure that you can have 3 different folks
doing three different events on the same day, all with the same "walk
number" (= 15).

So what I am trying to set up is one master database that has a table
for the walkers (we'll call it 'Walkers') which holds all of their
personal details and their walk number.

I'm pretty sure that you DON'T want to do this, for the long run. If you
record a person's walk number next to their name/address/..., what happens
when the same person comes back on another day? Are you going to write-over
the previous walk number? That's OK, if that's your intent, but as a data
bigot, I tend to want to know how many walks this person has attended,
entering WHICH specific distance, etc. From your description, this info
will be totally wiped out!

The walk number needs to come
from [B}separate tables[/B] that we'll call '1mileWalk', '5mileWalk' &
'10mileWalk' which will have fields for 'Ref_ID' (taken from the
'Walkers' table) and a 'WalkNo' field (which, I assume, will be set to
AutoNumber).

No, don't assume that -- see above. Why do you have separate tables for the
walk distances? Why are you passing the person's ID to the walk table(s)
and the walk table's ID back to the person table?

So when a walker is entered into the 'Walkers' table their 'Ref_ID'
will be transfer across into whichever table is appropriate (depending
on whether the 'Distance' field is entered as either 1,5 or 10 miles).
Their 'WalkNo' is then automatically generated and put back into the
Walkers table (& the world is a happier place or at least mine is
anyway.).

see below for an alternate approach...

I've tried all sorts of joining tables but nothing seems to work. It
just won't get a walk number out of the respective table. Does anyone
know of a way of doing this?



p.s. I also need to do the same thing for teams numbers i.e. the
various teams will need sequential numbers as well such as
TeamFamily01, TeamFamily02 etc.... But this should be resolved using
the same procedure that cures the walk number problem stated above...

I suspect you'll have to make significant changes to your data structure if
you want to use the same approach for teams as for individuals. You'll need
some way of associating persons into teams, and some way of entering teams
in distance events.

I'd recommend that you shut off your computer and grab paper and pencil. It
sounds like your data structure could do with a bit more normalization
before you start laying down tables.

The following is based on my assumptions that:
< an individual can enter one/more distance events (?same day or multiple
days?)
< an individual can be a member of a team
< the same individual can be a member of different teams for different
events, on different days
< a team consists of individuals
< the same team name can consist of different individuals for different
events, on different days
< ...?

Some structural notions:

< If you are going to enter Teams as well as Individuals, you need a way to
ID each. Before you do the following, ask yourself "Do I 'score' a team's
performance the same way I score an individual's performance?" If the
answer is "yes", then consider an "Entity" table:

tblEntity
EntityID
EntityType (individual or Team)

Now you need two more tables, one for the Individuals, one for the Teams:

tblPerson
EntityID (this will be the person's EntityID from the Entity table)
FName
LName
DOB
...

tblTeam
EntityID (the Team's EntityID from Entity table)
TeamName
<and probably NOTHING ELSE!>

Now you need information about your Day:

tblDayEvent
DayEventID
EventDate (don't use "Date" as a field name - this is an Access
reserved word)
EventName (don't use "Name" as a ..., you know!)
... (?any special attributes -- location, ...)

Getting closer. A small lookup table of distances (for your situation,
right now, only three records):

tlkpEventDistance
EventDistanceID
EventDistance

And a way to connect things:

trelEventEntry
EventEntryID
DayEventID (which day's event? from tblDayEvent)
EntityID (who/what is entered)
EventDistanceID (at what distance)
EventPerformance (?time)
... (?other event entry attributes?)

And finally, a way to show which individuals were on the team that entered:

trelEventTeamMembers
EventTeamMembersID
DayEventID (which day's event)
EventDistanceID (at what distance)
TeamEntityID (EntityID of Team)
PersonEntityID (EntityID of ONE team member)

This last table gets a row for each team member.

So now, if you want to know every team (Team's EntityID) member (Person's
EntityID) participating on a particular day (DayEventID) at a particular
distance (EventDistanceID), you can use that last table, joined as needed to
the earlier tables for details like FName, Team Name, ...

And if you want to record an Entity's performance, there's a place.

Now, full circle, ... you want to have a way to generate race bibs with
ID#s. Are you absolutely sure you want to have "duplicate" numbers
possible. I understand it may be traditional, but is it a requirement that
someone in every race has the number "1" bib? If so, you'll still need to
create some code that generates the sequence numbers, and does so from "1"
for each unique combination of DayEventID, and EventDistanceID.