create a small stock control and sales database

My fishing club sells bait to members. I want to print receipts for each
purchase listing items purchased and log the sales for stock inventory. Each
product has a unique ID and I want to enter that ID in the form for each
product being purchased and have the product description appear onscreen as
its entered. Finally the list thus produced will be printed as report for the
customer's receipt. I have three tables, product inventory, new stock in, and
stock sold all linked by product ID number. I'm having difficulty both
pulling out stock used for info purposes and recording the results to my
table (stock sold) at the same time. The nearest analogy which springs to
mind is a shop check out. The main table will only contain perhaps 150 items
and their description.

Have you looked at the "Inventory Control" sample database? (Use File
-- New, choose "Databases".) You might combine it with some of the
features of "Order Entry".

Barry Claxton wrote:

My fishing club sells bait to members. I want to print receipts for each
purchase listing items purchased and log the sales for stock inventory. Each
product has a unique ID and I want to enter that ID in the form for each
product being purchased and have the product description appear onscreen as
its entered. Finally the list thus produced will be printed as report for the
customer's receipt. I have three tables, product inventory, new stock in, and
stock sold all linked by product ID number.

[product inventory] seems straightforward; it might contain fields like
[ProductName], [ProductDescription], [CategoryID], [UnitPrice],
[ReorderLevel], and [LeadTime].

Does [new stock in] contain items waiting to be incorporated into
[product inventory]?

Perhaps [stock sold] contains historical information. Do you also keep
track of to whom something is sold (such as in a [members] Table)?

I'm having difficulty both
pulling out stock used for info purposes and recording the results to my
table (stock sold) at the same time. The nearest analogy which springs to
mind is a shop check out. The main table will only contain perhaps 150 items
and their description.

There are many ways you could organize your information. If you could
be a little more specific about what's in each Table, and specifically
what has to be shown on each receipt, that might help.

-- Vincent Johns
Please feel free to quote anything I say here.

Vincent, thanks a lot for taking the time to respond to my question.
"Inventory control" sample database doesn't really "fit the bill" (What's
"lead time?").
My [Product inventory] table contains fields, Prod_ID_num, Name of product,
Description, type of container, weight, Price, and Purchase price (to the
club).This table will rarely be changed, the "changing tables" are stock in
and stock out.
The [Product sold] table contains fields, Prod_ID_num, Quty sold, date sold
and to whom sold, the latter will be entered manually at present but I may
later link it to the members database using members regiistration Num.
The [Product sold] table contains fields, Prod_ID_num, New stock in, and
date entered, (new stock that is).

I want a form which, when the Prod_ID_num is entered into a blank field
displays all the fields relating to that product from the [Product inventory]
table (except price paid by the club). . That much I've achieved. The
problem now is how to leave that on show and enter the next Prod_ID_Num sold
and so on until the total list of items purchased appears on screen along
with the total purchase cost. NOW, ideally, I hit a button and a replica of
whats on screen is printed out in the form of a report.
At the same time, all this "on screen activity" is being saved to the
[Products sold] Table for later analysis (eg which products are selling best
or not at all.)

The second problem is to create a query which is capable of displaying
totals of each product sold and current stock levels. There are in the region
of 100 stock items and I cant work out how to calculate total sales of (say)
item 4, item 9, and so on for ALL items without creating a query for each
one! EEK!!. I'm sure the method will be simple but I cant think of it!
Thanks again, Barry Claxton.


"Vincent Johns" wrote:

Have you looked at the "Inventory Control" sample database? (Use File
-- New, choose "Databases".) You might combine it with some of the
features of "Order Entry".

Barry Claxton wrote:

My fishing club sells bait to members. I want to print receipts for each
purchase listing items purchased and log the sales for stock inventory. Each
product has a unique ID and I want to enter that ID in the form for each
product being purchased and have the product description appear onscreen as
its entered. Finally the list thus produced will be printed as report for the
customer's receipt. I have three tables, product inventory, new stock in, and
stock sold all linked by product ID number.

[product inventory] seems straightforward; it might contain fields like
[ProductName], [ProductDescription], [CategoryID], [UnitPrice],
[ReorderLevel], and [LeadTime].

