Editing data on single/multiple subforms

[Bradex]

I have 2 tables similar to the following example:
Table A: Customer details
The other tables contain details of orders placed by the customer
Table B: Orders - Books
Table C: Orders - Groceries
Table D: Orders - Electronics
Table A has one-to-many relationship with Tables B, C, and D.

Question 1: One form and one sub-form
Suppose I create a single form, where I place controls from Table A and Table B as a sub-form (displaying records as a spreadsheet/table) [NOTE: I did not display the sub-form as a form, as it would display only one record at a time while a spreadsheet/table would display a list of records linked to the customer]. User should now be able to click on a customer, and then add/edit records in the sub-form. Assuming that Table B has numerous fields, the user now has to drag the horizontal scroll bar on the sub-form to enter data. Now, is that a good idea?

Question 2: One form and multiple sub-forms


Suppose I wish the user to enter each of the orders pertaining to a customer sequentially (e.g. Customer details (Table A) > Orders - Books (Table B) > Orders - Groceries (Table C) > Orders > Electronics (Table D)), what is the best option for that?

Option A: Single form with multiple sub-forms on the pages of a tabbed form

• Create a single form
• Add controls from Table A
• Beside it add a tabbed form
• Add all the other tables as sub-forms on the pages of the tabbed form

Option B: Single form with sequential sub-form pop-ups (I do not know how to do this!)

• Make the user enter enter the customer details first
• He clicks on a button which opens 'Orders - Books' as a sub-form pop-up window
• After completing entry here, he clicks on a 'Next' button' on this sub-form, which closes the current sub-form and open the next sub-form 'Orders-Groceries'.
• The second sub-form 'Orders - Groceries' will have two buttons 'Previous' and 'Next' to navigate between the sub-forms, and so on.

Option C: Separate forms for each table

• User enters Form A with details from Table A
• Clicks a button which opens Form B for Table B
• I do not prefer this, as this method will not display the relation between the customer and his orders as 'Master and Child'.

[alansidman]

Here is a tutorial on sub forms. BTW. If I were doing this, I would only have 2 tables. 1 for the customer and 1 for the orders. I would add a field to the order table indicating the type of order (ie. Books, Groceries, Electronics). This will make retrieving the data easier and will help to normalize your data. You may want to read about normalization in the second link.

http://www.datapigtechnologies.com/f...subforms1.html
http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf

[orange]

I agree with Alan re tables.

Customer --->Order

If an Order can be for Many Products then I would revise that set up to 4 tables

Customer--->Order--->OrderDetails<----Product

I would start with a draft model of the things involved, and use the business description/facts to establish relationships. Do you have a clear description of the business and processes?
I would not start with Forms.

Good luck with your project.

[Bradex]

Thanks for the information, but I am still having some issues:
@alansidman: How can I use just 2 tables? Suppose I use CustomerTable and OrderTable. A customer can place many orders. Each order can contain different items (Books, groceries, electronics). The item description is different for each item (Books - title, pages, weight; electronics: display size, battery duration). So, I have to create a separate table for each item.
Suppose I do manage to use only two tables and then display them as a form and sub-form, respectively, editing data on the sub-form will be difficult as it will have too many fields (as explained in Question 1). Is there a better method for displaying OrderTable?

@Orange: "Customer--->Order--->OrderDetails<----Product" seems okay, but I am not able to correctly create tables for it. Could you please help me out?
I have attached the image of an excel table for the desired structure:

Please note that the ItemDescription will be different for different items, so different items cannot be placed in similar columns and they have to have their own tables with their own -item-specific description columns.

I understand that I have to create the following tables:
-CustomerTable (has customer name, address, etc.): CustomerID is primary key and has one-to-many relation with OrderIDTable
-OrderIDTable (has order date and other details): OrderID is the primary key
-BookTable (has the descriptive columns for books): OrderID is the primary key
-ElectronicsTable (has the descriptive columns for electronics): ElectronicsID is the primary key
-GroceriesTable (has the descriptive columns for groceries): GroceriesID is the primary key
OrderID Table has one to many relationship with the ID of each of the above tables.

But this is not exactly what you suggested it seems!

[orange]

Please note that the ItemDescription will be different for different items, so different items cannot be placed in similar columns and they have to have their own tables with their own -item-specific description columns.

This is incorrect, generally.
Database is quite different than spreadsheet.
With database you would have an Item table and possibly an ItemType table.
If you really have different Items with different attributes, then tell us more about your requirements.

This is a general approach to Item and ItemType.

tblItem

ItemID PK
ItemName
ItemDescription
ItemTypeId FK to ItemType table.


tblItemType
ItemTypeId PK
ItemTypeName
OtherItemTypeInfo???

ItemTypeName could be groceries/Electronics.....

You should review database Normalization.

Here are some samples of generic Customer Order Product data models. I hope they are useful.



CustomerOrders

If you do require separate tables for different Item types, you should research Database Supertype and Subtype.
Here is a link to get you started, but read the caveats and related materials.


I recommend you watch videos #1,2 and 4 in this free youtube Database series by Dr. Daniel Soper before you do any design. The series starts here.

Good luck with your project.

[Bradex]

Thanks a lot!

I will go through all the information in detail and report back if the issue persists!