Tables Relationship Question

[Greg Jesky]

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

 

[DL]

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

 

[Tim Ferguson]

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

 

[Greg Jesky]

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

 

[BruceM]

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.

 

[Tim Ferguson]

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

 

[Greg Jesky]

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