Tables structure Problem - Please help me!!!!!

[spooono]

I believe I may have structured my tables wrong.

I have 4 tables

tblcarrier - carrier Name, carrier comments, datestamp
tbllob - lob, datestamp
tblattachmentManager - attachmentname, attachmentlink, attachmentdatestamp
tbldatacenter- listbox to carrier, listbox to lob, attachment1-8 (8
fields)list box to attachment manager.

I need to accomplish 2 things

1) create an attachment1-8 name field after each attachment and poplulated
it from a drop down (on a form - thus populate 2 fileds with 1 listbox)

2) find a way to display the name through linking - problem is many of the
fields (8) relate to 1 table and not sure how to link

3) restructure this so I will be able to have 8 attachment but not have the
user enter redundant info

user needs to enter lob, carrier, and 1 to 8 attachments in the form

forms looks like this:

Carrier: <listbox>
LOB:<listbox>
attachment1:<listbox>
attachment2:<listbox>
attachment3:<listbox>
attachment4:<listbox>
attachment5:<listbox>
attachment6:<listbox>
attachment7:<listbox>
attachment8:<listbox>

<submit>

Please Help me!!!

 

[John Vinson]

I believe I may have structured my tables wrong.

I have 4 tables

tblcarrier - carrier Name, carrier comments, datestamp
tbllob - lob, datestamp
tblattachmentManager - attachmentname, attachmentlink, attachmentdatestamp
tbldatacenter- listbox to carrier, listbox to lob, attachment1-8 (8
fields)list box to attachment manager.

Yes. You have structured your tables wrong. Whenever you have fields
named anything1 to anything8, you are embedding a one to many
relationship in a single record - ALWAYS a bad idea!

In a relational database, when you have a one to many relationship,
*USE ANOTHER TABLE* to model that relationship.

Another problem is that you are saying that tbldatacenter contains a
listbox for this, a listbox for that. IT DOESN'T. A table contains
*data values*; a listbox is not a data value, it's a display tool! I
would recommend NEVER using combo boxes or listboxes in Tables. Table
datasheets should contain the data, and only the data; use Forms (with
listboxes or combo boxes of course) to enter the data, but *don't* put
controls into tables. They just slow things down and conceal the
actual structure and content of your data.

I need to accomplish 2 things

1) create an attachment1-8 name field after each attachment and poplulated
it from a drop down (on a form - thus populate 2 fileds with 1 listbox)

Again: NO. You do NOT want to store the attachment name in
tblDatacenter, or in any table other than tblAttachmentManager. Just
store the unique ID.

I have no idea what a "lob" might be but I'd suggest the following
tables: * indicates that the field is the Primary Key of that table.

tblCarrier
*CarrierID Autonumber ' names change and don't make good keys
CarrierName Text
Comments Memo
Datestamp Date/Time (default Now())

tblLob
*Lob Text (or maybe use an Autonumber if Lob is changeable or large)

tblAttachments
*AttachmentLink <I guess, not sure what you have in it>
AttachmentName Text
Datestamp Date/Time

tblDataCenter
*DataCenterID Autonumber
CarrierID Long Integer <link to tblCarrier>
Lob <whatever datatype a lob is>
SeqNo Integer <1 to 8>
AttachmentLink <same datatype as tblAttachments>

Rather than using 8 *FIELDS* for the links, use 8 *RECORDS* on a
Subform. If the mainform is based on Carriers, use the CarrierID as
the Master/Child Link Field. If the subform is Continuous, you'll be
able to enter as many attachments as you wish.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps