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.

 

[captainfargon]

Thanks muchly for your reply Jeff. It's very appreciated that you too
the time to have ponder my problem & post such a comprehensiv
response...

With regard to some of the points you raised in it and, in particular
with regard to the assumptions that laid the basis for you
suggestions:

-< an individual can enter one/more distance events (?same day o
multiple days?)
< an individual can be a member of a team
< the same individual can be a member of different teams for differen
events, on different days
< a team consists of individuals
< the same team name can consist of different individuals fo
different events, on different days
< ...?-

In actual fact the 3 walks (and there are only 3 walks) will be held o
one day and individuals can't enter separate walks or multiple tea
events (yes, an individual is a member of a team). There is a one mil
event for children with severe handicaps, a 5 mile walk for those ver
young or old, or only mildly limited by their disability. And, finally
there is the 10 mile walk for everybody else and this is the walk tha
the teams of 4 walkers or more can enter i.e. Men's, Women's, Over 55
Under 18, Pub, University / College etc. Hence it should now make sens
why walkers are able to have the same walk numbers (and wil b
differentiated on the day dependant by what colour bib they ar
wearing).

Further to this and some more background information, all of th
walkers are raising money for their choice of over 120 charities. Thes
charity organisations need to be able to look up all of the walkers wh
are walking for them online in order to give them updates, arrang
payment information etc. Which brings me to an important point in al
of this - it is all going to be run online through the walk's websit
(including the admin section & the manual entry of postal entries)
This is why walkers need to be in one centralised database, which wil
provide the required functionality to the event organisers
charities...

So as things stand, I think I'm going to have 3 main tables which wil
cover the walk events (1, 5 & 10 miles) which can provide their wal
numbers via the autonumber function (hope this makes sense why now)
This will cure the walk number problem for the 3 walks but as for th
numbering of teams, the administrator will just have to manually ad
this in later. I'm pretty good at putting together websites but don'
have the foggiest about setting up databases (as I'm sure is probabl
blindingly obvious to you folk by now. I offered to do all of this t
help out a friend & for the respective charities that do such goo
work, but it's grown into a bit of a behemoth).

So coming up with this auto team number with my limited knowledge isn'
going to work unless yourself or some other Access grandmaster wizar
{who wants to help a couple of thousand of disabled folks =) } has an
great ideas or guidance of what I should do then that is the way I'l
have to go...

Thanks again for your time Jeff for looking into this for me

 

[Jeff Boyce]

De nada. I was intending to stimulate further thought, more than provide a
"true" answer. Perhaps you have a nearby Access User Group that could also
provide further support.

Good luck

Jeff Boyce
<Access MVP>