Fill in table info based on # entered into specific field

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7752-fill-table-info-based-entered-into.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

On Fri, 18 Nov 2005 17:17:05 -0800, "equuscycle"
wrote:

>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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
enter another; move to the next row, enter another, as long as your
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
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]

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
able to get to them that way any longer, unless I'm just not looking in the
right place.

Tina

"John Vinson" wrote:

> On Fri, 18 Nov 2005 17:17:05 -0800, "equuscycle"
> wrote:
>
> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
> 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]
>

On Fri, 18 Nov 2005 1902 -0800, "equuscycle"
wrote:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
>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]

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

"John Vinson" wrote:

> On Fri, 18 Nov 2005 1902 -0800, "equuscycle"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
> wrote:
>
> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7752
> >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]
>