VLOOKUP EQUIVALENT OR RELATIONSHIP

[stevembe]

Very much a newbie so do bear with me. I have built adatabase with two tables where both have a field labelled “Employee ID”. Thesecond table contains Employee ID, Surname, First Name, Age, etc and what Iwant to achieve is auto populating table one with all this detail when theEmployee ID is typed in. I am thinking of a Excel Vlookup equivalent, perhaps arelationship. Obviously not really sure so very much grateful for any advice.

[HiTechCoach]

Very much a newbie so do bear with me. I have built a database with two tables where both have a field labelled “Employee ID”. The second table contains Employee ID, Surname, First Name, Age, etc and what I want to achieve is auto populating table one with all this detail when the Employee ID is typed in.

Not sure what you mean by "auto populating table". In a well design database all information is stored in only one place. In you table that has the Employee ID” as a foreign key should not also have the Employee information. It is looked up for the Employee table as needed.

There as several was to look it as needed.

For reports it is common to use a query that has both tables joined on employee ID. You select the desired fields you need for the report.

For data entry you would use a form.

To show the data form the related employee table I commonly use a combo box with one of these two methods:

In your case the combo box would be used to to select the employee

1) text boxes link ed to combo box. Great for one or two fields from the related table.

or

2) sub form linked to combo box. Best for when you need to show more that a few fields from the related table. I use this a lot.

Note: Access is a relation database that uses SQL. The key to success is to learn to create queries. and work with recordsets. This is not something can do in a spreadsheet. In general the best way to do stuff in a database is nothing like how you program it in a spreadsheet.


=========================================================================== =============================================
Note: This was cross posted here: http://www.mrexcel.com/forum/microsoft-access/890562-vlookup-equivalent-relationship.html

stevembe, please read http://www.excelguru.ca/content.php?184

[Johann]

I have to agree with HiTechCoach. It is certainly possible to do exactly what you asked, but I'm pretty sure that's not what you ultimately want. (I'm sure my answer annoys you, so the reason why I don't simply answer your question is because the amount of time you'll spend to duplicate what I suggest will frustrate you and leave you at square one.)

Please tell us what you want to achieve ultimately and we'll see if we can guide you along the way.