Combining Multiple Records

[Dumas.DED]

Hey there,

I'm sure most access users balk at the very notion of combining multiple records into a single record, so let me quickly clarify my scenario: I have a mountain of exquisitely maligned data. Said mountain comprises several item records with one royalty rate and one territory code per record. This means that royalty rates associated with one item are spread out between multiple (up to twelve) different records.

I would much prefer to have different territory rates stored in different fields within one record rather than spread throughout multiple records. However I am not aware of a quick and easy way to combine all this information accordingly.

Any ideas?

Thanks,

Darrell

 

[pbaldy]

It sounds like your data is stored correctly. You don't want fields for each possible rate; you'll solve one problem but likely create others. The type of thing you're describing is appropriate for a spreadsheet, not a database. For display in a report or something you can do this:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Dumas.DED]

It sounds like your data is stored correctly. You don't want fields for each possible rate; you'll solve one problem but likely create others.

That may be how it sounds, but I've already gotten it to work perfectly this way. On the other hand, the database I'm attempting to replace - which utilized an individual record for each rate - has been extremely error-prone as long as we've used it. It was also very poorly constructed (by someone else), but either way I'll want to know how this creates more problems than it solves before I go back and undo everything I've already done.

In the interest of staying focused, regardless of what I do or do not want: can it be done?

 

[PNGBill]

A maketable query should give you one record showing item and a field for each occurrence of the royaltyrate. You may need to rename some or all of the query fields but when you run the query you will get one record in the new table with royaltyrate1, royaltyrate2 etc.
If you are sure of the max number of royalty rates then the query will have that number of fields.

The new table will have null values where there is no royalty rate and you will have a table with a lot of fields that may later get confusing.
How will you handle 50 royalty rates if such occurred ?

 

[PNGBill]

Further to my post above we have a LoansTable that holds fields for the agreed repayments (20 fields).
A loan of 3 repayments still uses the 20 fields. 17 are null

In Hindsight we should have put this info into our TblTrans so it is just as "set in concrete" but each repayment is one record of a table with a few fields rather then expanding our TblLoans to have an additional 20 fields for each loan record.
Each record in TblTrans has a LoanID and Date so we can produce the data by query as and when required.