How to add multiple records with a form/subform?

[Don Seckler]

I am building an application to track the distribution and returns of
copies of magazines.

Copies of a magazine that are unsold are returned by the retailer to
the wholesaler. They are destroyed by the wholesaler and the
wholesaler supplies the publisher (me) with a report (affidavit)about
which copies are destroyed.

The report (affidavit)from the wholesaler is dated and numbered and
says how many copies of each issue were returned that week. For
example there may be 25 copies of the December issue of Magazine A, 50
copies of the January issue of Magazine B and 12 copies of the
December issue of Magzine C returned by Wholesaler A.

For billing purposes and sales reports etc, all this needs to to be
tracked by magazine, issue, and wholesaler.

I have a table called returns with the following fields:
WholesalerName
MagazineName
IssueCode
CopiesReturned
AffidavitDate

I'd like to have a form that allows the user to input the Wholesaler,
The AffidavitDate, AffidavitNumber, and then the name of each
magazine, issue code and number of copies returned.

Basically the Wholesaler, affidavit date and affidavit number will be
the same for each set of magazine name, issue, and copies returned. I
thought the way to do this would be to use a form/subform.

Is this a correct assumption? And how should I set up my master/child
links?

Thanks in advance for your help!
AffidavitNumber

[Fletcher Arnold]

"Don Seckler" <dj*******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...

I am building an application to track the distribution and returns of
copies of magazines.

Copies of a magazine that are unsold are returned by the retailer to
the wholesaler. They are destroyed by the wholesaler and the
wholesaler supplies the publisher (me) with a report (affidavit)about
which copies are destroyed.

The report (affidavit)from the wholesaler is dated and numbered and
says how many copies of each issue were returned that week. For
example there may be 25 copies of the December issue of Magazine A, 50
copies of the January issue of Magazine B and 12 copies of the
December issue of Magzine C returned by Wholesaler A.

For billing purposes and sales reports etc, all this needs to to be
tracked by magazine, issue, and wholesaler.

I have a table called returns with the following fields:
WholesalerName
MagazineName
IssueCode
CopiesReturned
AffidavitDate

I'd like to have a form that allows the user to input the Wholesaler,
The AffidavitDate, AffidavitNumber, and then the name of each
magazine, issue code and number of copies returned.

Basically the Wholesaler, affidavit date and affidavit number will be
the same for each set of magazine name, issue, and copies returned. I
thought the way to do this would be to use a form/subform.

Is this a correct assumption? And how should I set up my master/child
links?

Thanks in advance for your help!
AffidavitNumber

Don
If you have got to the stage of forms, subforms and master/child links, then
you must have a reasonable understanding of relational databases. One basic
idea is that data within the tables is not duplicated - that is
AffidavitNumber 0123 is only recorded once - even if 4 batches of magazines
were returned under that number.
In other words, a more standard design would be to have two tables:

tblReturns - (RetID, RetDate, RetWholesalerID, RetAffidavitNo, etc)
tblReturnItems (ItmID, ItmRetID, ItmMagID, ItmQty, etc)

That way, the master field is RetID (primary key from tblReturns) and the
child key is ItmRetID (matching key in tblReturnItems)

Although, it adds an extra layer of complexity, that is the standard way to
do it. Does that help?

Fletcher

[Don Seckler]

Fletcher,

I am a semi-experienced newbie. I fiddle with access when the need
arrises, I don't do it full time, so sometimes I miss the obvious.

Sometimes it takes a whack with the old "obvious" two by four for me
to get it.

Thanks so much for your help!

Don
If you have got to the stage of forms, subforms and master/child links, then
you must have a reasonable understanding of relational databases. One basic
idea is that data within the tables is not duplicated - that is
AffidavitNumber 0123 is only recorded once - even if 4 batches of magazines
were returned under that number.
In other words, a more standard design would be to have two tables:

tblReturns - (RetID, RetDate, RetWholesalerID, RetAffidavitNo, etc)
tblReturnItems (ItmID, ItmRetID, ItmMagID, ItmQty, etc)

That way, the master field is RetID (primary key from tblReturns) and the
child key is ItmRetID (matching key in tblReturnItems)

Although, it adds an extra layer of complexity, that is the standard way to
do it. Does that help?

Fletcher

[Don Seckler]

Fletcher,

I set the tables up the way you suggested (I Think):

ReturnsTBL
--------
WholesalerName
AffidavitDate
AffidavitNumber
ReturnID (autonumber and index based on Wholesalername and
AffidavitNumber Fields)
ReturnItemsTBL
---------------
ItemID (Autonumber)
MagName
IssueCode
ReturnQty
Would it be best to put these on a form/subform and use the ReturnID
and ItemID as the master and child links? If so, is that a problem
because they are autonumber and this form is for data entry. How
would I, or do I need to get these auto numbers to generate temselves
before the records are added to their respective tables.

Thanks in advance for your help.

Don
If you have got to the stage of forms, subforms and master/child links, then
you must have a reasonable understanding of relational databases. One basic
idea is that data within the tables is not duplicated - that is
AffidavitNumber 0123 is only recorded once - even if 4 batches of magazines
were returned under that number.
In other words, a more standard design would be to have two tables:

tblReturns - (RetID, RetDate, RetWholesalerID, RetAffidavitNo, etc)
tblReturnItems (ItmID, ItmRetID, ItmMagID, ItmQty, etc)

That way, the master field is RetID (primary key from tblReturns) and the
child key is ItmRetID (matching key in tblReturnItems)

Although, it adds an extra layer of complexity, that is the standard way to
do it. Does that help?

Fletcher

[Fletcher Arnold]

"Don Seckler" <dj*******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...

Fletcher,

I set the tables up the way you suggested (I Think):

ReturnsTBL
--------
WholesalerName
AffidavitDate
AffidavitNumber
ReturnID (autonumber and index based on Wholesalername and
AffidavitNumber Fields)
ReturnItemsTBL
---------------
ItemID (Autonumber)
MagName
IssueCode
ReturnQty
Would it be best to put these on a form/subform and use the ReturnID
and ItemID as the master and child links? If so, is that a problem
because they are autonumber and this form is for data entry. How
would I, or do I need to get these auto numbers to generate temselves
before the records are added to their respective tables.

Thanks in advance for your help.

Hi Don
You seem to have missed the most important field in all this! That is,
there must be a field in the ReturnItems table to indicate which Return the
item is part of. ie, you need a field ReturnItems.ReturnID which matches
the ReturnID in the Returns table.

I didn't quite get your idea about ReturnID being 'an autonumber and index
based on Wholesalername and AffidavitNumber Fields', but it doesn't sound
the sort of thing I would do. I would have ReturnID as an autonumber and
ItemID as an autonumber and tblReturnItems.ReturnID (the matching key) being
a long integer. This allows you to easily select Tools>Relationships and
enforce referential integrity between the 2 fields and it means that
designing the forms is easy. You do not display any of these three keys -
the autonumbers are generated automatically when you create a new record,
and the long integer (tblReturnItems.ReturnID) is inserted from having the
master/child links with the form/subform.

If I were designing the tables I usually give each field in the table a 3
letter prefix - it ensures that each field is unique across the database,
avoids reserved words such as 'name' and 'date' and helps also with the
foreign keys. For example RetID is the autonumber primary key from the
returns table, ItmID is the autonumber from the items table, but ItmRetID
tells me that the field lives in the Items table, but is actually the
ReturnID. If this results in somewhat geeky names, it really doesn't matter
as these are never displayed to the user - tblContacts.ConName might be
simply labelled 'name' on the form, and I would not advise having a field
called 'name'. Just a thought.
Fletcher