Insert a blank record every occurence of . . . .

[Peter8988]

I work with various sorted address databases. I have a field that contains a bundle id for addresses that belong to a specific set. At the beginning of a group I have an indicator in that bunlde id field (i.e. **2/2**). What I want to do is create (insert) a blank record right before the line that contains the bundle id. I can't have the database out of order. Some of the databases contain 70,000 records requiring about 550 blank records inserted before each bundle set.

Any ideas?

Thanks

Peter

 

[JimHorton]

Peter,
I'm not sure what exact values the Bundle ID can be, so it's best to start with a new field. This field would be what you order the table by when you view, export, or report on this table. The basic pseudo-logic would be to gather all available Bundle ID's in the table at the given time. This would be done with a simple aggregate (Group By) query. Then, lets say you have 100 different bundle id's. You'd then make a set of 100 of (NewFieldID we'll call it) new ID's, say, 10,20,30....1000. Assign the corresponding NewFieldID to each record with the corresponding BundleID. Now, you can insert 100 records (One for each unique NewField/BundleID), all fields blank, except NewFieldID, which would be NewFieldID - 1. So when ordered by this new field, you'd have:
NewFieldID BundleID OtherFields
9 Blank Blank
10 **2/2** Some Values
10 **2/2** Some Values
19 Blank Blank
20 **2/3** Some Values
20 **2/3** Some Values
29 Blank Blank
30 **3/3** Some Values
.....etc, etc.
Now, when viewing, exporting, or reporting, you order by the new id and hide it.
--Jim

 

[JerryDennison]

Why would you want to populate your table with empty records? This is very innefficient and completely uneccessary. If you wish to add a space between two records based on specific groupings, this can be done very easily without resorting to empty records. This is especially easy in reports but can be done even in queries or forms (with a little bit of coding).