Pull Data to TxtBox Control From One Of Many Tables

[malik via AccessMonster.com]

Hi
I have 5 tables and each have two field "PartyId" And "PartyName" common.

Now i have two TxtBox Controls on it Name "TxtPartyId" And "TxtPartyName".

I want that if a user enters a Party ID in TxtPartyId, then the Code look
these 5 Tables One By One And Pull the PartyName in TxtPartyName where it
found.

Note: All 5 tables will have seperate Party Id and there will not be any
duplication of any Party id, in any table. (One Party Code will be assign to
the Only a single Party.)

Thank you

 

[Jeff Boyce]

Malik

If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps
your data would benefit from a bit more normalization.

Having an ID field in common is ... common. Having a ...name field shared
among tables is ... a spreadsheet!

Please post the table structure (fieldnames, examples of data) if you want
folks here to offer more specific suggestions.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[malik via AccessMonster.com]

thanks 4 the response.
I have following 5 Tables.
1. Tblcustomer
Partyid
Party Name
Address
Phone
Fax
email
Isvendor(yes/no)field
Isbroker(yes/no)field

2. Tblvendors
Partyid
Party Name
Address
Phone
Fax
email
Iscustomer(yes/no)field
Isbroker(yes/no)field

3. Tblbrokers
Partyid
Party Name
Address
Phone
Fax
email
Isvendor(yes/no)field
Iscustomer(yes/no)field

4. TblOtherParties
Partyid
Party Name
Address
Phone
Fax
email

Now i have two of some other tables
1. TblDrvoucher
Drvoucherno
Drdate
Isposted(yes/no)

2.TblDrvoucherBndy
SerialNo
Drvoucherno (has a relation with TblDrvochers.Drvoucherno.)
Accountno (** Now this field is going to use first 4 tables. Party id field
of first 4 tables will have relation with this field).
DrAmount
Narration
.................................

Now i want to pull party name if user enters pary id.

If there is a need of normalization, please guide me in brief.

Thank u.

Malik

If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps
your data would benefit from a bit more normalization.

Having an ID field in common is ... common. Having a ...name field shared
among tables is ... a spreadsheet!

Please post the table structure (fieldnames, examples of data) if you want
folks here to offer more specific suggestions.

Hi
I have 5 tables and each have two field "PartyId" And "PartyName" common.

[quoted text clipped - 11 lines]

Thank you

 

[Jeff Boyce]

To start with, since your tblCustomer has [PartyID] and [PartyName], storing
[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther) is
unnecessary and exposes your database to data integrity issues. For
example, what happens if someone changes his/her name? You make the change
in tblCustomer ... but how does that change make it into all the other
places?!

Far simpler is to store it once, in the tblCustomer table, then only use the
PartyID (?CustomerID?) as needed in the other tables.

Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY
similar to tblCustomer. I suspect you could further normalize your data
structure by using a SINGLE table for person-related info (i.e., name,
address, phone...), and a PersonID (not PartyID, not CustomerID, not
VendorID, ...).

I have no way to know what situation you are attempting to model. Can a
person be a customer, a vendor, a broker, AND an other, all at the same
time? Without knowing the relationships, it's hard to offer specific
suggestions.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

thanks 4 the response.
I have following 5 Tables.
1. Tblcustomer
Partyid
Party Name
Address
Phone
Fax
email
Isvendor(yes/no)field
Isbroker(yes/no)field

2. Tblvendors
Partyid
Party Name
Address
Phone
Fax
email
Iscustomer(yes/no)field
Isbroker(yes/no)field

3. Tblbrokers
Partyid
Party Name
Address
Phone
Fax
email
Isvendor(yes/no)field
Iscustomer(yes/no)field

4. TblOtherParties
Partyid
Party Name
Address
Phone
Fax
email

Now i have two of some other tables
1. TblDrvoucher
Drvoucherno
Drdate
Isposted(yes/no)

2.TblDrvoucherBndy
SerialNo
Drvoucherno (has a relation with TblDrvochers.Drvoucherno.)
Accountno (** Now this field is going to use first 4 tables. Party id
field
of first 4 tables will have relation with this field).
DrAmount
Narration
................................

Now i want to pull party name if user enters pary id.

If there is a need of normalization, please guide me in brief.

Thank u.

Malik

If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps
your data would benefit from a bit more normalization.

Having an ID field in common is ... common. Having a ...name field shared
among tables is ... a spreadsheet!

Please post the table structure (fieldnames, examples of data) if you want
folks here to offer more specific suggestions.

Hi
I have 5 tables and each have two field "PartyId" And "PartyName"
common.

[quoted text clipped - 11 lines]

Thank you

 

[John W. Vinson]

Hi
I have 5 tables and each have two field "PartyId" And "PartyName" common.

Then you have at least four misdesigned tables. Relational databases use the
"Grandmother's Pantry Principle": "A place for everything - ONE place! - and
everything in its place".

