Populate two columns with one entry - Is this possible? 1

[travelfurther]

Hi,
I don't know if this is possible or maybe it's simple?

Say I have a table called 'employee' with their 'name' in one column and their 'phone number' in another.

Then I have a master table that I call 'staff.' I have a number of columns, but amongst those I have two that are called 'name' and 'phone number' as in the 1st table.

I want to use the 'employee' table as a lookup table for the master, 'staff' table.

Question is:
In the master table, under the 'name' column, when I do a lookup to 'staff' and choose an employee from a dropdown list, is there any way to also populate the adjoining 'phone number' column automatically, based on the data that is already in the 'employee' table????


Does this make sense??

 

[JeremyNYC]

actually, it doesn't make too much sense, as a design plan. Check out the "Fundamentals of Relational Database Design" article on my website, and you'll see why. There's a whole world of "Data Normalization" for you to learn about, and it's gonna be a fun ride.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[GDGarth]

Good point, Jeremy - you get a star.

While it's possible to populate the other table, why bother when you already have the information you need?

As a general rule, I try to avoid having the same information in more than one spot in a database. That way, if I change it, I don't have to worry about changing it in multiple places.

 

[travelfurther]

Thanks for the replies... I kinda see what your saying makes sense. I guess the fuller info I would like to see can be gathered in a query or something?
Back to the drawing board....

 

[idd]

Jeremy,

I have read your article previously, and I must say, that I liked it very much. It was a very informative and worthwhile read. I wanted to express my gratitude for your most excellent written work in some way but wasn't sure which website I got it from.

I guess the bottom line is Thanx

Iddris

 

[travelfurther]

Further to the above, I've given up on trying to duplicate too much info as suggested. Some sort of query is what I assume i need. But i can't get my head around it..

For simplistics sake, lets say this is what i want to do:
table1 = master
table2 = users

'master' has 'floor','extension','pc','username'
'users' has 'username','workgroup','cellphoneNumber'

when working on the 'master' table, 'username' column I have that lookup to
'username' in 'users.'

I now wish to make a query to display all columns in both tables based on the common 'username.'
e.g.
I want to show 'floor','extension','pc','username','workgroup','cellphoneNumber' for all rows
that have entries.

(I have related the username in users to username in master.)

I just can't seem to get anything to work. What kind of query do I need to do??
a joining, union, autoupdate...? can't make anything work and surely it's not that difficult..

 

[GDGarth]

The easiest way is to use the Query Wizard, and let it create the query for you. Just select all the fields from each table that you want to include, and it will set it up for you.

Once it's created, it's pretty easy to modify it if you want to change something.

 

[JeremyNYC]

Iddris,

Hmm. I posted earlier but I don't see it here. In any case, I'm glad the article was helpful to you, but I didn't write it. It was written by Paul Litwin, one of the authors of the Access [version number] Developer's Handbook series--the best books on Access I've seen.

To the original poster:
The query wizard should certainly be able to make this work for you. I would suggest you not use the lookup feature in Access tables. When you want to look at your data that way you can just create a query (or save it so it's always around). I think that feature puts too much distance between you and what your data really are, and it encourages you to use your tables for data entry, which is a big no no--that's what the forms are for.

Also, a bunch of the names you've used for your fields are Access reserved words, and should probably get changed. That said, assuming this list is accurate:
'master' has 'floor','extension','pc','username'
'users' has 'username','workgroup','cellphoneNumber'

The query you'll want will look like this:
SELECT floor, extension, pc, users.username, workgroup, cellphonenumber FROM master LEFT JOIN users on master.username = users.username

You can create a new query, not add any tables to it, and choose view|SQL and then paste this in there. You'll get all records in master and all the records in users that match those records.

Hope this helps.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[travelfurther]

I know it should be simple, but NO joy.

Here is my *actual* sql:
SELECT [Phone System].Extension, [Phone System].Floor, [Phone System].UserName, Users.UserGroup, Users.UserMobile
FROM Users INNER JOIN [Phone System] ON Users.UserName = [Phone System].UserName;

If I now run that, the query comes up *totally* blank.
If I change it to a LEFT JOIN, I get these populated { Users.UserGroup, Users.UserMobile }
but none of the [Phone System] entries.
If I change it to a RIGHT JOIN, I get it the other way around, all the [Phone System] entries but not the other side!

What am i doing wrong??

 

[jpa]

Chances are you've defined your relationships between the two tables incorrectly. Try deleting the relationship you set up between the two (in the relationships window, not the query) then run the query again as the INNER JOIN you listed.

If that doesn't work, make sure there is an Index on the username fields in each table where the username is set as unique (if you make username the primary key in both, Access will do this for you).

If you still can't get it working, consider moving all of the fields to a single "Contacts" table.