Joining list from (2) Columns in Query to existing table (1) colum

[netadmin]

I have some imported information on Eployee Jobs. The Primary table has
imported information in this format;

Clock # Name JobName1 JobName2 JobName3
xxxxx John Doe Job1 job3

I created some queries from the Primary table to define a list of who is
qualified for certain jobs.

There are 32 jobs

 

[netadmin]

Opps! Hit the worng key

I have some imported information on Eployee Jobs. The Primary table has
imported information in this format; The X's are what were specified in the
Primary table, But I changed them to be the job name instaed of an X thinking
that would be better.

Clock # Name JobName1 JobName2 JobName3
xxxxx John Doe x
xxxxx Dan S. x

I created some queries from the Primary table to define a list of who is
qualified for certain jobs.

There are 32 jobs that I created a seperate query for who quailfies for
that position, which I also added some of this information to the Primary
table, ie; (seperated names).

I also added a column called Jobs to the primary table;

Jobs Clock# Name JobName1 JobName2 JobName3
Grinder xxxxx John Doe Grinder Sander
Mechanic

How can I create a query/table to display a list of jobs that includes the
Jobs Column & the JobName1 in (1) colmn?

I can do (2) seperate queries to get the information, can I jion this
informatio some how?

I created a Form from the Primary table for data entry of Employee Job and
would like to get the 'job' data from the Imported information and add it to
the new data selected for the Job or Jobs Field. Is this a union query, but
with one table? I tried it and it didn't work.

 

[Tom Ellison]

Dear Net:

You seem to have fallen off the edge of the earth in the middle of posting.

The structure of the data as imported is faulty for the purpose of creating
a database. You should transform it into something more usable.

Please consider having something built like this:

Employee (Table)
ClockNo
EName

Job (Table)
JobName

EmployeeJob (junction table)
ClockNo
Sequence
JobName

For your fragmentary information given, you would have this in the tables:

Employee
ClockNo EName
xxxxx John DOe

Job
JobName
Job1
job3

EmployeeJob
ClockNo Sequence JobName
xxxxx 1 Job1
xxxxx 2 job3

You can produce this from the table you have with queries:

SELECT DISTINCT [Clock #], [Name] FROM Primary

SELECT DISTINCT JobName FROM (
SELECT JobName1 FROM Primary WHERE JobName1 IS NOT NULL
UNION ALL
SELECT JobName2 FROM Primary WHERE JobName2 IS NOT NULL
UNION ALL
SELECT JobName3 FROM Primary WHERE JobName3 IS NOT NULL) X

SELECT [Clock #], 1 AS Sequence, JobName1
FROM Primary
WHERE JobName1 IS NOT NULL
UNION ALL
SELECT [Clock #], 2 AS Sequence, JobName2
FROM Primary
WHERE JobName2 IS NOT NULL
UNION ALL
SELECT [Clock #], 3 AS Sequence, JobName3
FROM Primary
WHERE JobName3 IS NOT NULL

After testing, you can convert these 3 queries into append queries into the
3 respective tables.

The above will work only once, when the tables are empty. Afterward, the
first two queries would need to eliminate any rows already recorded and
append only what is left. The third one would take some study to be able to
determine what is best.

Tom Ellison

 

[netadmin]

Tom,

I would like to thank you fot the information & time you've given me in this
question. I am not very experienced in using SQL, but was able to get it
another way.

What I did was by column, changed the X's to the respective job name. I then
created a query to extract the information from the Excel file, in the format
of the Primary Table, I then hid the columns in the primary table that were
not included in the Query and pasted this information in the Table, one "job"
query at a time. Since it's a very simple database at this time, (1) table of
information, it worked for what I need.

Thank You again!

Kelly

Dear Net:

You seem to have fallen off the edge of the earth in the middle of posting.

The structure of the data as imported is faulty for the purpose of creating
a database. You should transform it into something more usable.

Please consider having something built like this:

Employee (Table)
ClockNo
EName

Job (Table)
JobName

EmployeeJob (junction table)
ClockNo
Sequence
JobName

For your fragmentary information given, you would have this in the tables:

Employee
ClockNo EName
xxxxx John DOe

Job
JobName
Job1
job3

EmployeeJob
ClockNo Sequence JobName
xxxxx 1 Job1
xxxxx 2 job3

You can produce this from the table you have with queries:

SELECT DISTINCT [Clock #], [Name] FROM Primary

SELECT DISTINCT JobName FROM (
SELECT JobName1 FROM Primary WHERE JobName1 IS NOT NULL
UNION ALL
SELECT JobName2 FROM Primary WHERE JobName2 IS NOT NULL
UNION ALL
SELECT JobName3 FROM Primary WHERE JobName3 IS NOT NULL) X

SELECT [Clock #], 1 AS Sequence, JobName1
FROM Primary
WHERE JobName1 IS NOT NULL
UNION ALL
SELECT [Clock #], 2 AS Sequence, JobName2
FROM Primary
WHERE JobName2 IS NOT NULL
UNION ALL
SELECT [Clock #], 3 AS Sequence, JobName3
FROM Primary
WHERE JobName3 IS NOT NULL

After testing, you can convert these 3 queries into append queries into the
3 respective tables.

The above will work only once, when the tables are empty. Afterward, the
first two queries would need to eliminate any rows already recorded and
append only what is left. The third one would take some study to be able to
determine what is best.

Tom Ellison

I have some imported information on Eployee Jobs. The Primary table has
imported information in this format;

Clock # Name JobName1 JobName2 JobName3
xxxxx John Doe Job1 job3

I created some queries from the Primary table to define a list of who is
qualified for certain jobs.

There are 32 jobs