Data entry form based on a Query (Access XP)

Topic

New Reply

I’m building a database for inventory. This is my second go around. I’ve created a form that works well to enter new inventory items (see attached database). I then built a query that concatenates the fields on the form to come up with a comprehensive description of each item. I would like to use this comprehensive description in a form that would enter new receipts of inventory items already in the database and a form that records inventory items used (or sold). Can I pull the description from the query and then have to form put it into another table that tracks either inventory items received or used? Also how can I avoid having duplicates come up in the query?

Thanks in advance for any help.

Christa

Reply | Quote

Replies

Hi Christa,
I took a look at your database, but am a bit confused about why you have three different tables for Inventory – Inventory Data Entry, Inventory Received and Inventory Used. In general, databases of this type have a table that lists all of the different products, with each product having a unique ID. Then you have receivals that come in as a transaction, and only repeat the unique info about that receipt of product and use the unique ID to identify which product it was. That transaction adds the quantity received to the current quantity on hand. The same is also true for usage/sales/shipments where you have a reverse transaction that subtracts from the quantity on hand. In many cases those two transactions are stored in the same table with a different transaction type indicator. Transactions usually include a date and time field, and often who entered the transaction. Often they also have a unique numeric ID number for sequencing purposes, usually an autonumber field. With this kind of structure there is no need to assemble a complete desicription of the product as it can be displayed on a combo box on the form. Hope this helps – if it leaves you more rather than less confused, please post back. Inventory systems can be a real pain if you are the developer, but that can also go a long way toward making a business run well.

Reply | Quote

Hi Wendell,

What you are describing sounds great and it’s a goal that I would like to get to. The reason there is more than one table for inventory is because I was experimenting on how to get the description into a combo box so that the receipts and subtractions could be recorded. The reason that I created to form that’s already there in enter inventory is to enter ‘new items’ that are not in the database yet. I included the quantity, cost etc on the form because I think that it is more user friendly that way than having to have the user go to a different form to enter the quantity etc after they’ve added the new item.

Is it possible to use one form to enter data into two different tables? (eg. putting the “item” into a table that lists inventory items only once and the quantity received into a transaction table as you suggest?) How do I set up the database to enter two different types of transactions into one table? Do you know of any place on the web that there might be a template for an inventory database for me to look at?

I’m really new at using Access…sorry for all the questions

Thanks,

Reply | Quote

Sorry for the long delay in responding – the last week got really hectic. To answer your question about data into two tables, yes it certainly is possible to put data into two tables from one form. It usually involves either creating a join between two related tables, using a subform/main form arrangement, or using an unbound form with lots of VBA code behind it. As to doing two different kinds of transactions in one table, we usually simply put a new field in the table which shows which kind of transaction it is.

Back to the general concept of database design, there are some normalization rules which are intended to make databases work well that you might want to read about a bit. It’s a rather essoteric subject, but the more general ones relate to first through third normal form and might help make your design better. Most Access books talk about them a bit – you can also find several places on the web that talk about the subject, and you can search this form for the words “normal” and “rule” to find other threads about it. (I stumbled across post 381775 which has a useful attachment about normalization after I made the initial post.)

Reply | Quote

Sorry for the long delay in responding – the last week got really hectic. To answer your question about data into two tables, yes it certainly is possible to put data into two tables from one form. It usually involves either creating a join between two related tables, using a subform/main form arrangement, or using an unbound form with lots of VBA code behind it. As to doing two different kinds of transactions in one table, we usually simply put a new field in the table which shows which kind of transaction it is.

Back to the general concept of database design, there are some normalization rules which are intended to make databases work well that you might want to read about a bit. It’s a rather essoteric subject, but the more general ones relate to first through third normal form and might help make your design better. Most Access books talk about them a bit – you can also find several places on the web that talk about the subject, and you can search this form for the words “normal” and “rule” to find other threads about it. (I stumbled across post 381775 which has a useful attachment about normalization after I made the initial post.)

Reply | Quote

Hi Wendell,

What you are describing sounds great and it’s a goal that I would like to get to. The reason there is more than one table for inventory is because I was experimenting on how to get the description into a combo box so that the receipts and subtractions could be recorded. The reason that I created to form that’s already there in enter inventory is to enter ‘new items’ that are not in the database yet. I included the quantity, cost etc on the form because I think that it is more user friendly that way than having to have the user go to a different form to enter the quantity etc after they’ve added the new item.

Is it possible to use one form to enter data into two different tables? (eg. putting the “item” into a table that lists inventory items only once and the quantity received into a transaction table as you suggest?) How do I set up the database to enter two different types of transactions into one table? Do you know of any place on the web that there might be a template for an inventory database for me to look at?

I’m really new at using Access…sorry for all the questions

Thanks,

Reply | Quote

Hi Christa,
I took a look at your database, but am a bit confused about why you have three different tables for Inventory – Inventory Data Entry, Inventory Received and Inventory Used. In general, databases of this type have a table that lists all of the different products, with each product having a unique ID. Then you have receivals that come in as a transaction, and only repeat the unique info about that receipt of product and use the unique ID to identify which product it was. That transaction adds the quantity received to the current quantity on hand. The same is also true for usage/sales/shipments where you have a reverse transaction that subtracts from the quantity on hand. In many cases those two transactions are stored in the same table with a different transaction type indicator. Transactions usually include a date and time field, and often who entered the transaction. Often they also have a unique numeric ID number for sequencing purposes, usually an autonumber field. With this kind of structure there is no need to assemble a complete desicription of the product as it can be displayed on a combo box on the form. Hope this helps – if it leaves you more rather than less confused, please post back. Inventory systems can be a real pain if you are the developer, but that can also go a long way toward making a business run well.

Reply | Quote