How can I set one field to return data into another field?
in [Tables]
|
Prev: Asset tracking design problem using MS Access 2007
Next: Search on one field & fill in the correct matching record
From: Terri on 4 Dec 2009 11:07 OK here goes, hope this is clear I am wanting to set up a database where info is selected in one field and then multiple fields are populated. EG entering username from drop down list automatically fills in Name, Dept and Ext so save looking all those up each time. In excel I would use the lookup function and put formulae in each cell. Is this possible in access? Any help greatly appreciated. -- Thanks Terri
From: KARL DEWEY on 4 Dec 2009 11:18 No need to populate all those fields in a second table. Just enter EmployeeID using a combo sourced from Employee table and join the Employee table in your query that feeds the form. -- Build a little, test a little. "Terri" wrote: > OK here goes, hope this is clear > > I am wanting to set up a database where info is selected in one field and > then multiple fields are populated. EG entering username from drop down list > automatically fills in Name, Dept and Ext so save looking all those up each > time. > > In excel I would use the lookup function and put formulae in each cell. > Is this possible in access? Any help greatly appreciated. > -- > Thanks > Terri
From: John W. Vinson on 4 Dec 2009 13:24 On Fri, 4 Dec 2009 08:07:01 -0800, Terri <terricritchlow(a)hotmail.com(donotspam)> wrote: >OK here goes, hope this is clear > >I am wanting to set up a database where info is selected in one field and >then multiple fields are populated. EG entering username from drop down list >automatically fills in Name, Dept and Ext so save looking all those up each >time. > >In excel I would use the lookup function and put formulae in each cell. >Is this possible in access? Any help greatly appreciated. The name, dept and ext fields should simply NOT EXIST in your second table. Relational databases use the "Grandmother's Pantry Principle": "A place - ONE place! - for everything, everything in its place". These fields should exist only in the Personnel table (btw I'd use FirstName and LastName rather than just Name); you can use Queries to link other tables to the personnel table by EG, and you can use additional textboxes on a Form referencing a combo box. For instance, if the Dept is column 3 in a multicolumn combo box (even if some of the columns aren't visible) you can put a textbox on the Form with a control source =comboboxname.Column(2) The column property is zero based so this will show the third column. If you're working in Tables with Lookup fields... well, don't: http://www.mvps.org/access/lookupfields.htm -- John W. Vinson [MVP]
From: Terri on 8 Dec 2009 09:59 Thanks for your replies, I have followed what you have both said for me to do and have the form now working BUT the data is duplicated on each record ie we change record 3 and records 1 and 2 change to match record 3, have I missed something on one of the settings. Thanks again for your help. -- Thanks Terri "Terri" wrote: > OK here goes, hope this is clear > > I am wanting to set up a database where info is selected in one field and > then multiple fields are populated. EG entering username from drop down list > automatically fills in Name, Dept and Ext so save looking all those up each > time. > > In excel I would use the lookup function and put formulae in each cell. > Is this possible in access? Any help greatly appreciated. > -- > Thanks > Terri
From: BruceM via AccessMonster.com on 8 Dec 2009 10:23
If you are using unbound text boxes (or other controls) on a continuous form you would see the same value in each one, I believe. The following link gives some ideas about how to have a continuous form, and next to it the related information for the selected record: http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm In general you should not store linked information, as mentioned, but there are exceptions when information is subject to change, and you need the historical data. For instance, a person may transfer from one department to another. If the record needs to show the department at the time the record was created, you need to store the department. Terri wrote: >Thanks for your replies, I have followed what you have both said for me to do >and have the form now working BUT the data is duplicated on each record ie we >change record 3 and records 1 and 2 change to match record 3, have I missed >something on one of the settings. Thanks again for your help. >> OK here goes, hope this is clear >> >[quoted text clipped - 5 lines] >> In excel I would use the lookup function and put formulae in each cell. >> Is this possible in access? Any help greatly appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1 |