Does [new stock in] contain items waiting to be incorporated into
[product inventory]?

Perhaps [stock sold] contains historical information. Do you also keep
track of to whom something is sold (such as in a [members] Table)?

I'm having difficulty both
pulling out stock used for info purposes and recording the results to my
table (stock sold) at the same time. The nearest analogy which springs to
mind is a shop check out. The main table will only contain perhaps 150 items
and their description.

There are many ways you could organize your information. If you could
be a little more specific about what's in each Table, and specifically
what has to be shown on each receipt, that might help.

-- Vincent Johns
Please feel free to quote anything I say here.

Barry Claxton wrote:
Vincent, thanks a lot for taking the time to respond to my question.
"Inventory control" sample database doesn't really "fit the bill" (What's
"lead time?").

Sorry, I should have explained that it's not intended to be used without
changes. What I was hoping was that it would give you some ideas of
types of Tables, Queries, Reports, etc., that you could use or change to
suit your needs. You could just erase the parts that you don't need,
like fields in a Table, or entire Tables. My comments also apply to the
Northwind Traders database (sample database that is shipped with Access).

My [Product inventory] table contains fields, Prod_ID_num, Name of product,
Description, type of container, weight, Price, and Purchase price (to the
club).This table will rarely be changed, the "changing tables" are stock in
and stock out.

I assume that [Prod_ID_num] is the primary key of this Table, used to
identify some product type. It's a valid name, but since the key will
also be used elsewhere to refer to a product (it's called a "foreign
key" when it's used in another Table), I suggest renaming it to
something reflecting the name of the Table where it's the primary key,
such as [Product inventoryID]. You could use this same name in other
Tables that need to refer to a product type.

I notice that you consider [Purchase price] (= per-unit cost to the
club) to be a quality of the product type. Are you sure that this will
never change? Suppose you get a discount, or there's a tax increase? I
suggest you move this to your purchase-records Table (which I'm
suggesting you call something like [New Stock]). If you leave it here,
and then later change it, changing it will invalidate any existing
records that depended on the former value. Be careful.

The [Price] field, I think, could easily be left here and changed as
needed to reflect whatever the current price is that you're charging.
But you might also want to copy it to a new field in [Product sold] to
reflect what the unit price was at the time of a specific sale.
Changing [Product inventory].[Price] would then not goof the values of
[Product sold].[Price] for products that you sold last week.

The [Product sold] table contains fields, Prod_ID_num, Quty sold, date sold
and to whom sold, the latter will be entered manually at present but I may
later link it to the members database using members regiistration Num.

You might want to also include a primary key in [Product sold], perhaps
calling it [Product soldID], in case you need to refer to individual
sales records. I suggest making it an Autonumber (with "New Values" set
to Random so that you won't mistake them for something else). If you
find later that you do not need it, you can remove it then.

The [Product sold] table contains fields, Prod_ID_num, New stock in, and
date entered, (new stock that is).

You might want a new name for the second [Product sold] Table, perhaps
something like [New Stock]. :-)

I want a form which, when the Prod_ID_num is entered into a blank field
displays all the fields relating to that product from the [Product inventory]
table (except price paid by the club). . That much I've achieved.

I hope you have that set up so that your user chooses (for example) a
product name from an alphabetical list, instead of having to enter a
number, as using just numbers can lead to mistakes. (You can get
mistakes with names, too, but why tempt fate?)

The
problem now is how to leave that on show and enter the next Prod_ID_Num sold
and so on until the total list of items purchased appears on screen along
with the total purchase cost. NOW, ideally, I hit a button and a replica of
whats on screen is printed out in the form of a report.
At the same time, all this "on screen activity" is being saved to the
[Products sold] Table for later analysis (eg which products are selling best
or not at all.)

Much of what you say you want to do is already done in the [Orders] Form
in the Northwind Traders database, Northwind.mdb. Just make a copy of
the database (and, I suggest, the other files in your Office\Samples\
folder whose names begin with NW) and play with it.

You'll still have some work to do (for example, you might want to
replace the Northwind logo with a picture of your bait shop), but much
of the heavy lifting has already been done for you, so you don't have to
do it all yourself.

