Fill in table info based on # entered into specific field

[equuscycle]

I am setting up a database to track my book purchases and sales, etc. I am
not new to Access, but it's been so long since I've used it much that I've
forgotten a lot of the terms, so please bear with me! I often make lot
purchases of 50 different books. What I believe I should do is set up one
table, say "Purchases" that contains PurchaseID, LotID (which would be the #
of books purchased), PurchaseDate, Cost, and Tax. I believe I should have a
separate table that would track my actual individual books, say "Items" with
ItemID, PurchaseID (as a foreign key), Title, CategoryID (type of book), and
UnitCost (a calculated field based on cost+tax/lotID).

First of all, is my assumption correct?

If so, I would like to create a form for Purchases with a subform for Items
and be automatically prompted to enter x amount of "Items" where x = LotID.
Is this possible? If so, how would I do this?

Thanks in advance for anyone willing to help me out!
Tina

 

[John Vinson]

I am setting up a database to track my book purchases and sales, etc. I am
not new to Access, but it's been so long since I've used it much that I've
forgotten a lot of the terms, so please bear with me! I often make lot
purchases of 50 different books. What I believe I should do is set up one
table, say "Purchases" that contains PurchaseID, LotID (which would be the #
of books purchased), PurchaseDate, Cost, and Tax. I believe I should have a
separate table that would track my actual individual books, say "Items" with
ItemID, PurchaseID (as a foreign key), Title, CategoryID (type of book), and
UnitCost (a calculated field based on cost+tax/lotID).

First of all, is my assumption correct?

Absolutely. Take a look at the Northwind sample database for a
worked-out example. Unless you need to store it for historical
reasons, you can just calculate the UnitCost rather than storing it in
your table, though.

If so, I would like to create a form for Purchases with a subform for Items
and be automatically prompted to enter x amount of "Items" where x = LotID.
Is this possible? If so, how would I do this?

Why preenter records?

A Continuous Subform will let you enter a row; move to the next row,
enter another; move to the next row, enter another, as long as your
fingers hold out (well, or until you type in over 2 GBytes of data).

Thanks in advance for anyone willing to help me out!
Tina

John W. Vinson[MVP]

 

[equuscycle]

John -- thank you for responding so quickly and for your suggestion on unit
price! Where might I find the Northwinds database you referred to? I've
tried to do a search through Google for Northwinds templates, but haven't
been successful. I know at one time they came up through Access, but I'm not
able to get to them that way any longer, unless I'm just not looking in the
right place.

Tina

 

[John Vinson]

John -- thank you for responding so quickly and for your suggestion on unit
price! Where might I find the Northwinds database you referred to? I've
tried to do a search through Google for Northwinds templates, but haven't
been successful. I know at one time they came up through Access, but I'm not
able to get to them that way any longer, unless I'm just not looking in the
right place.

Northwind.mdb should be in the Samples folder under \Office\Access,
unless you chose not to install it. It's certainly still on the Office
CD.

John W. Vinson[MVP]

 

[equuscycle]

Ah - that's why I haven't been able to find it! I recently got a new
machine, and I've come across things in Excell that weren't installed.

Thanks again!!!!!

Tina