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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|