If you have the time, you might even consider making a copy of Northwind
Traders and copying into it (in suitable places) the data you have,
replacing the current records. (I expect that your shop doesn't sell
Caernarvon tigers.) The Northwind [Products] Table could hold what you
have in [Product inventory], and the [Order Details] Table could contain
the information you have in [Product sold]. Details about new stock in
don't have an obvious counterpart in Northwind, but you could probably
just use new copies of [Orders] and maybe [Order Details] to record
those data.

The second problem is to create a query which is capable of displaying
totals of each product sold and current stock levels. There are in the region
of 100 stock items and I cant work out how to calculate total sales of (say)
item 4, item 9, and so on for ALL items without creating a query for each
one! EEK!!. I'm sure the method will be simple but I cant think of it!

Yes, there's an easy way. In Query Design View, if you click on the
little capital Sigma (Greek letter looking like a zigzaggy E) in the
toolbar, it will allow you to group your sales by item, by month, &c.,
and display the sums. (In Northwind, look at the [Product Sales for
1997] Query.) Or you might be able to use a Crosstab Query to do
something similar; look at Northwind's [Quarterly Orders by Product]
Query. (Check out GROUP BY and Crosstab topics in Access Help.)

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks again, Barry Claxton.


"Vincent Johns" wrote:


Have you looked at the "Inventory Control" sample database? (Use File
-- New, choose "Databases".) You might combine it with some of the
features of "Order Entry".

Barry Claxton wrote:


My fishing club sells bait to members. I want to print receipts for each
purchase listing items purchased and log the sales for stock inventory. Each
product has a unique ID and I want to enter that ID in the form for each
product being purchased and have the product description appear onscreen as
its entered. Finally the list thus produced will be printed as report for the
customer's receipt. I have three tables, product inventory, new stock in, and
stock sold all linked by product ID number.

[product inventory] seems straightforward; it might contain fields like
[ProductName], [ProductDescription], [CategoryID], [UnitPrice],
[ReorderLevel], and [LeadTime].

Does [new stock in] contain items waiting to be incorporated into
[product inventory]?

Perhaps [stock sold] contains historical information. Do you also keep
track of to whom something is sold (such as in a [members] Table)?


I'm having difficulty both
pulling out stock used for info purposes and recording the results to my
table (stock sold) at the same time. The nearest analogy which springs to
mind is a shop check out. The main table will only contain perhaps 150 items
and their description.

There are many ways you could organize your information. If you could
be a little more specific about what's in each Table, and specifically
what has to be shown on each receipt, that might help.

-- Vincent Johns
Please feel free to quote anything I say here.

Vincent, I'm sorry if I appeared dismissive of your advice to use the ready
made "Inventory Control" database, it wasn't my intention! I looked at it
and, for example fields like "Serial Number" and "Lead time" (you didn't tell
me what that is!) are required fields, I cant delete them (it says). I
reasoned that there would be more work re-structuring it than to start my own
"from the ground up".

Should I end up doing that then some of the advice you offer is very
helpful. I intend to take a fresh look at "inventory control" and the "North
Wind" Db as well.

I've played with a cross tab query and that appears to be the answer for
keeping a running total of stock, both in and out, So... back to the drawing
board! If you watch during the next couple of weeks for a posting using the
same (or similar) heading as this one I'll let you know how I go on. I
honestly think guys like yourself are stars to take time out trawling these
postings and offering help. When one's working alone at home, sometimes it's
great to be able to talk through your problems, thanks a lot.

"Vincent Johns" wrote:

Barry Claxton wrote:
Vincent, thanks a lot for taking the time to respond to my question.
"Inventory control" sample database doesn't really "fit the bill" (What's
"lead time?").

Sorry, I should have explained that it's not intended to be used without
changes. What I was hoping was that it would give you some ideas of
types of Tables, Queries, Reports, etc., that you could use or change to
suit your needs. You could just erase the parts that you don't need,
like fields in a Table, or entire Tables. My comments also apply to the
Northwind Traders database (sample database that is shipped with Access).