The PartyName should exist in the table for which PartyID is the Primary Key,
*and noplace else*. Storing the partyname redundantly in all five tables
wastes space, and much more importantly, risks update anomalies, where the
same partyID has different partyNames in different tables.

Now i have two TxtBox Controls on it Name "TxtPartyId" And "TxtPartyName".

I want that if a user enters a Party ID in TxtPartyId, then the Code look
these 5 Tables One By One And Pull the PartyName in TxtPartyName where it
found.

Note: All 5 tables will have seperate Party Id and there will not be any
duplication of any Party id, in any table. (One Party Code will be assign to
the Only a single Party.)

You need a master Parties table with PartyID as the primary key and PartyName
as the (only?) other field.

You can display the partyname on your form by using a DLookUp or a Combo Box
displaying both fields; with the combo box solution, you can use a textbox
with a control source like

=comboParty.Column(1)

to display the second column (it's zero based) of the combo.

 

[malik via AccessMonster.com]

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....) and will use Party Id on other Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox will
confirm him that he has typed the desired Party Id correctly.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field
BuT
The TxtPartyName has source [PartyName] From "TblParties" just for display
the name.

Now where the Customer, Vendor, Broker concern.

A customer can be a Vendor and a vendor can become a Broker after some time.


Ur suggestion are very helpful for me.

thank u.

To start with, since your tblCustomer has [PartyID] and [PartyName], storing
[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther) is
unnecessary and exposes your database to data integrity issues. For
example, what happens if someone changes his/her name? You make the change
in tblCustomer ... but how does that change make it into all the other
places?!

Far simpler is to store it once, in the tblCustomer table, then only use the
PartyID (?CustomerID?) as needed in the other tables.

Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY
similar to tblCustomer. I suspect you could further normalize your data
structure by using a SINGLE table for person-related info (i.e., name,
address, phone...), and a PersonID (not PartyID, not CustomerID, not
VendorID, ...).

I have no way to know what situation you are attempting to model. Can a
person be a customer, a vendor, a broker, AND an other, all at the same
time? Without knowing the relationships, it's hard to offer specific
suggestions.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

thanks 4 the response.
I have following 5 Tables.

[quoted text clipped - 75 lines]

 

[malik via AccessMonster.com]

***Jeff ****

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....) and will use Party Id on other Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox will
confirm him that he has typed the desired Party Id correctly.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field
BuT
The TxtPartyName has source [PartyName] From "TblParties" just for display
the name.

Now where the Customer, Vendor, Broker concern.

A customer can be a Vendor and a vendor can become a Broker after some time.

Ur suggestion are very helpful for me.

thank u.

 

[Jeff Boyce]

It sounds like you've decided to keep your current structure. I'll mention
again that the design appears not to be well-normalized, based on what I've
interpreted from your descriptions.

If your design is not well-normalized, you'll find that both you and Access
will have to work overtime to overcome a data design for which Access is NOT
optimized.

.... and I still don't understand the inter-relationships among Customers,
Vendors, Brokers and Others ...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....) and will use Party Id on other
Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox
will
confirm him that he has typed the desired Party Id correctly.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field
BuT
The TxtPartyName has source [PartyName] From "TblParties" just for
display
the name.

Now where the Customer, Vendor, Broker concern.

A customer can be a Vendor and a vendor can become a Broker after some
time.


Ur suggestion are very helpful for me.

thank u.

To start with, since your tblCustomer has [PartyID] and [PartyName],
storing
[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther)
is
unnecessary and exposes your database to data integrity issues. For
example, what happens if someone changes his/her name? You make the
change
in tblCustomer ... but how does that change make it into all the other
places?!

Far simpler is to store it once, in the tblCustomer table, then only use
the
PartyID (?CustomerID?) as needed in the other tables.

Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY
similar to tblCustomer. I suspect you could further normalize your data
structure by using a SINGLE table for person-related info (i.e., name,
address, phone...), and a PersonID (not PartyID, not CustomerID, not
VendorID, ...).

I have no way to know what situation you are attempting to model. Can a
person be a customer, a vendor, a broker, AND an other, all at the same
time? Without knowing the relationships, it's hard to offer specific
suggestions.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

thanks 4 the response.
I have following 5 Tables.

[quoted text clipped - 75 lines]

Thank you

 

[John W. Vinson]

***Jeff ****

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....) and will use Party Id on other Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox will
confirm him that he has typed the desired Party Id correctly.

You need to *DISPLAY* the PartyName.

That does not mean that you need to *STORE* the party name.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field
BuT
The TxtPartyName has source [PartyName] From "TblParties" just for display
the name.

Use a Combo Box with a RowSource like

SELECT PartyID, PartyName FROM tblParties ORDER BY PartyName;

and a control source of AccountNo.

On the form, put a textbox with a Control Source of

=comboboxname.Column(1)

This will allow the user to select a valid PartyID, and display the
corresponding party name, *without* any need to store the party name
redundantly in a second table.