A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How can I set one field to return data into another field?



 
 
Thread Tools Display Modes
  #1  
Old December 4th, 2009, 05:07 PM posted to microsoft.public.access.tablesdbdesign
Terri[_8_]
external usenet poster
 
Posts: 3
Default How can I set one field to return data into another field?

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
  #2  
Old December 4th, 2009, 05:18 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default How can I set one field to return data into another field?

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

  #3  
Old December 4th, 2009, 07:24 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How can I set one field to return data into another field?

On Fri, 4 Dec 2009 08:07:01 -0800, Terri
(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]
  #4  
Old December 8th, 2009, 03:59 PM posted to microsoft.public.access.tablesdbdesign
Terri[_8_]
external usenet poster
 
Posts: 3
Default How can I set one field to return data into another field?

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

  #5  
Old December 8th, 2009, 04:23 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default How can I set one field to return data into another field?

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/AlbertKal...icles/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/For...esign/200912/1

  #6  
Old December 8th, 2009, 07:26 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default How can I set one field to return data into another field?

It appears you did not bind the combo to a field.
Row Source is where the combo pull data from and Control Source is where it
is bound to store the data.
Bound Column is the field of Row Source that you store.

--
Build a little, test a little.


"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.
--
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

  #7  
Old December 8th, 2009, 08:53 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default How can I set one field to return data into another field?

I just realized I misunderstood what is going on. The text boxes would
either have combo box columns or query fields as their Control Source. I
somehow read unbound text boxes into the equation. My error.

To the OP, the article to which I linked in my previous posting is
interesting to know about, but does not seem to apply in this situation. The
other part, about storing historical data that is subject to change, could
apply, depending on the situation.

KARL DEWEY wrote:
It appears you did not bind the combo to a field.
Row Source is where the combo pull data from and Control Source is where it
is bound to store the data.
Bound Column is the field of Row Source that you store.

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

[quoted text clipped - 10 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/For...esign/200912/1

  #8  
Old December 9th, 2009, 03:12 PM posted to microsoft.public.access.tablesdbdesign
Terri[_8_]
external usenet poster
 
Posts: 3
Default How can I set one field to return data into another field?

Thanks very much, that has now solved the problem for me - I was missing
changing the bound column selection. Thanks again for the replies.
--
Thanks
Terri


"KARL DEWEY" wrote:

It appears you did not bind the combo to a field.
Row Source is where the combo pull data from and Control Source is where it
is bound to store the data.
Bound Column is the field of Row Source that you store.

--
Build a little, test a little.


"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.
--
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.