Multiple tables for one form 2

[Pourmoi]

Hello,

I need your help with this. I am not an expert but I can work around in Access dbase pretty good but this one is killing me.
I have a form (Myform) with a subform (MySubform)linked by one key MyID.
I would like to pull/populate Myform from tableA, add stuffs on Mysubform and be able to save all of it to tableB... in another word when it is saved tableB will contains data from tableA data from the form + new entry from the subform...

Thank you in advance for your help

Pourmoi

 

[Eupher]

Hi, Pourmoi,

I'm compelled to ask... why? If you already have two tables linked through a PK/FK (MyID), why on earth do you want a third table to hold redundant data?

Ken S.

 

[willir]

PourToi

One of the powerful concepts for using relational database is to avoid redundency.

By taking information from two tables (I assume two tables -- table for your main form MyForm, and another table for your subform MySubForm), and copying the contents of both tables to a third table defeats the purpose of using a relational database. a) Duplicate data; b) Data is not "normalized".

Perhaps you have not depicted the situation properly.

MyForm is based on TableA - correct?
Primary key for TableA is MyID - correct?

What table is MySubForm based on?
Is this TableB ??
What is the primary key for TableB?

...Moving on to relationships, you would probably benefit from reading Paul Litwin's article...

Fundamentals of Relational Database Design by Paul Litwin
Download document from Jeramy's site
Read on-line (HTML) at Rudy's site

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

 

[EdwardAtSGI]

You'll need to use VBA code to load the data from Table A.
As the other posters pointed out, what you need to do violates the normalization rules, so the built in binding functionality doesn't support it.

Here's what I have done when I needed to de-normalize a data entry form:

You can use VBA code to pre-load some or your fields with data from Table A. Then allow users to fill out the remaining fields. Finally bind all the fields back to Table B. You could also choose to use VBA code to save the data back to table B, but you shouldn't need to.

This method does not require use of a subform, which also simplifies things a bit.

 

[Pourmoi]

Hello and thank you all for responding to my thread and tried to help. The links are great, lots of useful information.
I think I need to explain further what what I am trying to do here.

MyForm is based on TableA
Primary key for TableA is MyID

MySubForm is based on TableB
Primary key for TableB is MyCID

-TableA contains customer information that populates MyForm and new information will be added through the subform MySubform and both Myform and Mysubform data will all be saved in TableB.

Basically what I need is to be able to use information from TableA and save it in TableB along with new information from the subform

I don't have any third table.

What do you say, can I do something like that?
I appreciate all your help in advance.

Pourmoi

 

[Eupher]

Pourmoi,

Sorry, I misread your first post, I see now that you are not talking about a 3rd table. However, you still haven't clarified the relationship (if any) between TableA and TableB.

Ken S.

 

[Pourmoi]

Thanks Eupher,

I have a one-to-one relationship between TableA and TableB

TableA = CstCode
TableB = CustCode

I hope this helps...

Thank you.

Pourmoi

 

[willir]

What does table A capture, what does table B capture?

A 1:1: relationship is not used very often.

Your design where you are copying data from one record (table A) to antoher (table B) really suggests that your design lacks "normalization"

Why do you need to enter data into table A and then transfer it to B? Is this an audit trail.?

If you feel you need to copy the data over to table B. What are you going to do when you need to make a change to a record. You will have to change the data in both locations. Not as easily done as you may think.

Richard

 

[Pourmoi]

Hi,

TableA contains Customer information like:
AcctNumber
Company Name
Contact Name
Address
Phone

And new information will be added to subform like:
Item
Description
Price
item color
mode of payment

TableA populate the form and when new information are added through the subform, all the information ie TableA data from the form based on a selected customer + new data entered on the subform all be saved in TableB

Maybe this will help.

Thank you

 

[willir]

Pourmoi

I see the light....
Thank yu for providing specifics.

Can I / may I present your database with a slightly different slant?

You have customers (companies).
They place (sales) orders.
The order can include different items.

A typical way to approach this is to have four main tables -- one for each these "things" / "nouns" / "entities".

tblCompany
CustomerCode - text, primary key
CustomerName - text
Address - text
PrimaryPhone - text

Discussion:
These are your customers. More on this later, but include things unique to the Company. Some things to tweak later include the address and contact info.

tblProduct
ProductCode - text, primary key
ProductName - text
CurrentPrice - currency
ProductUnits - text
Active - yes / no boolean

Discussion:
A product or item table will depend greatly on what you sell. If you are selling wine, cars, insurence or a service will influence the fields used to capture the data. For example units of liters or gallons, pounds or kilograms, hours, etc -- 1000 L of wine, 1000 bottles of wine, 1000 kg of butter, 40 hrs of service or one car. The other thing is inventory in stock / on-hand. Measuring inventory is a huge discussion item. So let's keep this simple for now.

tblOrder
OrderID - primary key
CompanyCode - foreign key to tblCompany
OrderDate
ShipToAddress
ContactName
CustomerRef
SpecialInstructions - memo

tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder
ProductCode - foreign key to tblProduct
OrderQuantity - depends (interger, single...)
ItemPrice - currency

