Re: Inserting multiple rows in one entry
- From: "PS" <PS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 28 Oct 2005 07:53:07 -0700
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" wrote:
> 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?
>
> --
> Regards
>
> Jeff Boyce
> <Office/Access MVP>
>
> "PS" <PS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:2A5CADB7-3BB9-41BB-86F8-F3A760DD789D@xxxxxxxxxxxxxxxx
> > 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
>
>
.
- Follow-Ups:
- Re: Inserting multiple rows in one entry
- From: Jeff Boyce
- Re: Inserting multiple rows in one entry
- References:
- Inserting multiple rows in one entry
- From: PS
- Re: Inserting multiple rows in one entry
- From: Jeff Boyce
- Inserting multiple rows in one entry
- Prev by Date: Add a record from a bound subform to an unbound subform
- Next by Date: Re: Display list of files in a folder on a hard drive in a form
- Previous by thread: Re: Inserting multiple rows in one entry
- Next by thread: Re: Inserting multiple rows in one entry
- Index(es):
Relevant Pages
|
