Combining Multiple Queries

[Bobbie]

I have an Access Database that runs on XP (Access 2002). There are
multiple queries that pull multiple job codes. In other words each
query has multiple job codes. I'd like to take those queries and
combine into one query. Ultimately, what I'm looking for is to make a
table out of the combined query. I am not familiar with SQL.

Would anyone have any ideas on how I could get all of the query results
into one table. I got so frustrated yesterday that I just exported all
of the queries into Excel and then made a master spreadsheet and copied
all of the results from the queries into the master sheet. Once I had
that, I imported it as a table and set up my relationships and got the
information I needed. I thought to myself there has to be a better way
..

Thank you in advance for any help.

Bobbie

 

[Duane Hookom]

I'm not sure why you need multiple queries to pull multiple job codes when a
single query with a parameter/criteria might work.
I assume if query1 returns 20 records and query2 returns 11 records that you
want your final query to contain 31 records. If this is so, you can create a
union query that combines the results.

 

[Jeff Boyce]

Bobbie

How you write a query (queries) depends on how your data is structured. Can
you tell us more about what table(s) your data is being pulled from?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Make you first query a make table query and the rest append queries.

 

[Bobbie]

Thank you Jeff. From my limited knowledge it appeared a parameter
query wouldn't work since the job codes were complicated and a wildcard
was used to return the ones that were needed. There are over 25,000
employees and over 125 job codes. When the queries were made, such as
below:
Like "*T103" Or Like "*T104" Or Like "*L016" Or Like "*L365" Or Like
"*L561" Or Like "*T561" Or Like "*T675" Or Like "*T677"
Not all the job codes had a T.

I was only able to enter so many Or Likes. If I went over a certain
amount we weren't getting all the records. Which is what led to so
many queries.


The queries for the job codes all came from the same Table but now we
need to use this information with a table that has the employees name,
emp id and email address.

If a union query is what is required, I may be out of luck since I
don't know how to write one.

Thank you for your time.

Bobbie

 

[Guest]

You can build a reference table with T103, T104, L016, L365, L561, T561, etc
in it. Then include the table in your query. In the criteria use Like "*"
& [YourRefTableField] & "*" to pull all records that match the data in the
reference table.

 

[Pat Hartman\(MVP\)]

You are having this problem due to poor table design. Apparently job code
is not atomic and therefore your table violates first normal form. It is
poor practice to store multiple attributes in one field. If you cannot
change the table to break out job group or whatever it is that defines the
groups you are looking for, you can compensate by creating a new table that
lists all job codes and the group to which they belong. If a job code
belongs to more than one group, the process is more complicated and requires
a second table but I'll just assume that you have only one grouping level.

tblJobGroup
JobID (primary key, foreign key to the table you are currently extracting
the data from)
GroupID

Then when ever you want a particular group, include tblJobGroup in your
query. Join to it on JobID and select the group you want.

Group should probably be a parameter so you can specify the group at
runtime.

 

[Bobbie]

Pat,
I think I have not been clear. Yes, I do know that the user is having
problems due to poor table design, that's pretty obvious. But the
reality is trying to work with what she has done. There are not
multiple job codes in one field. There are 20,000 employees and over a
hundred job codes. Each person only has one code. I don't know what
"atomic" means and I guess if I did, I wouldn't have had to try to find
her help on this board.
There are some real good suggestions below that are truly helpful for
someone who is not an Expert at this and I'll be working at their
suggestions.

Thank you

 

[Pat Hartman\(MVP\)]

I understand that there are not multiple job codes in each field. The
problem is that pieces of the job code have meaning. This SQL - Like
"*T103" Or Like "*T104" Or Like "*L016" Or Like "*L365" Or Like
"*L561" Or Like "*T561" Or Like "*T675" Or Like "*T677"
indicates that the last four positions of job code have some meaning and you
want to group the codes for some purpose. Of course, it might be that you
are using Like when you should be using =. If job code is actually only 4
characters then you should not be using LIKE. The LIKE operator is ONLY
used when your criteria is supplying only part of a field's value. A
better, simpler syntax if complete job codes are being supplied is:

In("T103", "T104", "L016", "L365", "L561", "T561", "T675", "T677")

BTW your posted syntax is incorrect. You need to repeat the field name with
each condition:
YourField Like "*T103" Or YourField Like "*T104" Or YourField Like "*L016"
Or YourField Like "*L365" Or YourField Like "*L561" Or YourField Like
"*T561" Or YourField Like "*T675" Or YourField Like "*T677"

In the context of table normalization, atomic means not further divisible.
An example of a non-atomic field would be a name field that contained first,
middle, and last names. If you wanted to properly sort the name field, you
would have to extract the last name so you could make it the first sort
field. This can be quite difficult since last names can be several words
separated by spaces so it is hard to tell where the middle name ends or even
if there is one.

The method I suggested in my previous post will allow you to group job codes
without writing complicated SQL where clauses. A parameter query to select
a particular group would then look like:

Select ...
From YourTable Inner Join GroupingTable On YourTable.JobCode =
GroupingTable.JobCode
Where GroupingTable.JobCode = [enter the group you want this time];

Notice the absence of a long string of jobcodes. You would just enter the
group code at the prompt and the query would return only job codes in that
group.