Automatically Create a number of records

[Guest]

I am wondering how to create records automatically. I have a main form and a
subform. On the main form I have a field called Total number tested. The
value I put in that box I would like it to automatically create that number
of records, with default information in them. Can you help me figure this
out? Macro? Code? Do you have any examples?

Thanks for advice.

 

[Guest]

Hi

I am a little confused as to why you would want to create lots (or even a
few) records with exactly the same information in them (apart from the
primary field)

 

[Guest]

Because it is information that is normally the same and would save a lot of
time for the amount of tests that are done. Does that make sense?

 

[Guest]

Sorry but that goes against the whole idea of well designed relational
databases. Each record should contain unique information and you should
(really should) try and avoid duplication.


I would lokk at your tables and try to work out why you have a situation
where you feel you need many records with non unique data.

You can always set the default value of a field so that they are as you want
but even I would suggest that to have default values for "all field" means
there is a problem with the database.

I may be wrong about this and someone else will have some more ideas

 

[Guest]

We do a lot of negative testing and need to have results at the sample level
and we test many thousands of samples. does that help you understand why I am
trying to do this?

 

[Joseph Meehan]

We do a lot of negative testing and need to have results at the
sample level and we test many thousands of samples. does that help
you understand why I am trying to do this?

That explains your problem but I don't think it explains why you can't
or should not redesign your table(s) to normalize the data. In fact it would
appear you have an even greater need for normalization that usual.

Properly normalized data design will help you do what you want in the
long run and be less trouble. It just seems counter intuitive if you are
use to spreadsheets.

 

[Guest]

Hi

Sorry for not back to you (I had to go out)

After you have read Joseph's reply if you "really" want to add loads of
records and this does not go against any validation you may have in your
tables (which looking at your post you should have and this is another
reason not to do what you want) you could use something like this. I assum
you have a mouse with a roller (to make it easier).

Private Sub Form_Current()
If (Eval("[Forms]![FormName]![FieldXXX] Is Null")) Then
Forms!FormName!FieldXXX = 0
End If
End Sub

FieldXXX is a new field in your table (you will need to insert this) that
does nothing other than contain the 0. On your form set the records slector
to =Yes and simply rotate the mouse until you have as many new duplicate
records as you want. The addition of the 0 will force the record primary
field to advance one as the record is created. It's up to you.

Have fun

 

[Guest]

It might be helpful to consider what is actually involved in determining
whether your subform's underlying table is properly normalized or not. For
the sake of simplicity lets for the moment look at normalization to Third
Normal Form. In essence this requires that all non-key columns are
functionally dependent solely on the primary key of the table (John Vinson's
'the key, the whole key and nothing but the key so help me Codd!' is a useful
way of remembering this).

It follows from this that the duplication of values in a table is only
prohibited where these are in a column or columns (as a whole not
individually) which are either the primary key or a candidate key of the
table. So lets assume that in your case the SampleTests table on which your
subform is based includes columns SampleID, TestDate, TestType and Result and
each test type of a sample on a particular day must be different. Of these
columns SampleID, TestDate, TestType constitute a candidate key and would
probably be made the table's primary key. The only non-key column in this
simple example therefore is Result and there is no reason why for each value
of the primary key this might not have the same value, e.g. 'Negative', i.e.
all tests of that sample on that date, but each of a different TestType,
returned a negative result. This column is obviously functionally dependent
on the whole of the key so the table is normalized to 3NF.

Lets take a slightly different scenario and assume that multiple tests of
the same sample of the same TestType can legitimately be carried out on the
same TestDate. SampleID, TestDate, TestType cannot now be a candidate key.
Nor can all four columns as two of these tests of the same type might produce
the same result. There is no way therefore in which the table can be
normalized to 3NF. It would be necessary to introduce another column into
the table NumberOfTests, so that SampleID, TestDate, TestType, Result becomes
a candidate key. If three samples of the same TestType return a negative
result therefore, there would be one row for those three tests with a
'negative' result value and identical SampleID, TestDate, TestType values.
The NumberOfTests column is obviously functionally dependent on the whole of
the key so the table is normalized to 3NF

In either case you can't really do what you want. In scenario 1, while you
could insert a default value of 'Negative' for all results you cannot insert
a default value of 'Full' say for TestType because this is part of the key
and, as SampleID and TestDate must be the same in each row of this subset of
the table, to put 'Full' in TestType for each row would violate the key.
Also you can't simply leave it Null as n part of a primary key can be Null.

For scenario 2 you can't insert rows automatically as you even though you
know the total number of tests for this sample/testdate ther is no way of
knowing how many there are per testtype/result until the data is enetered
into the SampleTests table underlying the subform.

While the above will doubtless not be anything like analogous to your real
world scenario I hope that it will at least serve to illustrate the
underlying principles involved, and to show why what appears to be a simple
and reasonable requirement at first site is in fact far from straightforward.
What it all boils down to is that a well designed database is constrained in
such a way as to preserve the integrity of the data, which is one of the
strong points of the relational model, whereas to insert multiple 'default'
rows almost invariably will involve violating those constraints, so can only
be done if the need to maintain data integrity is sacrificed. Its not being
able to have your cake and eat it, I'm afraid.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

John Vinson's
'the key, the whole key and nothing but the key so help me Codd!'

I'm sure John Vinson wouldn't seek to take the credit for that one.
See:

http://groups.google.com/group/comp.databases/browse_frm/thread/
cc9655dfa2a06219/e8fbd6b5862ac87d?#e8fbd6b5862ac87d

"I think that Chris Date used it first in a magazine column or speech"

Jamie.

--

 

[Jamie Collins]

I am wondering how to create records automatically. I have a main form and a
subform. On the main form I have a field called Total number tested. The
value I put in that box I would like it to automatically create that number
of records, with default information in them. Can you help me figure this
out?

Here's a general approach for ideas:

CREATE TABLE Things (
thing_ID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE ThingOccurrences (
thing_ID INTEGER NOT NULL REFERENCES Things (thing_ID),
occurrence_number INTEGER NOT NULL,
UNIQUE (thing_ID, occurrence_number),
occurrence_result CHAR(8) DEFAULT 'Negative' NOT NULL,
CHECK (occurrence_result IN ('Negative', 'Positive'))
)
;
CREATE TABLE Sequence (
seq INTEGER NOT NULL UNIQUE
)
;

Populate the Sequence table with incrementing integers between 1 and
N.

CREATE PROCEDURE AddThingOccurrences (
arg_thing_ID INTEGER,
occurrences_total_amount INTEGER
)
AS
INSERT INTO ThingOccurrences (thing_ID, occurrence_number)
SELECT T1.thing_ID, S1.seq
FROM Things AS T1, Sequence AS S1
WHERE T1.thing_ID = arg_thing_ID
AND S1.seq BETWEEN 1 AND occurrences_total_amount
AND NOT EXISTS (
SELECT *
FROM ThingOccurrences AS O1
WHERE T1.thing_ID = O1.thing_ID
AND O1.occurrence_number = S1.seq)
;

Jamie.

--