Pull Data From Multiple Tables ????

[malik via AccessMonster.com]

Hi
I will have 4 tables name "TblCostomers","TblVendors","TblAccounts",
TblExpenses"
Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in
source.

This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName"

If User enters a Account No., It pulls the Account Name from Any One of these
Table.

I can do this if I have only one table.
But tell me how can i do it while I have 4 tables for One Field of a table

Thank you..

 

[BruceM via AccessMonster.com]

By the lack of responses I suspect I am not the only one who does not
understand your question. If the form is based on tblDrVouchers, what do the
other tables have to do with anything? How do you "pull" the account name
into the text box? How are the tables related?

 

[Armen Stein]

I will have 4 tables name "TblCostomers","TblVendors","TblAccounts",
TblExpenses"
Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in
source.

This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName"

If User enters a Account No., It pulls the Account Name from Any One of these
Table.

I can do this if I have only one table.
But tell me how can i do it while I have 4 tables for One Field of a table

It sounds like you might have a poorly designed database structure.
You seem to be saying that "accounts" could be in four different
tables, but one of them is called "tblAccounts".

But, to answer your question: You could use a Union All query to
"vertically join" your 4 tables together, then use that query to look
up your textbox values. To be accurate, AccountNo needs to be unique
across all 4 tables. This approach might work, but I'm concerned that
it's just masking your design problem.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com