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
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
is it possible that for every record to have multiple entry at given fields
covering more than two "rows"? |
#2
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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 "Abel" wrote in message ... is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
#3
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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) "Abel" wrote: is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
#4
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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 -- -Larry- -- "Abel" wrote in message ... is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
#5
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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... "Larry Daugherty" wrote: 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 -- -Larry- -- "Abel" wrote in message ... is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
#6
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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. |
#7
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
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 -- -Larry- -- "Abel" wrote in message ... 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... "Larry Daugherty" wrote: 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 -- -Larry- -- "Abel" wrote in message ... is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
#8
|
|||
|
|||
MS ACCESS: entering multiple data on a field per record
Thanks a lot Mr Larry, you've been a great help...
"Larry Daugherty" wrote: 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 -- -Larry- -- "Abel" wrote in message ... 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... "Larry Daugherty" wrote: 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 -- -Larry- -- "Abel" wrote in message ... is it possible that for every record to have multiple entry at given fields covering more than two "rows"? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concerned about different Access versions--need serious help! | NightowlMania | General Discussion | 22 | January 16th, 2006 01:34 PM |
The "Right" web hosting for data access pages?? | Ron Ehrlich | General Discussion | 9 | May 6th, 2005 05:49 AM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |