Using Query to link tables

[KristenD]

I am working on creating a query to base a form on. I've done it several times in this database, but today, for some reason I cannot figure out how to do it.

I believe this is how I did it before but it looks like the SQL statement got wonky:

SELECT tblEmpInfo.EmpInfoID, tblEmpInfo.EmpIDFK, tblEmpInfo.JobNumberFK, tblEmpInfo.EmpType AS Expr1, tblEmpInfo.CraftCode AS Expr2, tblJobs.JobSite
FROM tblJobs INNER JOIN tblEmpInfo ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;

Basically I have a look up table and all I'm doing is making it so I don't see the Cert ID it just pops up with the description. Am I over thinking this doing it wrong??

 

[boblarson]

Normally, unless it is a 1 to 1 relationship, you should have a single table for your form's record source. You would have a main form with the one side of a one to many or many to many. And then you would have a subform with the other table in it there.

It looks like you are trying to include lookup fields which you should not be doing. It looks like you should be having tblJobs as the form's record source and then the lookup table (tblEmpInfo) would be used in row sources of combo's or list boxes on your form.

 

[KristenD]

I do have a single table for the record source. The SQL statement I posted is similar to what I want to create for the certifications.

I have a tblCertifications and then tblEmpCert. I have created lookup tables several times for this database (mostly one to many relationships) but I am having a difficult time (brain spasm or something) trying to figure out how to do it again. I have looked through all the SQL statements and design views and looked at the property sheets to try and remember how I did it and it is looking like Greek to me and I just cannot compute it right now!

I am getting frustrated as I did it before and for some reason understanding and doing it are completely eluding me right now. :banghead: