Query confused by Lookup

[Ron in NYC]

I have a query I run on an employee ID lookup that displays the name. When I run a query some of the fields show up as the SSN, but most show up as the name. I have a sub report that is linked on that ID, but only those that come through the query as SSN, link to the report.

Why are some of the results the name, and some the ID? Help.

 

[CJ_London]

for an answer in line with the level of detail provided - anyone's guess, perhaps the name field is actually populated with SSN

for a more focussed answer, please provide the code you are actually using (not an edited version), example data with a before and after to demonstrate the problem

 

[Ron in NYC]

I'm not writing code.

I have a table storing emplyee ID, work date, and hours worked. The employee ID is a lookup field that looks up the name from a Roster and is supposed to display the name and store the SSN. On some it displays the name, and some the employee ID.

All of the records in the Roster had an Employee ID and name fields.

 

[CJ_London]

so what is your query if it is not code?

And if you are using a lookup field in your table design, change it back to an ordinary field - it is probably the cause of your problem

 

[jdraw]

See this thread for recent dialogue regarding Lookup

 

[Ron in NYC]

I've been designing databases for decades without "writing code." I took a VBA course once a long time ago, but didn't use it enough to make it stick. Access writes the code, I presume.

 

[The_Doc_Man]

The question is what happens when there is a problem with the SSN or other ID such that it isn't in the lookup table. I think that is the answer to your question.

Ron, there are more types of code than VBA. If you are writing SQL then that is also code in a 4th generation language. However, I digress.

I might have written this in two parts, with an employee table for which an employee ID is listed as the PK of the table - and SSN is generally a poor choice. It might be right for your case but technically, not everyone has an SSN. But if they work for your company, then everyone will have an employee number.

Anyway, if you have the Employee ID number field in the Employee table, you can write JOIN queries that will contain all the data you want and do that lookup automagically anyway. You can do this ENTIRELY through the query-builder grid. A lookup FIELD is trouble waiting to happen. A JOIN for your lookup is ALMOST a no-brainer and keeps your database simpler (in my personal opinion.)