Discussion:
Again this will depend on what you sell. And you may state, well I sell cars so why do I need two tables to describe how I sell cars?? The answer is that using two tables gives you much more flexibility. With one table, you may sell a car for $20,000 and then use a huge memo field describing what the "car" includes. OR you can itemize what was sold - 1 car - $15,000, freight - $1,000, tires - $500, accessories - $2000 and taxes and discount making up the remainder.

...Moving on
In your example, TableA and TableB, you really have described the tblOrder and tblOrderDetail. Rather than "copying" data in a one-to-one relationship, you really just need to "link" one table to the other.

Instead of typing in the Company info for the tblOrder / TableA, you just need to link to tblCompany using the CompanyCode. Perhaps some test data may help -- you are selling "pens" and "stationary"
[tt]
tblCompany
CompanyCode CompanyName
CallMack Call Mack Plumbers
1stRate First Rate Builders
Maple Maple Leaf Reastaurant

tblProduct
ProductCode ProductName CurrentPrice ProductUnits
PEN_BL_EC Economy Blue Pen 1.00 each
PEN_RD_GL Gel Red Pen 3.00 each
PPR_WT_CR White paper 10.00 box
PPR_BG_PK Beige paper 5.00 package

tblOrder
OrderID CompanyCode OrderDate ...
1 CallMack 05/01/2005
2 1stRate 05/03/2005
3 Maple 05/10/2005

tblOrderDetail
OrderDetailID OrderID ProductCode OrderQuantity ItemPrice
1 1 PEN_BL_EC 5 0.90
2 1 PPR_WT_CR 2 9.00
3 2 PEN_BL_EC 10 0.85
4 2 PEN_RD_GL 5 2.85
5 2 PPR_WT_CR 2 8.00
6 2 PPR_BG_PK 5 4.00
7 3 PEN_BL_EC 10 0.90
8 3 PPR_WT_CR 5 9.50
9 3 PEN_RD_GL 3 3.00
[/tt]

Looks meaningless?
CallMack bought 5 blue pens at $0.90 each plus 2 cartons of white paper at $9.00 on May 1, 2005.

The tblOrderDetail contains links to the tblOrder and the tblProduct. Although prices in tblProduct are provided, they have been over-ridden in this example. The one thing is "how much" did CallMack pay in total?

With relational database, calculated values are normally not stored. However, since these values will not change, and we are dealing with money, storing the extended price is a common practice...

tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder
ProductCode - foreign key to tblProduct
OrderQuantity - depends (interger, single...)
ItemPrice - currency
ExtendedPrice - currency.

Notice anything unusual about the sample data?? Sales tax -- assume 5% (is that too much??)...
[tt]
tblOrderDetail
OrderDetailID OrderID ProductCode OrderQuantity ItemPrice ExtendedPrice
1 1 PEN_BL_EC 5 0.90 4.50
2 1 PPR_WT_CR 2 9.00 18.00
3 1 SalesTax 1.13
4 2 PEN_BL_EC 10 0.85 8.50
5 2 PEN_RD_GL 5 2.85 14.25
6 2 PPR_WT_CR 2 8.00 16.00
7 2 PPR_BG_PK 5 4.00 20.00
8 2 SalesTax 2.94
9 3 PEN_BL_EC 10 0.90 9.00
10 3 PPR_WT_CR 5 9.50 47.50
11 3 PEN_RD_GL 3 3.00 9.00
12 3 SalesTax 3.28
[/tt]

...Moving on
In your TableB, you have two "fields" that help in describing the "things" or "entities". "Color" describes the item being sold, and "mode of payment" decribes the terms of the sale.

Pop quizz. Where would color go -- tblProduct, tblOrder or tblOrderDetail? It could go on either tblProduct or tblOrderDetail. If the color is described in tblProduct, then it does not have to be descirbed in tblOrderDetail. And if it is part of tblOrderDetail, then it is not required in tblProduct. In the above example, selling "pens" and "paper", I made the colour part of tblProduct (indirectly). Since I "buy" blue pens and "sell" blue pens, this makes a lot of sense. If I were to manufacture pens, then I could reference the colour in either tbale.

What about mode of payment? Only one answer here -- on tblOrder. This table describes the order, and how and order is paid is part of the description. I would not list each item in the OrderDetail table as being paid by credit card, or stipulate that all orders are sold by chque in the Company table.

On May 27th, I gave you some links to some pretty good references. This current post incorporates the ideas of design. For "how to" create references please consider reviewing Defining Relationships Between tables. To get you started, from the menu, "Tools" -> "Relationships". Add your tables. Then click on the "primary key" in one table and drag it to the "foreign key" on the related table. At the popup where you can further define the relationship, I recommend that you "Enforce referential integrity".

Richard

 

[PHV]

And feel free to have a look to the Northwind sample database.

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

 

[Pourmoi]

Thanks to all of you... My database is moving in the right direction and I learned other things along the way with all your helps...... This forum is very good.
I will be in touch to let you know when it is all done and operational...

Thanks so much.

Pourmoi