Vlookup equivalent in Access???? (1 Viewer)

[ewong]

Hi all,

I am currently working on an Access project.

I have 2 tables:
Table 1 has 10 fields: Account ID, Employee ID, etc
Table 2 has 3 fields: Account ID, Employee ID, Commission Date

Both tables have the same Account ID and Employee ID information.

What I want to do is I want to pull Commission Date information from Table 2 and put it on Table 1. The data on Commission Date will vary depending on Account ID and Employee ID.

If this is in Excel, I can always use vlookup. But I am currently on Access and I have never used Access before.

I would also need to do this every month so I think I would need to use a type of programming language (like a macro) that would automate this.

Since I am VERY new on Access and even more on programming language, I would appreciate it if you guys could provide me with a detailed step by step guidance

Thanks for your help!

 

[Beetle]

What I want to do is I want to pull Commission Date information from Table 2 and put it on Table 1.

By "put it on Table 1" I'm guessing that you mean you want to store this data in Table 1. In most cases it is inadvisable to redundantly store the same data in two different tables. The Commission Date data already exists in Table 2 so just retrieve it from there whenever you need it.

If this is in Excel, I can always use vlookup. But I am currently on Access and I have never used Access before.

I would also need to do this every month so I think I would need to use a type of programming language (like a macro) that would automate this.

You don't necessarily need any functions or code for this at all. Usually when you want to return data from two or more tables, you join those tables in a query and return the necessary fields. You can then filter the query by date ranges, etc. to get only the records you want for a given time frame. The filtering is usually done via a form control or a parameter prompt.

 

[ewong]

how do you join tables in a query? and do i need to join the matching fields using the "join category" feature?

By "put it on Table 1" I'm guessing that you mean you want to store this data in Table 1. In most cases it is inadvisable to redundantly store the same data in two different tables. The Commission Date data already exists in Table 2 so just retrieve it from there whenever you need it.



You don't necessarily need any functions or code for this at all. Usually when you want to return data from two or more tables, you join those tables in a query and return the necessary fields. You can then filter the query by date ranges, etc. to get only the records you want for a given time frame. The filtering is usually done via a form control or a parameter prompt.

 

[Beetle]

Open the query designer and add the relevant tables to the grid. If there is already a relationship established between the tables then that relationship will automatically appear in the design grid. If not you can create a relationship for the query simply by dragging field(s) from one table to the other.

 

[jdraw]

Double post in AWF

http://www.access-programmers.co.uk/forums/showthread.php?t=232398