If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
create a small stock control and sales database
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. |
#3
|
|||
|
|||
create a small stock control and sales database
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. |
#4
|
|||
|
|||
create a small stock control and sales database
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. |
#5
|
|||
|
|||
create a small stock control and sales database
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. |
#6
|
|||
|
|||
create a small stock control and sales database
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. |
#7
|
|||
|
|||
why don't the po#number go in order when i finish enteing the info
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 |
#8
|
|||
|
|||
why don't the po#number go in order when i finish enteing the info
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 |
#9
|
|||
|
|||
why don't the po#number go in order when i finish enteing the info
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Ambiguous Name Error | pm | Using Forms | 10 | June 5th, 2005 09:19 PM |
Total hours from Table into Report | HeatherLou1974 | General Discussion | 3 | March 7th, 2005 08:04 PM |
Stock Control Database | Wantula | Database Design | 1 | October 27th, 2004 02:13 PM |
How can I create a Stock Control system for a Public House using . | Joe Flynn | General Discussion | 1 | September 16th, 2004 02:02 PM |
Access Calendar | lost | General Discussion | 2 | July 7th, 2004 04:58 AM |