Referencing fields and values

[kman]

Example I want to reference the values of lname in table1 to lastnames in table2 without creating a query. Is there a way I can do this in table properties?

 

[Parra]

I don't think you could do such a thing.

Why would'nt you want to create a query?

 

[Russell Hauf]

Do you want to do this for data entry? You can use a field "lookup".

Before I get into that, can you explain what you want to do in a little more detail (what is your desired end-result)?

Thank you,

Russell

 

[kman]

Further detail.

Yes I do want to use this for data entry purposes.

I want the table2 lastnames to always contain the latest values of lname in table1. So as soon as data as new data is inserted into lname table1 it will autopopulate the values into lastnames in table2. Russell if you need me to clarify further please let me know I appreciate your help.

Can you explain how to implement the vlookup in Access if it would be of value for my table?

 

[Anne Troy]

Generally, when I create a database, I have a bunch of *lookup* tables, and then a main records table. Suppose I want to do payroll. I don't want every piece of employee information to show on every pay record, so I just use a lookup from the payments table to the employees table, probably storing the employee ID field and showing the employee's last name.

To show an entire list of payments along with the employee's info would then require the use of a query and a report...

If you're not doing it this way then, IMHO, you are messing up the normalization of your database---duplicating data---and not following good database design.

 

[Anne Troy]

Sorry. I realize now that it might help to tell you how to *lookup*.

When I'm making my *main* table--the one I'll use the most--I create a field called (using the example I posted above) EmpID. It will NOT be my primary key in the main table, but it would be the primary key in the Employee table.

So I make this field in my main table. The field type is LOOKUP. The wizard kicks in and you'll want to "lookup the value in another table", then you'll choose the EmpID field and the Lname or something, hit Next. It'll want to hide the EmpId, and that's cool. Then it'll ask which one you want to *store* (maybe it'll ask) and you want to store the EmpID. So, in effect, when you create a record, your dropdown will show the last name, but the DB will store the EmpID. This will also create a relationship between EmpID in your employee table and EmpID in your main table--you won't need to do that manually.

 

[Corticus]

Wouldn't a simple select query accomplish this.

You could just have the EmpID from table1, and lname from table2 as your two fields, joined by EmpID. If you really need a table with tis data, change the query type to a 'make-table' query. If you only allow form access to the lname table(I'm trying hard to imagine how your data is organized, but you might have to flip around what I'm saying), you could trigger the make-table with an even on the form, like a button that goes to the next record.

Oh, and Dreamboat's solution sounds cool, too...

HTH,
Corticus