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
|
|||
|
|||
Tables Relationship Question
I am new at this so please have patience with the ignorant.
I do not understand what is happening and hope someone can explain. I have two tables: Table 1 has several fields. The field called "telephone number" is the Primary Key... Table 2 has several fields. The fields called "telephone number" and a field called "telephone number features" are the Primary Keys Table 2 also has a field "telephone number 1" that is identical to the "telephone number" field used as a primary key(values) I have set up a relationship "1 to many" between the fields "telephone number " in table 1 and the "telephone number 1" in table 2 . I used to have this working well but I have screwed something up. When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. My test data looks accurate but it appears that new records I input to both tables do not match each other. Thank You, Greg |
#2
|
|||
|
|||
From your explanations it would seem that you are storing identical data in
two fields on the same tbl Table 2 also has a field "telephone number 1" that is identical to the "telephone number" field used as a primary key(values) but perhaps I missunderstand "Greg Jesky" wrote in message ... I am new at this so please have patience with the ignorant. I do not understand what is happening and hope someone can explain. I have two tables: Table 1 has several fields. The field called "telephone number" is the Primary Key... Table 2 has several fields. The fields called "telephone number" and a field called "telephone number features" are the Primary Keys Table 2 also has a field "telephone number 1" that is identical to the "telephone number" field used as a primary key(values) I have set up a relationship "1 to many" between the fields "telephone number " in table 1 and the "telephone number 1" in table 2 . I used to have this working well but I have screwed something up. When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. My test data looks accurate but it appears that new records I input to both tables do not match each other. Thank You, Greg |
#3
|
|||
|
|||
"Greg Jesky" wrote in
: When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! No it's not, it's bad. You really should not be working with data in table datasheets. That's what forms are for. For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. See above. You presumably need some method of making sure that the foreign key (Table2.TelephoneNumber) receives the value of the appropriate related field (Table1.TelephoneNumber). If you were using a form with a subform Access would do it for you automatically. I don't know you would do it with table datasheets: that's not what they are for. My test data looks accurate but it appears that new records I input to both tables do not match each other. Actually, you would not want two tables to match each other. There would not be any reason to store stuff twice. By the way, what was Table2.TelephoneNumber2 for? B Wishes Tim F |
#4
|
|||
|
|||
Tim and DL,
Thank you!! I agree that forms are the way to go but I am trying to take some short cuts during testing. Since I keep changing my tables I often need to also redo my forms. Probably bad.. My problem is; I have a table of telephone numbers and related information (Table 1). I have fields- Telephone number, Price of line(telephone number), Billing account number, Service provider, date of installation, date of disconnection, Tax cost, eleemosynary concession, etc. The primary key for this table is the telephone number. I have another table comprised of call features for each line(telephone number) in the table listed above. In this table(Table 2) I have fields such as telephone number, calling feature, calling feature price, calling feature date of installation, calling feature date of disconnection, etc. A telephone number in table could have 0 calling features or 8-10 calling features. Which means for each telephone number in table one I could have no records in table 2 or 10 records in table 2. I have established 2 fields as primary keys in table 2, telephone number and calling feature. (A calling feature is something like call waiting) I established a relationship between telephone numbers in each table. However since the primary key in table one had to be a foreign key in table 2, I established another field in table 2 called "telephone number 1" identical to the field "telephone number". And I have created a "one to many relationship". When I run a select only query attempting to extract information from both tables I do not get complete output in the dynaset. For example I define telephone number and price of line (telephone number) from table 1 and the calling features and cost of features from table 2. For some of my test records I get a record in the dynaset for each call feature in table 2(this is what I want). For other telephone numbers I get no records in the dynaset even though my test data at least looks valid i.e.. correct telephone number in both tables and related call features in table 2. I hope I have not confused you worse than I have confused myself.... Greg "Tim Ferguson" wrote in message ... "Greg Jesky" wrote in : When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! No it's not, it's bad. You really should not be working with data in table datasheets. That's what forms are for. For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. See above. You presumably need some method of making sure that the foreign key (Table2.TelephoneNumber) receives the value of the appropriate related field (Table1.TelephoneNumber). If you were using a form with a subform Access would do it for you automatically. I don't know you would do it with table datasheets: that's not what they are for. My test data looks accurate but it appears that new records I input to both tables do not match each other. Actually, you would not want two tables to match each other. There would not be any reason to store stuff twice. By the way, what was Table2.TelephoneNumber2 for? B Wishes Tim F |
#5
|
|||
|
|||
The telephone table would contain anything unique to a phone number (such as
the number itself, account number, etc. The second table could be called the features table. Something like this, maybe (field names are indented below the table name): tblTelephone PhoneNo (primary key, or PK) AccountNumber etc. tblFeatureList FeatureListID (PK) PhoneNo (foreign key, or FK) Feature description You would establish a relationship (Tools Relationships) between the PK and FK fields in the two tables. With a form based on tblPhone and a subform based on tblFeatureList (make the subform default view Continuous on the form's property sheet), any entries in the subform will automatically have the PK from tblPhone as the FK. Additional thoughts: Phone number may not be the best choice for PK. These two numbers: (999)555-1212 (999) 555-1212 are the same to us, but are unique and distinct as far as Access is concerned. You could maybe set up an input mask to prevent this, but a foreign phone number would be a problem. Remember that the PK does not need to be a value with which you work. Some would argue that you should not even see the PK, that it does its work behind the scenes. You may want a features table to describe the available features, including cost. It would be related to tblFeaturesList as described above for other tables. If the cost of a feature changes you may want that change to be universal rather than needing to edit a lot of individual records. Could an account have more than one phone number, or could a phone number ever be assigned to a different account? If yes to either, account should be in a separate table from the phone number. As it is, the account number is a feature of the telephone number. "Greg Jesky" wrote: Tim and DL, Thank you!! I agree that forms are the way to go but I am trying to take some short cuts during testing. Since I keep changing my tables I often need to also redo my forms. Probably bad.. My problem is; I have a table of telephone numbers and related information (Table 1). I have fields- Telephone number, Price of line(telephone number), Billing account number, Service provider, date of installation, date of disconnection, Tax cost, eleemosynary concession, etc. The primary key for this table is the telephone number. I have another table comprised of call features for each line(telephone number) in the table listed above. In this table(Table 2) I have fields such as telephone number, calling feature, calling feature price, calling feature date of installation, calling feature date of disconnection, etc. A telephone number in table could have 0 calling features or 8-10 calling features. Which means for each telephone number in table one I could have no records in table 2 or 10 records in table 2. I have established 2 fields as primary keys in table 2, telephone number and calling feature. (A calling feature is something like call waiting) I established a relationship between telephone numbers in each table. However since the primary key in table one had to be a foreign key in table 2, I established another field in table 2 called "telephone number 1" identical to the field "telephone number". And I have created a "one to many relationship". When I run a select only query attempting to extract information from both tables I do not get complete output in the dynaset. For example I define telephone number and price of line (telephone number) from table 1 and the calling features and cost of features from table 2. For some of my test records I get a record in the dynaset for each call feature in table 2(this is what I want). For other telephone numbers I get no records in the dynaset even though my test data at least looks valid i.e.. correct telephone number in both tables and related call features in table 2. I hope I have not confused you worse than I have confused myself.... Greg "Tim Ferguson" wrote in message ... "Greg Jesky" wrote in : When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! No it's not, it's bad. You really should not be working with data in table datasheets. That's what forms are for. For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. See above. You presumably need some method of making sure that the foreign key (Table2.TelephoneNumber) receives the value of the appropriate related field (Table1.TelephoneNumber). If you were using a form with a subform Access would do it for you automatically. I don't know you would do it with table datasheets: that's not what they are for. My test data looks accurate but it appears that new records I input to both tables do not match each other. Actually, you would not want two tables to match each other. There would not be any reason to store stuff twice. By the way, what was Table2.TelephoneNumber2 for? B Wishes Tim F |
#6
|
|||
|
|||
"Greg Jesky" wrote in
: I agree that forms are the way to go but I am trying to take some short cuts during testing. Since I keep changing my tables I often need to also redo my forms. Probably bad.. Not so much bad as just out-or-order. Always get the schema design nailed down and right before even thinking about the UI. It's reasonable to use the table datasheets for debugging and testing (that is what they _are_ for), but not for final users. I have a table of telephone numbers and related information (Table 1). [snipped table description] The primary key for this table is the telephone number. This is fine: like Bruce says, a long text string is not the best choice but it's not theoretically wrong. table(Table 2) I have fields such as telephone number, calling feature, calling feature price, calling feature date of installation, calling feature date of disconnection, etc. Okay too. If the FeaturePrice is an attribute of the Feature (rather than the Feature as installed on this TelephoneNumber), I'd probably create a new table of Features (FeatureCode, Price, WaitingList, etc) to maintain them; but you may be fine as you are. I established a relationship between telephone numbers in each table. However since the primary key in table one had to be a foreign key in table 2, I established another field in table 2 called "telephone number 1" identical to the field "telephone number". And I have created a "one to many relationship". No need for any of this. Make the relationship between Table1.TelephoneNumber and Table2.TelephoneNumber. The fact that Table2.TelephoneNumber is part of the PK of its table is not a problem -- in fact, it's pretty much usual. For some of my test records I get a record in the dynaset for each call feature in table 2(this is what I want). For other telephone numbers I get no records in the dynaset even though my test data at least looks valid Try creating the relationship correctly and then if the query does not work, post the SQL here and we can try to help All the best Tim F |
#7
|
|||
|
|||
BruceM,
I have received several suggestions and will use them all to improve my capabilities. Bruce , you really got me thinking about your comment regarding the format of telephone numbers. I was trying to clean things up when my problems began. I input telephone numbers through a mask for both table 1 and table 2. I did begin to review my "masks" that are displayed at the bottom left and found different formats. I have made all the formats the same using wizards and everything is back to normal(good). However, I did a select only query on table 1 and sorted on telephone number(ascending). I now get a sequence (270) 759-0591 (270) 759-0592 .. .. (352) 271-0300 (352) 271-0301 ... (770) 388-9884 ... (352) 271-0400 (numbers input since correcting masks) Is there anything I can use to see the actual data (ASCII?) to determine why the records are sorting as they are? Thank You All. I believe I am about ready to start building the DB for real leaving testing behind me "BruceM" wrote in message ... The telephone table would contain anything unique to a phone number (such as the number itself, account number, etc. The second table could be called the features table. Something like this, maybe (field names are indented below the table name): tblTelephone PhoneNo (primary key, or PK) AccountNumber etc. tblFeatureList FeatureListID (PK) PhoneNo (foreign key, or FK) Feature description You would establish a relationship (Tools Relationships) between the PK and FK fields in the two tables. With a form based on tblPhone and a subform based on tblFeatureList (make the subform default view Continuous on the form's property sheet), any entries in the subform will automatically have the PK from tblPhone as the FK. Additional thoughts: Phone number may not be the best choice for PK. These two numbers: (999)555-1212 (999) 555-1212 are the same to us, but are unique and distinct as far as Access is concerned. You could maybe set up an input mask to prevent this, but a foreign phone number would be a problem. Remember that the PK does not need to be a value with which you work. Some would argue that you should not even see the PK, that it does its work behind the scenes. You may want a features table to describe the available features, including cost. It would be related to tblFeaturesList as described above for other tables. If the cost of a feature changes you may want that change to be universal rather than needing to edit a lot of individual records. Could an account have more than one phone number, or could a phone number ever be assigned to a different account? If yes to either, account should be in a separate table from the phone number. As it is, the account number is a feature of the telephone number. "Greg Jesky" wrote: Tim and DL, Thank you!! I agree that forms are the way to go but I am trying to take some short cuts during testing. Since I keep changing my tables I often need to also redo my forms. Probably bad.. My problem is; I have a table of telephone numbers and related information (Table 1). I have fields- Telephone number, Price of line(telephone number), Billing account number, Service provider, date of installation, date of disconnection, Tax cost, eleemosynary concession, etc. The primary key for this table is the telephone number. I have another table comprised of call features for each line(telephone number) in the table listed above. In this table(Table 2) I have fields such as telephone number, calling feature, calling feature price, calling feature date of installation, calling feature date of disconnection, etc. A telephone number in table could have 0 calling features or 8-10 calling features. Which means for each telephone number in table one I could have no records in table 2 or 10 records in table 2. I have established 2 fields as primary keys in table 2, telephone number and calling feature. (A calling feature is something like call waiting) I established a relationship between telephone numbers in each table. However since the primary key in table one had to be a foreign key in table 2, I established another field in table 2 called "telephone number 1" identical to the field "telephone number". And I have created a "one to many relationship". When I run a select only query attempting to extract information from both tables I do not get complete output in the dynaset. For example I define telephone number and price of line (telephone number) from table 1 and the calling features and cost of features from table 2. For some of my test records I get a record in the dynaset for each call feature in table 2(this is what I want). For other telephone numbers I get no records in the dynaset even though my test data at least looks valid i.e.. correct telephone number in both tables and related call features in table 2. I hope I have not confused you worse than I have confused myself.... Greg "Tim Ferguson" wrote in message ... "Greg Jesky" wrote in : When I look at the at table 1(datasheet) and click the + on the left side of a row I get a list of records from table 2 (this is good)! No it's not, it's bad. You really should not be working with data in table datasheets. That's what forms are for. For records I input directly to the tables(1 and 2) since my "improvements" I now only displays an empty row. See above. You presumably need some method of making sure that the foreign key (Table2.TelephoneNumber) receives the value of the appropriate related field (Table1.TelephoneNumber). If you were using a form with a subform Access would do it for you automatically. I don't know you would do it with table datasheets: that's not what they are for. My test data looks accurate but it appears that new records I input to both tables do not match each other. Actually, you would not want two tables to match each other. There would not be any reason to store stuff twice. By the way, what was Table2.TelephoneNumber2 for? B Wishes Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
problem with query that has many tables with differnt relationship | DowningDevelopments | Running & Setting Up Queries | 1 | March 21st, 2005 04:21 PM |
relationship & Input for 5 tables in a single Form impossible | Rasoul Khoshravan Azar | New Users | 2 | February 26th, 2005 10:20 PM |
tables repeat in relationship window and will not stay deleted | Marcy | General Discussion | 1 | September 1st, 2004 10:40 PM |