Query to complex need help

[nitrotech89]

Hi Have a table I'm importing from that has job codes these job codes need to be changed to the job description

I tried IIF Statement but I have 18 and Access can only handle 10

so I setup a second table with the the jobcode and descriptions

How do I take the imported table jobe code and refernce my second table to give me the description thanks

 

[dhookom]

nitrotech89,
Welcome to Tek-Tips. You can create a query based on your imported table and your second table. Create a join by dragging the jobcode field from one to the other. You can then select all of the required fields into the query grid.

Duane
Hook'D on Access
MS Access MVP

 

[nitrotech89]

Thanks

I was on the right track then I did add the second table.

How would this query look

First table is called Payroll Second table is Departments

I pull the Jobclass from the payroll file and it is called the number in the Departments table and what I would like is the to call is the dept in my departments table. this will give me my description to out put for my file thanks

 

[dhookom]

There is a description of a query with more than one table at creating Access Queries. Check about 2/3 of the way down the page.

Duane
Hook'D on Access
MS Access MVP

 

[nitrotech89]

Thanks

Sorry for not following that but not sure how it refeneces the to numbers to get me my description?

For Example when jobclass=number then it should display whats in the departments table dept field?

Does that make sense?

 

[dhookom]

Can you provide the exact table and field names as well as the common fields between the two tables?

Duane
Hook'D on Access
MS Access MVP

 

[nitrotech89]

Sure thanks

First table is: Payroll
2nd Table is: Departments

The payroll table is the imported table, the field I'm looking at is jobclass
The second table has 2 fields number and Dept

the number is the same as jobClass

the dept is is the department description?

so when the import table comes in it will query jobclass and give me a number which is the same number as number in the Department table then it should give me the description which I will append to a diffrent table for an output file.

Hope this is clear thanks

 

[Andrzejek]

Try something like this:[tt]

SELECT P.*, D.*
From Payroll P, Departments D
WHERE P.jobclass = D.[number]
ORDER BY ...
[/tt]

BTW - [tt]number[/tt] is a bad idea for a field name. It is a reserved word.

Have fun.

---- Andy

 

[dhookom]

I typically use joins and the aliasing of the table names might be confusing.

SELECT Payroll.*, Departments.*
From Payroll JOIN Departments ON Payroll.jobclass = Departments.[number]

Duane
Hook'D on Access
MS Access MVP

 

[nitrotech89]

Ok I following your join there and I see how you are now getting the jobclass and number to equal
now how does it pull the description from that line so I can append it to my new table?

Thanks

 

[Andrzejek]

If you try any of the examples you have been given, you will end up with all fields from both Payroll and Departments tables. One of the fields has to be 'description'


Have fun.

---- Andy

 

[dhookom]

In relational databases, you don't want the description value stored anywhere other than the one table where it is currently residing. Use a query to display the description value with other tables.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I should have also mentioned the field Departments.[number] should be the primary key in the Departments table.

Duane
Hook'D on Access
MS Access MVP