Stuck with table design, leading to probs with forms/query 3

[naiku]

I have been trying to tidy up the design of my database by eliminating repeating data. As such, instead of having a single table I now have several much smaller tables.

The problem is that if I do this the only way I can create a form for data entry is by creating a form and having a subform for each of the smaller tables. I was going to join all the smaller tables using a query, and then base the form on the query until I remembered that I can't update recordset's on a query. So this leaves me with the option of subforms, if I follow this path is there anyway that I can format the subform so that it does not look like a subform? (no border's around the subform etc) but just to look like a bunch of fields on a single form?

Or should I stick with my larger table and base the form on this? If I stick with the larger table I then get a problem with a query that I want to use further down the road (this is why I looked at breaking up the tables) If I use the large table I want the query to move data into a new table, and then to create reports on this new table, however I get stuck when trying to move data, I have in my table:

Supplier 1 - Item 1
Supplier 2 - Item 2
Supplier 3 - Item 3
Supplier 1 - Item 4
Supplier 1 - Item 5

But I want to move this into the query as:

Supplier 1 - Item 1, Item 4 and Item 5
Supplier 2 - Item 2
Supplier 3 - Item 3

So that I only need to produce 3 reports instead of 5. Any help on the issues above is greatly appreciated, at the moment I am not sure which way to go and get stuck either way.

 

[PHV]

I can't update recordset's on a query
Really ?
Take a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I would always choose to use the more normalized table structure. Subforms are our friends. You can make them look much like they are part of the main form. You can't make subform records display across.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

Superb article PHV

I can create a form for data entry is by creating a form and having a subform for each of the smaller tables

Nothing wrong with this approach ... provided that the "smaller" tables are properly "normalized".

Your above example is product supply / order system. The two tables listed would be the Supplier, and Products or Items supplier sell / provide. You nailed it perfectly.

So that I only need to produce 3 reports instead of 5

But then I get confused with this statement. Three reports seems to refer to one report per supplier compared to five reports - one for each item.

Would not one report, Grouped on the Supplier suffice?

...I get stuck when trying to move data...

I am going to assume the following...

tblSuppier
SupplierID - prmary key
SupplierName

tblItem
ItemCode - primary key
ItemName
...plus other fields to "describe" items (may include ItemsOnHand, warehouse location, etc)

Okay, how to join the two?
A supplier can supply many items.
But, can an item be supplied by different suppliers? This is a critical question.

...If the answer, NO, each item can have only one supplier, then you have a one-to-many relationship.

You would have to tweak the design for tblItem as follows...

tblItem
ItemCode - primary key
ItemName
SupplierID - foreign key to tblSupplier

...If the answer, YES, an item can have more than one supplier, then you have a many-to-many relationship.

Storing the SupplierID on the tblItem would not work. Instead, you add a third "joiner" or intermediar table.

tblItemSupplier
SupplierID - foreign key to tblSupplier
ItemCode - foreign key to tblItem

Primary key = SupplierID + ItemCode

You can add some additional fields to tblItemSupplier. For example, if you track ItemsOnHand, ItemsOnOrder for each supplier, then they could be added to tblItemSupplier.

Note that before "porting" your data to the new design, it is important to decide which design will work better. Hopefully, the above helped.

...Now back to your problem, how to had data.
You can create Append Queries using the Query Builder.

The easiest thing to create will be the tblSupplier. You just have to make sure not to create duplicate suppliers. You can minimize this problem by creating a unique index on something like the phone number, or zip / postal code, or other info. Creating a unique index is done when designing the table.

The next problem is how to populate the Item table. This will depend on the design -- one-to-many, or many-to-many.

With 1:M, you have to ensure the new append query updates the information for the Item plus SupplierID from tblSupplier. Again, you have to make sure you do not create duplicate Item records.

With a M:M (or N:M), the update process takes two steps. First populate the tblItem with an append query in a similar fashion used for populating the tblSupplier.

The next step is to populate the tblItemSupplier table with the foreign keys used in the other two tables in another append query.

Some further reading...
Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 -

Description of the database normalization basics
304467 - ACC

2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard

 

[naiku]

Thanks for all the information........

But then I get confused with this statement. Three reports seems to refer to one report per supplier compared to five reports - one for each item.

When I say "report" this is so that I can print a purchase order. So in my above example I would only need to create 3 purchase orders (1 per supplier, some orders containing more than 1 item), these are then faxed/emailed to the suppliers who deliver the required products. Hopefully that makes more sense.

I do have a many to many relationship between suppliers and products, and so have the join table.

The purpose of the database is to be able to enter jobs at customer sites, and then print off purchase orders to be able to order in the required products for each job, I have a customer table, and then 2 job detail tables (these are the ones I need to break down). I then have the supplier, product and joining table.

A form is then used to enter customer data, then job data entered using combo boxes (this is where the products and product quantity are chosen) and finally a command button is to be pressed that will generate a job sheet (containing costs and details) and purchase orders to go off to the suppliers.

The suppliers and products tables are populated using their own forms, and then on the job data form the combo boxes use data from these tables, which then gets stored back into the job data table.

I hope that makes a bit more sense about what I am trying to achieve, if it helps to understand what I am doing I can email a copy of the database.

Thanks again.