Multiple record creation

R

Rpatton

Problem: At the time of creating a master record i need to add child records
using the same key (ID) to 3 other tables. They are skeleton records nneded
for further processing.

Can you help by telling me the best way to do this. Do i use an append
query, if so how or should i use sub forms?

Many thanks for your help in advance,

Dick
 
J

John Vinson

Problem: At the time of creating a master record i need to add child records
using the same key (ID) to 3 other tables. They are skeleton records nneded
for further processing.
Click to expand...

Could you explain what "further processing"? In my experience such
"placeholder" records are NEVER necessary, and very often are a
positive menace. They have a nasty way of never getting filled in! If
you're creating queries joining the master record to the child tables,
you can use a Left Outer Join query to return data even when there is
no child record; this is (IMO) better than creating a dummy record.
Can you help by telling me the best way to do this. Do i use an append
query, if so how or should i use sub forms?
Click to expand...

Append queries (three of them) would be preferred if you really must
do this. If you use Subforms, the child records will be created only
when data is entered into the subform, not automagically when the main
form record is created; in practice this is normally just what one
would do.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
R

Rpatton

Hi John,

I have been playing with the sub-form approach, but can not seem to get it
to work. the sub form will not allow addition of records. It's key should
be the same as the master record.

The actual problem is as follows. There are 4 tables in this Boy Scout data
base. Each keeps particular information. The first (Roster) keeps Scout ID
(the KEY) and all personal data. The second tracks summer camp payments (key
Scout ID) the third tracks Dues payments (key ID) and the forth tracks Scout
Accounts (Key Scout ID), this table is a many to one relationshp with the
master. Each table must be initialized when the master record is creted.
There are other tables which contain Patrol name, CSZ, and System parameters
these are no problem.

The data which starts the "dummy record" is valid information and will
change when needed. There are reports and queries to tell us when a boy has
not paid his dues, for example.

Hmm... perhaps i'm making this more coplicated than it needs to be, maybe i
shoul put records 2 and 3 in the master table. On the other hand, if we
decide to track multiple years, that would be a problem.

Any suggestions??

Dick
 
J

John Vinson

Hi John,

I have been playing with the sub-form approach, but can not seem to get it
to work. the sub form will not allow addition of records. It's key should
be the same as the master record.

The actual problem is as follows. There are 4 tables in this Boy Scout data
base. Each keeps particular information. The first (Roster) keeps Scout ID
(the KEY) and all personal data. The second tracks summer camp payments (key
Scout ID) the third tracks Dues payments (key ID) and the forth tracks Scout
Accounts (Key Scout ID), this table is a many to one relationshp with the
master. Each table must be initialized when the master record is creted.
There are other tables which contain Patrol name, CSZ, and System parameters
these are no problem.

The data which starts the "dummy record" is valid information and will
change when needed. There are reports and queries to tell us when a boy has
not paid his dues, for example.

Hmm... perhaps i'm making this more coplicated than it needs to be, maybe i
shoul put records 2 and 3 in the master table. On the other hand, if we
decide to track multiple years, that would be a problem.

Any suggestions??
Click to expand...

Yes. Normalize your data properly!

The Roster should have only information about the Scout as a person -
lastname, firstname, contact information, etc.

The summer camp payments and dues payments table should contain the
ScoutID and payment information (amount, payment date, perhaps an
indication of what the payment is for), and nothing else. These should
be related many-to-one to Roster, since presumably each scout will
make multiple payments. I do not know what the purpose of the "Scout
Accounts" table might be.

It is perfectly possible to create a Query to indicate that a Scout
has made no payments for a particular event, or for that matter has
made no payments at all; it is *NOT* necesary (in fact it gets in the
way) to create a dummy record for this purpose.

I have *no* trace of an idea what your last paragraph means. The
master table should - as I said - contain no information about
payments, just about the scout as a person.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top