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 Design
This is similar to an earlier thread about Teams and Games. I have a table
for Firms which contains Subcontractors, Suppliers, Owners, Designers, Insurance Providers, etc. A Firm Type Code field identifies each firm's type of work. The primary key for the Firms table is an autonumber field. I'm trying to add an Insurance Certificate feature to my database, so I've created a table for Insurance Certificates. Eventually there should be one Insurance Certificate record for each Subcontractor. Each record will also have various Insurance Providers and Insurance Carriers. I'm beginning to think I can't just use Combo Boxes to reference all these firms from my Firms table. Normalization notwithstanding, should I break my Firms table up into separate tables for each type of firm? Thanks |
#2
|
|||
|
|||
Tables Design
There are advantages to having all the firms in the one table. Typical
example is if you need another table with a foreign key (e.g. for appointments or payments, where the record could relate to any of these types of firm.) Create a query to use as the RowSource for your combo, e.g.: SELECT FirmID, FirmName FROM Firm WHERE FirmTypeID = 'subcontractor' ORDER BY FirmName, FirmID; Save it, and use it as the RowSource for the combos throughout your application where you want a subcontractor selected. Regarding the insurance certificates, one contractor could have many certificates over time (as each expires)? If so, it's a one-to-many relation (unless you only keep the current certificate.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "oldblindpew" wrote in message ... This is similar to an earlier thread about Teams and Games. I have a table for Firms which contains Subcontractors, Suppliers, Owners, Designers, Insurance Providers, etc. A Firm Type Code field identifies each firm's type of work. The primary key for the Firms table is an autonumber field. I'm trying to add an Insurance Certificate feature to my database, so I've created a table for Insurance Certificates. Eventually there should be one Insurance Certificate record for each Subcontractor. Each record will also have various Insurance Providers and Insurance Carriers. I'm beginning to think I can't just use Combo Boxes to reference all these firms from my Firms table. Normalization notwithstanding, should I break my Firms table up into separate tables for each type of firm? Thanks |
#3
|
|||
|
|||
Tables Design
Yes, I'm only concerned about the current certificate. I don't care about
past certs. I do have a query for RowSource, but I'm thinking there's a bigger problem. The problem is not with identifying the insured firm (Subcontractor). The problem is with other firms in the same certificate record, e.g. the Certifcate Provider and the Insurance Carrier. Can I have a keys for the Sub and the Provider and the Carrier all in the same record and all pointing back to the Firms table? Also, I'm not really interested in having the user see the autonumber value for these firms, but I can't figure out how to display the firm name on the form rather than the key value. I'm sorry to ask such basic questions; never have I had to struggle so hard with a programming language to get it to do such basic operations. Thanks "Allen Browne" wrote: There are advantages to having all the firms in the one table. Typical example is if you need another table with a foreign key (e.g. for appointments or payments, where the record could relate to any of these types of firm.) Create a query to use as the RowSource for your combo, e.g.: SELECT FirmID, FirmName FROM Firm WHERE FirmTypeID = 'subcontractor' ORDER BY FirmName, FirmID; Save it, and use it as the RowSource for the combos throughout your application where you want a subcontractor selected. Regarding the insurance certificates, one contractor could have many certificates over time (as each expires)? If so, it's a one-to-many relation (unless you only keep the current certificate.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "oldblindpew" wrote in message ... This is similar to an earlier thread about Teams and Games. I have a table for Firms which contains Subcontractors, Suppliers, Owners, Designers, Insurance Providers, etc. A Firm Type Code field identifies each firm's type of work. The primary key for the Firms table is an autonumber field. I'm trying to add an Insurance Certificate feature to my database, so I've created a table for Insurance Certificates. Eventually there should be one Insurance Certificate record for each Subcontractor. Each record will also have various Insurance Providers and Insurance Carriers. I'm beginning to think I can't just use Combo Boxes to reference all these firms from my Firms table. Normalization notwithstanding, should I break my Firms table up into separate tables for each type of firm? Thanks |
#4
|
|||
|
|||
Tables Design
I think I've got things working. My Combo Box had a typo in the RowSource
query and the wrong Bound Column. One tiny step forward; at this rate I'll be retired before I get this application done. "oldblindpew" wrote: Yes, I'm only concerned about the current certificate. I don't care about past certs. I do have a query for RowSource, but I'm thinking there's a bigger problem. The problem is not with identifying the insured firm (Subcontractor). The problem is with other firms in the same certificate record, e.g. the Certifcate Provider and the Insurance Carrier. Can I have a keys for the Sub and the Provider and the Carrier all in the same record and all pointing back to the Firms table? Also, I'm not really interested in having the user see the autonumber value for these firms, but I can't figure out how to display the firm name on the form rather than the key value. I'm sorry to ask such basic questions; never have I had to struggle so hard with a programming language to get it to do such basic operations. Thanks "Allen Browne" wrote: There are advantages to having all the firms in the one table. Typical example is if you need another table with a foreign key (e.g. for appointments or payments, where the record could relate to any of these types of firm.) Create a query to use as the RowSource for your combo, e.g.: SELECT FirmID, FirmName FROM Firm WHERE FirmTypeID = 'subcontractor' ORDER BY FirmName, FirmID; Save it, and use it as the RowSource for the combos throughout your application where you want a subcontractor selected. Regarding the insurance certificates, one contractor could have many certificates over time (as each expires)? If so, it's a one-to-many relation (unless you only keep the current certificate.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "oldblindpew" wrote in message ... This is similar to an earlier thread about Teams and Games. I have a table for Firms which contains Subcontractors, Suppliers, Owners, Designers, Insurance Providers, etc. A Firm Type Code field identifies each firm's type of work. The primary key for the Firms table is an autonumber field. I'm trying to add an Insurance Certificate feature to my database, so I've created a table for Insurance Certificates. Eventually there should be one Insurance Certificate record for each Subcontractor. Each record will also have various Insurance Providers and Insurance Carriers. I'm beginning to think I can't just use Combo Boxes to reference all these firms from my Firms table. Normalization notwithstanding, should I break my Firms table up into separate tables for each type of firm? Thanks |
Thread Tools | |
Display Modes | |
|
|