Barry said:
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.)
Thanks again, Barry Claxton.
:
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 <
[email protected]>
Please feel free to quote anything I say here.
