Creating unique record from record and field data

[arthurpenske]

I currently have a table where each record is a different contract, and the fields are various parameters of that contract, like this:

Contract Name | Flat Rate ($$) | Monthly Fee ($$) | Annual Fee ($$)

I think the data will be more compatible with other tables in my database if there is a unique record for each part of the contract, organized some way like this:

Contract Name | Parameter (Flat rate, monthly fee, etc.) | $$ amount




Can anyone think of an easy way to do this? Or any other suggestions? The data is in this format because I'd like to be able to enter each contract into the table from a form, so if you can think of a better way to input the data that would be useful as well.

[rpeare]

You wouldn't use the contract name you'd use a primary key field

for instance

tblContracts
ContractID ContractName -----> other fields

tblFees
FeeID FeeName
1     Annual
2     Flat
... etc

tblContractFees
CTID ContractID FeeID FeeAmt

So you'd set up a form for your contracts, and in that form you'd have a subform that handled the contract fees (i'm assuming you can have multiple fees for each contract) If you link the two tables on the contractID the only thing you'll have to enter on the subform is the fee ID (if you use a combo box you can just type in the name of the fee and have it store the ID) and the fee amount.

[arthurpenske]

Thank you- I think that should work for new data entry. Do you have any suggestions for converting old data in the table I gave above?

[rpeare]

Well, you'd have to create the tables to properly normalize (I do not know your data structure, table names, field names, anything so I can't give you anything other than general instructions) Then create an APPEND query that will take your existing data and correctly update the normalized table. In your case you are only reporting three different types of rates (Flat, Monthly, Annual) so you'd have to do three different appends the non zero amounts to your table.