G
Guest
is it possible that for every record to have multiple entry at given fields
covering more than two "rows"?
covering more than two "rows"?
E
Ed Robichaud
You seem to have a misunderstanding of basic database structure. In
table/datasheet view, each row is a single record. Each of those records
can have up to 255 fields.
What are you trying to do?
-Ed
table/datasheet view, each row is a single record. Each of those records
can have up to 255 fields.
What are you trying to do?
-Ed
G
Guest
No, a record is one row. A field contains an atomic piece of data. If you
need more than one value in a field for a record, this is a candiate for a
related child table. For example, if you have a customer that has more than
one telephone number, you can only put one number in the Phone_Number fields,
so you need a way to handle this. You could create a table named
tblCustomerPhones that would contain the following fields:
CUST_KEY - The value of the primary key for the customers record.
PHONE_NUMBER - A unique phone number for the customer
NUMBER_TYPE - (fax, cell, office, home, etc)
need more than one value in a field for a record, this is a candiate for a
related child table. For example, if you have a customer that has more than
one telephone number, you can only put one number in the Phone_Number fields,
so you need a way to handle this. You could create a table named
tblCustomerPhones that would contain the following fields:
CUST_KEY - The value of the primary key for the customers record.
PHONE_NUMBER - A unique phone number for the customer
NUMBER_TYPE - (fax, cell, office, home, etc)
L
Larry Daugherty
An Access implementation of just what you describe was shown to me by
one of its authors who proudly explained that they'd made Access
emulate "Mumps" or "M".
Sure enough, in a record there was data, separator, data, separator
..... Things got a little frosty when I told them I wouldn't touch
their code and that I didn't want to see any of the special code
they'd had to do to make it work.
My question then and now is this: Why not a child table with a row
for each piece of the data? That's called a one-to-many relation. As
in Relational Database. Each parent record's data is unambiguously
related to it. Access is pretty good at it.
HTH
one of its authors who proudly explained that they'd made Access
emulate "Mumps" or "M".
Sure enough, in a record there was data, separator, data, separator
..... Things got a little frosty when I told them I wouldn't touch
their code and that I didn't want to see any of the special code
they'd had to do to make it work.
My question then and now is this: Why not a child table with a row
for each piece of the data? That's called a one-to-many relation. As
in Relational Database. Each parent record's data is unambiguously
related to it. Access is pretty good at it.
HTH
G
Guest
i'm new with MS Access and i can't find time to play around with the
program...how can i create a child table?
thanks for those who replied...
program...how can i create a child table?
thanks for those who replied...
R
Ron2006
A child table is basically just another table BUT with one additional
field. An example may help
tblContact
ID - the autonumber field that Access always asks if you
want to create.
Typically you should say yes.
Could also call this ContactID
ContactLastName
contactFirstName
tblPhone A table of phone numbers
ID - The autonumber field that Access always asks if you
want to create
Could also call this PhoneID
ContacID - this is the ID number for the contact that this phone
belongs to
This should be a longInteger field
PhoneType - Cell, home, work, etc
PhoneNumber
Go to the Relations button and press that.
Right click in the new window and go to Add Tables
drag and drop both of the above tables onto the window.
Drag the ID field of the tblContact and drop it on the ContactID
field of the Phone table.
You now have a parent child relationship between two tables
If you create a form based on the Contact table and drop a subform on
it then access will ask if you want to establish a parent child
relationship. Say yes and make sure that
says that the ID of the Contact table is to match the ContactID of the
phone table.
You have now created a parent child for relationship and if you add a
phone number Access will automatically make sure that the ContactID
field of the phone table is loaded with the correct ID field from the
Contact Table.
field. An example may help
tblContact
ID - the autonumber field that Access always asks if you
want to create.
Typically you should say yes.
Could also call this ContactID
ContactLastName
contactFirstName
tblPhone A table of phone numbers
ID - The autonumber field that Access always asks if you
want to create
Could also call this PhoneID
ContacID - this is the ID number for the contact that this phone
belongs to
This should be a longInteger field
PhoneType - Cell, home, work, etc
PhoneNumber
Go to the Relations button and press that.
Right click in the new window and go to Add Tables
drag and drop both of the above tables onto the window.
Drag the ID field of the tblContact and drop it on the ContactID
field of the Phone table.
You now have a parent child relationship between two tables
If you create a form based on the Contact table and drop a subform on
it then access will ask if you want to establish a parent child
relationship. Say yes and make sure that
says that the ID of the Contact table is to match the ContactID of the
phone table.
You have now created a parent child for relationship and if you add a
phone number Access will automatically make sure that the ContactID
field of the phone table is loaded with the correct ID field from the
Contact Table.
L
Larry Daugherty
My apologies. Child table is an incorrect term I use for the many
side of a one-to-many relationship.
What would apply in your case is to remove that field with the
repeating info, create a new table probably named for the field just
removed.
in that new table, usually the Primary Key will be the first field and
the Primary Key of the Parent table (the one side of the one-to-many
relationship we're about to create) will be the second field with the
same name as the parent table PK and type is number/long integer. If
you don't yet have primary key's defined then I recommend that you
create them as an Autonumber datatype in the first field of each
table. Usually that Autonumber surrogate key will be named
TableNameID. i.e. if the table name is tblClient then the primary key
field would be named ClientID.
When that is done, open the Relationships window, show the two tables
you are interested in, click and drag a line from tblClient.ClientID
to tblNew.ClientID. Choose one to many as the relationship and set
Referential Integrity on. Enable Cascading Deletes.
Sorry that no one else picked this one up for you. I'm absent from
the newsgroups more than present anymore.
HTH
side of a one-to-many relationship.
What would apply in your case is to remove that field with the
repeating info, create a new table probably named for the field just
removed.
in that new table, usually the Primary Key will be the first field and
the Primary Key of the Parent table (the one side of the one-to-many
relationship we're about to create) will be the second field with the
same name as the parent table PK and type is number/long integer. If
you don't yet have primary key's defined then I recommend that you
create them as an Autonumber datatype in the first field of each
table. Usually that Autonumber surrogate key will be named
TableNameID. i.e. if the table name is tblClient then the primary key
field would be named ClientID.
When that is done, open the Relationships window, show the two tables
you are interested in, click and drag a line from tblClient.ClientID
to tblNew.ClientID. Choose one to many as the relationship and set
Referential Integrity on. Enable Cascading Deletes.
Sorry that no one else picked this one up for you. I'm absent from
the newsgroups more than present anymore.
HTH
G
Guest
Thanks a lot Mr Larry, you've been a great help... 