Inserting multiple rows in one entry

[PS]

Access Gurus,

Let me first appologize for the long mail.

I asked this question on 10/21/05 (under subject: How to insert same values
that already entered in the form) but I was not ready with all the biz rules
which go in designing the application.

Here is the requirement. I've a table with 25 fields. I am listing only a
few below:
REGISTRATION_ID NUMBER
REGISTRATION_TYPE TEXT
REGISTRATION_CATEGORY TEXT
REGISTRATION_SUB_CATEGORY TEXT
REGISTRATION_PAYTYPE NUMBER
REGISTRATION_PRICE NUMBER
IS_REVERSIBLE TEXT
REVERSIBLE_REG_ID NUMBER
IS_REG_USUABLE TEXT
TRXN_SEQ_NO NUMBER
UPDATE_TS DATETIME
SHORT_DESCRIPTION TEXT
LONG_DESCRIPTION TEXT
.......

Every time a new record is added we start with Reg_Type of "A" (control is
defaulted to "A"). When "A" type record is inserted in the tabe, what I want
to do basically is change the values of some fields and insert a set of 7
more rows back into the same table immediately. The Field values will be
changed based on Reg_Type. Including Reg_Type "A", I've --> B,D,F,N,R,W,X.

This is my logic:

User enters the 1st record in the form:
1,432, "A", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "F", 0, "T", 0, "10/21/2005",
"CA OVR PAY", "OVER PAYMENT COLLECTION"

Once the 1st entry is recorded in the table, I compute the next 7 rows based
on the Reg_TYpe.

Compute for 2nd entry:

If Reg_Type = "R" Then
Increment Reg_ID by 10,000 (meaning 1,432 + 10,000 = 11,432)
Reg_Type = "R"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 11,432
IS_REG_USUABLE = "F"
(other field values stays same as in Type "A")

Insert 2nd row:
11,432, "R", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 11432, "F", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"

Compute 3rd entry:

If Reg_Type = "X" Then
Increment Reg_ID by 50,000 (meaning 1432 + 50,000 = 51,432)
Reg_Type = "X"
IS_REVERSIBLE = "T"
REVERSIBLE_REG_ID = 0
IS_REG_USUABLE = "T"
(other field values stays same as in Type "A")

Insert 3rd row:
51,432, "X", "ACCTSCOLL", "COLLOVERACCT", 0, 0, "T", 0, "T", 0,
"10/21/2005", "CA OVR PAY", "OVER PAYMENT COLLECTION"

In this way I must insert records for all the remaining Reg_Types. As you
see, everytime Reg_Type is changed I increment Reg_ID (either by 10k or 50k
or 70k ...). This means for every Reg_Type "A" entry 7 other types needs to
go into the table. The logic remains same whenever a new Registration_ID is
added. Next time around, when a new Registration_ID needs to be added check
the table and find the max(Registration_ID) for Reg_TYpe "A" and increment it
by 1. That way I increment "A" Type also meaningfully. The reason behind
for this is to make it easier for one to know what series a particular type
("A", "B", "D", "X"...etc types) fall under.

I could make the user to enter all the 8 rows. But the problem is what if
the user makes a mistake or forgets to enter a record totally. This will
cause unnecessary problems because the Registration_ID is the most key field
in the application.

Hope I explained it well what I need to do. I would really appreciate it if
you could put me in right direction as I am new to Access coding.

Thanks a million.

PS

 

[Jeff Boyce]

I believe I understand "what" you are trying to do (insert multiple rows in
the same table, based on a first row's data, with only a few differences in
the "multiple" rows).

What I don't understand is "why?" What business need are you attempting to
solve by having all the duplication of values your post implies?

Wouldn't it make for considerably fewer rows and less duplication to use a
parent-child relationship between the first (new) row and all the rest?
That way you wouldn't need to duplicate the duplicate field values.

Or do I not yet understand what the unique requirements are for your
specific situation?

 

[PS]

Jeff,

Thanks for the input.

It sounds like there is a duplication involved but actually there is not. I
just went back and checked the database table how the data is stored. Let me
simplify what I am after. When a user enters the data for one Reg_ID with
Reg_Type of "A" I want to insert 7 more rows back into the same table while
dynamically changing other field values before commit - most of the field
values are boolean (T/F). I would say, out of 25 database fields, within
each set, 18 - 20 field values will be different. And of course, each Reg_ID
set is different from each other. We have several projects going at the same
time. All projects use this table and the data differs from each other.

Hope I aswered it right.

PS

 

[Jeff Boyce]

I've re-read both your posts, but still don't understand the "why". In your
first post, you said:

In this way I must insert records for all the remaining Reg_Types

I'm trying to understand what business situation calls for multiple (?8)
additional rows of data, based on an initial entry. And if someone entered
a "Type = R" record first, would you still want to automatically add all the
other "type records"? And why?

I'm trying to understand so I can offer specific/appropriate suggestions.

 

[PS]

Like I mentioned in my first post, Type = "R" can not be entered first
because the default value for the control is set to "A". More over the
controls enabled property is set to NO - user can not enter a value. It
means the user always starts with type "A".

In the application Reg_ID and Reg_Type combination is unique for any given
transaction. The Type "A" transaction is an adjustment to the original
transaction while Type "X" is the reversal of the original. So each
condition is different.

Thanks,
PS

 

[Jeff Boyce]

Can't say it's any clearer, but maybe it doesn't have to be...

To add multiple rows, each with something slightly different, you'll have to
code several INSERT statements and run them each. You'd do this in an event
procedure.

(I still don't understand the business need for entering 8 transactions at
the start ... but it's your situation?!)