Pulling records from database to build an item lists in ordering form?

[Demonpumpkin]

I am trying to build a database to hold the order details and hoping that some one can advice a proper database design. The ordering page has fixed amount of 15 items, people open the form and fill in the quantity of the listed items.

I can do the lazy way and create 15 fields for each of the item name and price, but I would prefer to have the ordering form to pull data from a separate table (Products database) and populate the ordering form automatically with the items and the current prices (keeping the old order form price history).



This can allow the other people to update the price or perhaps increase the amount of items on the 'Product database' and it will reflect on the ordering page without needing to redesign the database/form. Any help?

[ItsMe]

Your order form should not have "Fifteen Fields" that represent items that may be or may get ordered.

Your order history should be represented in several tables. One table would be something similar to a purchase order table. This can hold records with unique identifiers "Primary Keys" of OrderNumber/PurchaseOrder. This number would show as the main transaction number.

Another table would hold the "Detail" of your transaction events. You would place the primary key of an OrderNumber and the primary key of an item sold or service rendered in this second table of "Detail". The primary key of your fifteen services/items will represent rows/records in a third table. This third table will have at least 15 records to begin with.

Queries will bring all of your tables together via Joins/Relationships.

[Demonpumpkin]

Thanks for the reply. My work will be much easier if the users select the items ordered one by one, but they prefer the items all listed out, be it ordered or not, due to the nature of the sales that each order will almost cover the 15 items, and will be much quicker for them to just hit the quantity field. So every new order will have that 15 items ready for them to just type the quantity.

What I am thinking now is to create a 20 fields ordering database with default value that pull data from the Product database, the users can update the price on the Product database, and at least give me some leeway before I need to increase the amounts of fields again if they products are increasing.

I am hoping there is a better way to dynamically adding the item fields on the ordering page.

[ItsMe]

What you are attempting to do sounds like something called "Committing Spreadsheet". You need to satisfy the users' needs to work with the database. This is known as "Business Rules".

If you build your tables like spreadsheets or build tables to look like an order form, you will not have a functional Relational Database or RDBMS.

Use queries to create recordsets. Build a form that will display one, two or more recordsets. The form is a GUI that the user will see. The user will not see queries or tables. How the tables and querries look and behave should not matter to a user. Only the GUI, the "Application" part of Access matters to the Users and their "Business Rules"

[Demonpumpkin]

I had already built an Order Database with linked Order Details that pull data from the Product database, pretty much what you had described earlier. It works perfectly but for every new order record I have to add in the products one by one, which is the part I am stuck with.

Is there a way to just add all the products in for every new order record? Exploring the function INSERT TO, but still figuring out the syntax...

[ItsMe]

Sorry I did not respond sooner.

I really am having a hard time visualizing what, exactly, you are working with. In post one you mention fields. Well, fields are found in tables. Fields can be within a row and they can be within a column. When I read post #1 I tend to think that you are committing "Columns" to specific Items. Having 15 columns where the field names are dedicated to 15 items is not a good idea. This will cause issues when a new item needs to be introduced. The solution is to have your "items" in a separate table and store the "items" PK in an event table.


You also mention a database and "Linked". There is nothing wrong with linking tables from another database. When you do this, you should be using the linked tables and not "store" data in a separate DB. There are exceptions, of course. If you already have a DB that you are linking to, there is not any reason to "create fields".

If you want to use INSERT within an action query, that is OK. There may be a way to get what you need by using a main form bound to a recordset that represents a PO or Sales Order number. On this main form there can be a subform that is bound to a recordset with the details or items associated with the unique order number.

If you don't want to type directly into the subform and add multiple records to compose the "detail" then you can have combo boxes and unbound text boxes collect user input. With this information you can run an action query to update the details table. You can also use DAO to add new records and populate fields with values provided via user input.