Recordset with data joined from 2 tables?

[Hugh self taught]

Hi to all the Gurus out there,

I originally developed a database for keeping record of registered
competitors & their matching as competitive couples.

Now I'm developing a database for recording the points earned at
competitions & I use the same back end from the Register database. My
difficulty is dealing with couples who are not registered with our
organization.

I've started by getting the registered couples data & points calculations
working correctly. Now I need to incorporate the non registered. I need to
keep record of them as I also need to know how many couples competed in an
event. The registered couples have a number which they use for the year which
I use in a cbo box. The non registered get a different temp number at every
competition. The temp number will never be in the database at the time of
data input as it is +- 100 greater than the highest registered number. My
couples number is a text field.

Now if I lookup the couple in the cbo they are not_in_list (obviously) &
I've coded the "don't add" part of the event.

The "non" competitors are in tblNonCompetitor & their couple matching is in
tblNonCouples.

Now comes the scary part... Best solution to go forward bearing in mind that
a non registered couple may join our organization during the year & be
assigned their own permanent number.

Any pointers, suggestions how I should best deal with this will be sooooo
appreciated

 

[Allen Browne]

Interesting question.

For starters, you'll need a Client table (one record for each person,
whether part of a registered couple or not.)

You will then have a table for identifying the people who make up a couple.
I suspect this table will have lesser importance from the database
perspective than it seems to have for the sporting association. From the db
point of view, consider things like:

- Are couple for a limited timespan? E.g. Bill and Betty may be a couple for
2007, but Bill's part of a different couple in 2010.

- Could some persons be registered to multiple couples simultaneously? Or
does joining a couple automatically terminate membership of a previous
couple?

- Is *everything* done in couples? For example, in tennis doubles matches
are played as couples, but singles matches are not in couples. Golf could be
couples for foursomes or ... (Remember you have to foresee every possible
situation the db must handle.)

You will have a table of events (each event is something that some couple
can win), and then an EventDetail table (the persons in that event.) I would
be very tempted to set up the EventDetail table so that it relates to the
Client table (rather than the Couple table), so you have a record of each
person in the event. This copes better with the ad-hoc couples you need to
handle.

One possibility (may not be ideal) would be to treat the registered couples
as 'clients' in their own right. This allows you to associate a 'client'
with an event, where the client could be a registered couple or a person (2
records where the persons aren't a registered couple.) If that might be
worth investigating, follow the example of grouping clients here:
http://allenbrowne.com/AppHuman.html

Another possibility would be have an autonumber in your Couples table, and
another field for the registered couple number. This allows you to create
records for unregistered couple (i.e. leave the RegCoupleNum field blank for
that record), but still use the autonumber for your relationships.

HTH

 

[Hugh self taught]

Hi Allen, Thanks for responding.

In the "Registrar's" database the Competitors are recorded in a table with
all their details & there is a couples table where the couples are connected.
Ie referenced to the PK of the competitors table. The couples tbl has a
autonumber as PK & a txt field as the couples' number then 2 fields
referencing the PK of the competitors tbl for male & female.

To clarify this is Ballroom & Latin dancing. The couples can change several
times in any given year & I record the date of change for my calculations.
The male keeps the number & the female changes.

I have basically replicated that format for "non registered" couples in a
separate database where I will do the points calculations & use the BE of the
registered for access to those tables/records.

I started to come unstuck when trying to figure out how I will look up the
couples since I don't record couple numbers for the "non reg" couples
(changes every comp) & should I need to change the structure of the db then
I'd rather do so now before it's more developed.

So far everything is pretty much in line with your suggestions in terms of
the basic design. My stumbling block is how to get the 2 sets of couples data
together to find the couple. A cbo box could show columns for number, Male,
female, which will enable easy enough selection of an non numbered couple to
be selected by their names combination.

If that is what you also think as being a suitable solution, can you suggest
how I would go about it. In the tbl that records the results of an event, I
have a Y/N field to record if they are Reg or Non-Reg for later reporting. ie
know which tbl to look in.

 

[Allen Browne]

Competitors table sounds good. Couples table is also relational.

It seems to me that you need to record the individuals in an event rather
than the couples. You might interface it with a combo so the data entry
operator can identify a couple that way, but I wouldn't store that because
(a) the couples aren't consistent over time, and (b) there's not always a
couple to choose.

If you store the individual competitors (rather than the couple number) for
the event, it would still be possible to look up the couple number for the
couple (if it exists.)

 

[Hugh self taught]

Hi Allen,

I hear what you're saying. I'll give it a go Thanks!