Access SQL syntax Reference other tables

[Lorlai]

I am very very new to Access, and am trying to figure out the best way to create my database. I have experience in SQL, and find that much easier than the Access UI. I have two tables (Details and Manager) and am trying to create a foreign key reference so that these two can be together. For example, the Details Table looks like this:



ProductID ManagerName OfficeNum OfficeAddress
234 Sally Smith 56 123 Fake St
789 Bob McBob 02 987 Cool Rd

*I know this is bad db formating; the data is being pulled in from an external reporting system and is easier to keep in this format.*

I want my manager table to take the ProductID and ManagerName from the details table, like so:

ManagerID ManagerName ProductID GroupName
1 Sally Smith 234 Paper
5 Bob McBob 789 Pens

I'm having big problems in trying to figure out how to join these (I would prefer to do it SQL). Does anyone know how to quickly do this? Any help is greatly appreciated!

[pbaldy]

SELECT...
FROM Details LEFT JOIN Managers ON Details.ManagerID = Managers.ManagerID

LEFT could be INNER or RIGHT, as appropriate to the situation.

[Lorlai]

Perfect! Thank you so much for the help. One more question: Is there a way to get the field to auto fill? Like if I had all the data in the details field, and wanted to get the matching fields in the Manager table filled with the appropriate data?

[pbaldy]

Typically you would not store anything other than the key (ManagerID) in the other table. I can't think of an automatic way to populate the table. You'd need to do it with an update query or something like this in a form:

http://www.baldyweb.com/Autofill.htm