Pull Data From Multiple Tables ????
in [Tables]
|
Prev: Linking First & Last Names in a table to a Full Name field in anot
Next: help designing query to get results
From: malik via AccessMonster.com on 29 Dec 2009 15:14 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.. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1
From: BruceM via AccessMonster.com on 30 Dec 2009 10:47 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? malik wrote: >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.. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1
From: Armen Stein on 31 Dec 2009 10:35
On Tue, 29 Dec 2009 20:14:15 GMT, "malik via AccessMonster.com" <u49026(a)uwe> wrote: >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 |