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 he
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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Hugh self taught" wrote in
message ...
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