Multiply Function - Repeating Fields

[keshalyi]

I don't need any help (for once), but I just found the coolest niche, in terms of Relational Design, that I thought I'd let y'all know about.

My old DB, the one that I inherited, had some major problems, and one of these is: repeating fields. Bad ones. To put it shortly, I had a repeating field of publication dates, and a repeating field of publications, to determine what date and pub instance to export classified ads into.

At any rate, without getting into the nitty gritty, sufficeth to say, I wanted to export these out, into an Ad Instance DB, that would have one record for each combination of pub and pub date. In relational terms (I think), I needed to multiply a table of Ad Number and Pub by a table of Ad Number and Pub Date.

Well, I tried some scripting solutions, where I exported into a pub and a pub date table, and these worked, but they were slow - the test DB had about 250 ads, which translated to 450 entries in the pub table and 700 in the date Table, and it took about 10 minutes on my Powerbook G3 all said and done.

Luckily, this is where inspiration flashed -

I found the easiest way to do a multiply with two repeating fields! Here's what I did:

I started with the original ads table, and exported Ad Number and Pub Dates, into a bridge table, creating a new record for each repetition - this made a date table. Then, I did another export, into the SAME TABLE, by having the database match ad numbers, and import Publications. What this gave me was a record for each pub date/Ad Number combination, with a repeating field of Pubs for that AdNumber. Then, I just exported into the AdInstances Table, seperating repeating fields into seperate records! WOW! It took about 20 seconds.

Wonder if anyone else has found an easy way to do table multiplies? With actual seperate data tables for instance?