Production (Main)

[Lourens7707]

Hi, how can I add completed production items to a production pool , and then draw from that pool by hand of a customer order

 

[Lourens7707]

Hi, how can I add completed production items to a production pool , and then draw from that pool by hand of a customer order

That is in access

 

[The_Doc_Man]

From your low number of posts, it is not clear how much you know about database setup. However, I'm going to assume that you are in the very first stages of trying to do this. Access, unfortunately, is not a trivial tool to learn quickly because it is incredibly powerful. I'm going to give you some advice that I believe to be appropriate for the stage of development that I believe applies to you.

Old programmer's rule #1: If you can't do it on paper then you can't do it in Access.

Expanded, that means that you need to have a document of some kind that lists what you are trying to do via a paper "flow diagram." In making that document, you will identify your data sources and destinations. You will identify your process steps. You will have to think about how to perform some of these steps that you will identify. But that is the nature of the beast - that you need to decide what you want to do before you try to get Access to do it for you. That is because Access, not being psychic, can't read your mind and make things happen. If you have not done so before this time, I think you should learn a little something about normalization. In this forum, you can search for normalization. If you search the worldwide web, you must search for "database normalization" because there are other kinds of normalization. If you do a web search, initially stick with .EDU domain sites because most of the .COM sites have something to sell you.

It is in this stage that you identify tables that are the work-horse data storage entities within Access. Which means that your production pool is a candidate for being represented in a table. However, there are implications of this, since you have specified "completed" items. You may have either another table or a new field in the production items to indicate whether it is completed. As to how you select that, it is usually done via some type of form. Being "selected" might mean yet another flag OR a more complex status flag that can have states of "in work" "completed" "selected" etc. Up to you as to you would approach it because we don't know too much about your business setup.

Old programmer's rule #2: Access won't tell you anything you didn't explain to it first.

Expanded, that is a reminder that YOU are the subject matter expert. Access is top-notch at rapid development of forms and reports and queries and tables, and is a decent programming interface for the occasional code module that crops up as a necessity. Access knows about how to do those things - but it has NO CLUE as to WHY you want anything. That is something YOU know and thus something that you need to articulate (probably in step 1 as part of your documentation). More to the point, if you decide in step 1 (making that document) that you want an output of X then in step 2 you have to assure that you have a source or a formula for X. If you want XYZ then you need a source of XYZ; or you need sources of X, Y, and Z and the formula that combines them to XYZ. If this sometimes means working backwards through your code, then so be it.

 

[Lourens7707]

From your low number of posts, it is not clear how much you know about database setup. However, I'm going to assume that you are in the very first stages of trying to do this. Access, unfortunately, is not a trivial tool to learn quickly because it is incredibly powerful. I'm going to give you some advice that I believe to be appropriate for the stage of development that I believe applies to you.

Old programmer's rule #1: If you can't do it on paper then you can't do it in Access.

Expanded, that means that you need to have a document of some kind that lists what you are trying to do via a paper "flow diagram." In making that document, you will identify your data sources and destinations. You will identify your process steps. You will have to think about how to perform some of these steps that you will identify. But that is the nature of the beast - that you need to decide what you want to do before you try to get Access to do it for you. That is because Access, not being psychic, can't read your mind and make things happen. If you have not done so before this time, I think you should learn a little something about normalization. In this forum, you can search for normalization. If you search the worldwide web, you must search for "database normalization" because there are other kinds of normalization. If you do a web search, initially stick with .EDU domain sites because most of the .COM sites have something to sell you.

It is in this stage that you identify tables that are the work-horse data storage entities within Access. Which means that your production pool is a candidate for being represented in a table. However, there are implications of this, since you have specified "completed" items. You may have either another table or a new field in the production items to indicate whether it is completed. As to how you select that, it is usually done via some type of form. Being "selected" might mean yet another flag OR a more complex status flag that can have states of "in work" "completed" "selected" etc. Up to you as to you would approach it because we don't know too much about your business setup.

Old programmer's rule #2: Access won't tell you anything you didn't explain to it first.

Expanded, that is a reminder that YOU are the subject matter expert. Access is top-notch at rapid development of forms and reports and queries and tables, and is a decent programming interface for the occasional code module that crops up as a necessity. Access knows about how to do those things - but it has NO CLUE as to WHY you want anything. That is something YOU know and thus something that you need to articulate (probably in step 1 as part of your documentation). More to the point, if you decide in step 1 (making that document) that you want an output of X then in step 2 you have to assure that you have a source or a formula for X. If you want XYZ then you need a source of XYZ; or you need sources of X, Y, and Z and the formula that combines them to XYZ. If this sometimes means working backwards through your code, then so be it.

Hi Thank you for the reply, I have created a stock code , what the trick is to add an deduct stock amounts by orders

 

[MajP]

You may want to look at the Northwind example database:

Featured Access templates - Microsoft Support

Analyze and store large amounts of information, and manage data more efficiently with Access database templates available for download.

support.office.com

This does stock and inventory. This is one of the more common and more complicated types of databases. It will require a much more detailed question for anyone to answer. If you have some tables or a sample database that will help.

 

[Isaac]

Old programmer's rule #1: If you can't do it on paper then you can't do it in Access.

I used to tell my business partners: If we can define it, we can design it.

 

[June7]

Review http://allenbrowne.com/AppInventory.html

 

[The_Doc_Man]

what the trick is to add an deduct stock amounts by orders

If I am reading correctly between the lines, this is the less preferred method of doing stock. I'll try to give you the overview of the preferred method, which assumes that items in stock, if they have the same stock code, are the same item. That is, absolute replaceability between two items with the same stock code.

You NEVER have a master record with a count of the number of items. EVER. Instead what you do is have the master stock record that supplies names and descriptions associated with the stock code. Then you have a separate transaction table that shows any variety of stock PUT (on the shelves) and any variety of stock PULL (from the shelves.) Each transaction has a date, stock number, amount, and some sort of transaction code. It could have more than these fields if appropriate but those are the minimum requirements. The amounts are such that a PUT amount is positive and a PULL amount is negative. Then you just run a summation query of your transactions grouped by the stock number. The single number you get is quantity on hand. Or in code you can execute a DSUM( "{amount]", "xacttable", "[StockCode] = 'some-stock-code'" ).

Variations on this theme include the idea that the first time something is defined in the master table, it has no transactions. This is OK, though you have to account for it by testing whether you have anything to count. When your supplier delivers the first batch of X (or you manufacture the first batch of X) then the first transaction is a stock PUT. When you sell one, you have a stock PULL. Note that it would be OPTIONAL to include an invoice number in the transaction, which MIGHT be an extra field in the transaction table.

Let's say you do this kind of inventory for a couple of years and you want to declutter the table. You can do an inventory entry to show actual count on hand by manual count. Then archive and remove ALL entries for that one stock item earlier than the inventory entry, which is a type of PUT.

There is a lot more to it, but this should get you started in thinking about how the folks here USUALLY do an inventory system.