Pulling a cell from one table to another based on the 'Account Number'

[bmsrmd]

Table 1:
Account Number
Start Date
End Date
Cost data**
Budget data**

Table 2 (Imported excel file with cost/budget data):
Account Number
Cost data**
Budget data**

-------------------

Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.

So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.

Is this possible to do? Or is there an easier way of going about this?

Thanks

 

[pr2-eugin]

Could you provide a JPEG of the relationship that exists between the two tables?

Remember to check out the thread.. If you need to post a picture or database before 10 posts...

 

[bmsrmd]

I haven't setup any relationships yet between the two tables, but I made this to help explain my idea. Let me know if I can provide anymore information.

 

[pr2-eugin]

You do not need the information to be int he Table1.. This is the setup you need to look at..

Table 1:
Account Number (Primary Key)
Start Date
End Date

Table 2 (Imported excel file with cost/budget data):
exportID (Primary Key)
Account Number (Foreign Key)
Cost data
Budget data

When you need the information, all you have to do is..

SELECT table1.AccNo, table1.StDate, table1.EndDate, table2.CostData, table2.BudgetData
FROM table1 INNER JOIN table2 ON table1.AccNo = table2.AccNo_FK;

 

[bmsrmd]

Where would I put the code to inner join them?