My [Product inventory] table contains fields, Prod_ID_num, Name of product,
Description, type of container, weight, Price, and Purchase price (to the
club).This table will rarely be changed, the "changing tables" are stock in
and stock out.

I assume that [Prod_ID_num] is the primary key of this Table, used to
identify some product type. It's a valid name, but since the key will
also be used elsewhere to refer to a product (it's called a "foreign
key" when it's used in another Table), I suggest renaming it to
something reflecting the name of the Table where it's the primary key,
such as [Product inventoryID]. You could use this same name in other
Tables that need to refer to a product type.

I notice that you consider [Purchase price] (= per-unit cost to the
club) to be a quality of the product type. Are you sure that this will
never change? Suppose you get a discount, or there's a tax increase? I
suggest you move this to your purchase-records Table (which I'm
suggesting you call something like [New Stock]). If you leave it here,
and then later change it, changing it will invalidate any existing
records that depended on the former value. Be careful.

The [Price] field, I think, could easily be left here and changed as
needed to reflect whatever the current price is that you're charging.
But you might also want to copy it to a new field in [Product sold] to
reflect what the unit price was at the time of a specific sale.
Changing [Product inventory].[Price] would then not goof the values of
[Product sold].[Price] for products that you sold last week.

The [Product sold] table contains fields, Prod_ID_num, Quty sold, date sold
and to whom sold, the latter will be entered manually at present but I may
later link it to the members database using members regiistration Num.

You might want to also include a primary key in [Product sold], perhaps
calling it [Product soldID], in case you need to refer to individual
sales records. I suggest making it an Autonumber (with "New Values" set
to Random so that you won't mistake them for something else). If you
find later that you do not need it, you can remove it then.

The [Product sold] table contains fields, Prod_ID_num, New stock in, and
date entered, (new stock that is).

You might want a new name for the second [Product sold] Table, perhaps
something like [New Stock]. :-)

I want a form which, when the Prod_ID_num is entered into a blank field
displays all the fields relating to that product from the [Product inventory]
table (except price paid by the club). . That much I've achieved.

I hope you have that set up so that your user chooses (for example) a
product name from an alphabetical list, instead of having to enter a
number, as using just numbers can lead to mistakes. (You can get
mistakes with names, too, but why tempt fate?)

The
problem now is how to leave that on show and enter the next Prod_ID_Num sold
and so on until the total list of items purchased appears on screen along
with the total purchase cost. NOW, ideally, I hit a button and a replica of
whats on screen is printed out in the form of a report.
At the same time, all this "on screen activity" is being saved to the
[Products sold] Table for later analysis (eg which products are selling best
or not at all.)

Much of what you say you want to do is already done in the [Orders] Form
in the Northwind Traders database, Northwind.mdb. Just make a copy of
the database (and, I suggest, the other files in your Office\Samples\
folder whose names begin with NW) and play with it.

You'll still have some work to do (for example, you might want to
replace the Northwind logo with a picture of your bait shop), but much
of the heavy lifting has already been done for you, so you don't have to
do it all yourself.

If you have the time, you might even consider making a copy of Northwind
Traders and copying into it (in suitable places) the data you have,
replacing the current records. (I expect that your shop doesn't sell
Caernarvon tigers.) The Northwind [Products] Table could hold what you
have in [Product inventory], and the [Order Details] Table could contain
the information you have in [Product sold]. Details about new stock in
don't have an obvious counterpart in Northwind, but you could probably
just use new copies of [Orders] and maybe [Order Details] to record
those data.

The second problem is to create a query which is capable of displaying
totals of each product sold and current stock levels. There are in the region
of 100 stock items and I cant work out how to calculate total sales of (say)
item 4, item 9, and so on for ALL items without creating a query for each
one! EEK!!. I'm sure the method will be simple but I cant think of it!

