Re: Inserting multiple rows in one entry

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • f0dders fabulous folly.
    ... its target audience that the mistakes being sold by f0dder are not ... Following is the test program that proves this very clearly. ... proof that prototype duplication of an API ... IAT Entry ...
    (alt.lang.asm)
  • Simplified Data Entry
    ... duplicated entries and try to prevent operator entry errors. ... This data is entered weekly at the moment to avoid duplication but I ... Clients may have more than one a/c but the weekly amount may change but ... provided the a/c is already present in a previous ...
    (microsoft.public.excel.misc)
  • Re: Query to duplicate last entry
    ... If you look at the results you want, you are duplication names, IDs ... Applications ... Your table looks to be incorrect. ... I need to duplicate the last entry in a table. ...
    (microsoft.public.access.queries)
  • Re: Inserting multiple rows in one entry
    ... It sounds like there is a duplication involved but actually there is not. ... just went back and checked the database table how the data is stored. ... >> Access Gurus, ... >> Compute for 2nd entry: ...
    (microsoft.public.access.formscoding)
  • Deleting multiple entrys
    ... How can I delete multiple rows that have the same entry? ... Example if I filter ... by a certain zip code can I delete all without deleting other info? ...
    (microsoft.public.excel.newusers)