A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Recordset with data joined from 2 tables?



 
 
Thread Tools Display Modes
  #1  
Old January 14th, 2010, 05:11 PM posted to microsoft.public.access.tablesdbdesign
Hugh self taught[_2_]
external usenet poster
 
Posts: 57
Default Recordset with data joined from 2 tables?

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


  #2  
Old January 15th, 2010, 04:53 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Recordset with data joined from 2 tables?

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


  #3  
Old January 15th, 2010, 09:58 AM posted to microsoft.public.access.tablesdbdesign
Hugh self taught[_2_]
external usenet poster
 
Posts: 57
Default Recordset with data joined from 2 tables?

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" wrote:

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


.

  #4  
Old January 15th, 2010, 03:04 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Recordset with data joined from 2 tables?

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.)

--
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 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" wrote:

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


.

  #5  
Old January 16th, 2010, 07:36 AM posted to microsoft.public.access.tablesdbdesign
Hugh self taught[_2_]
external usenet poster
 
Posts: 57
Default Recordset with data joined from 2 tables?

Hi Allen,

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

"Allen Browne" wrote:

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.)

--
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 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" wrote:

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


.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.