Yes, there's an easy way. In Query Design View, if you click on the
little capital Sigma (Greek letter looking like a zigzaggy E) in the
toolbar, it will allow you to group your sales by item, by month, &c.,
and display the sums. (In Northwind, look at the [Product Sales for
1997] Query.) Or you might be able to use a Crosstab Query to do
something similar; look at Northwind's [Quarterly Orders by Product]
Query. (Check out GROUP BY and Crosstab topics in Access Help.)

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks again, Barry Claxton.


"Vincent Johns" wrote:


Have you looked at the "Inventory Control" sample database? (Use File
-- New, choose "Databases".) You might combine it with some of the
features of "Order Entry".

Barry Claxton wrote:


My fishing club sells bait to members. I want to print receipts for each
purchase listing items purchased and log the sales for stock inventory. Each
product has a unique ID and I want to enter that ID in the form for each
product being purchased and have the product description appear onscreen as
its entered. Finally the list thus produced will be printed as report for the
customer's receipt. I have three tables, product inventory, new stock in, and
stock sold all linked by product ID number.

[product inventory] seems straightforward; it might contain fields like
[ProductName], [ProductDescription], [CategoryID], [UnitPrice],
[ReorderLevel], and [LeadTime].

Does [new stock in] contain items waiting to be incorporated into
[product inventory]?

Perhaps [stock sold] contains historical information. Do you also keep
track of to whom something is sold (such as in a [members] Table)?


I'm having difficulty both
pulling out stock used for info purposes and recording the results to my
table (stock sold) at the same time. The nearest analogy which springs to
mind is a shop check out. The main table will only contain perhaps 150 items
and their description.

There are many ways you could organize your information. If you could
be a little more specific about what's in each Table, and specifically
what has to be shown on each receipt, that might help.

-- Vincent Johns
Please feel free to quote anything I say here.

Barry Claxton wrote:

Vincent, I'm sorry if I appeared dismissive of your advice to use the ready
made "Inventory Control" database, it wasn't my intention! I looked at it
and, for example fields like "Serial Number" and "Lead time" (you didn't tell
me what that is!) are required fields, I cant delete them (it says). I
reasoned that there would be more work re-structuring it than to start my own
"from the ground up".

Well, even if you can't delete them (without first deleting the links),
you could probably ignore them, and add fields of your own design.

Should I end up doing that then some of the advice you offer is very
helpful. I intend to take a fresh look at "inventory control" and the "North
Wind" Db as well.

I've played with a cross tab query and that appears to be the answer for
keeping a running total of stock, both in and out, So... back to the drawing
board! If you watch during the next couple of weeks for a posting using the
same (or similar) heading as this one I'll let you know how I go on. I
honestly think guys like yourself are stars to take time out trawling these
postings and offering help. When one's working alone at home, sometimes it's
great to be able to talk through your problems, thanks a lot.

Looking at a good-quality database like Northwind is an excellent way to
become familiar with what you can do witn Access. Good luck!

-- Vincent Johns
Please feel free to quote anything I say here.

i am trying to enter info. in the fields and when i am finish i won't the
number to go right in order but they don't . we i am typing the po number
theymay not be in order and i want the database to do that.
i didi it for one table but can not figure it out for the other

helppppppppppppppppp

Not really sure what you are asking. The order of entries in your table is
not important. If you enter records in a table, then wish to see them in
numerical order, simply create a query, report, or form and set the desired
sorting. You should never work directly in the tables, so the order there
is irrelevant.

--
Rick B



"access database reports"
wrote in message ...
i am trying to enter info. in the fields and when i am finish i won't the
number to go right in order but they don't . we i am typing the po number
theymay not be in order and i want the database to do that.
i didi it for one table but can not figure it out for the other

helppppppppppppppppp

As Rick points out, you are better off working in a form than directly in
the table.

Access stores the data entered in a table in a big bucket, in any order it
wants to. If YOU want to see the data in some order, it is up to you to
create a query and tell Access what that order is.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"access database reports"
wrote in message ...
i am trying to enter info. in the fields and when i am finish i won't the
number to go right in order but they don't . we i am typing the po number
theymay not be in order and i want the database to do that.
i didi it for one table but can not figure it out for the other

helppppppppppppppppp