 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2006Combining Multiple Queries
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 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.  SignatureDuane Hookom MS Access MVP >I have an Access Database that runs on XP (Access 2002). There are > multiple queries that pull multiple job codes. In other words each [quoted text clipped - 13 lines] > > Bobbie 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 >I have an Access Database that runs on XP (Access 2002). There are > multiple queries that pull multiple job codes. In other words each [quoted text clipped - 13 lines] > > 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 > Bobbie > [quoted text clipped - 23 lines] > > > > Bobbie 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. > Thank you Jeff. From my limited knowledge it appeared a parameter > query wouldn't work since the job codes were complicated and a wildcard [quoted text clipped - 47 lines] > > > > > > Bobbie Make you first query a make table query and the rest append queries. > I have an Access Database that runs on XP (Access 2002). There are > multiple queries that pull multiple job codes. In other words each [quoted text clipped - 13 lines] > > Bobbie 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. >I have an Access Database that runs on XP (Access 2002). There are > multiple queries that pull multiple job codes. In other words each [quoted text clipped - 13 lines] > > 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 Bobbie > 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 [quoted text clipped - 33 lines] > > > > Bobbie 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. > Pat, > I think I have not been clear. Yes, I do know that the user is having [quoted text clipped - 53 lines] >> > >> > Bobbie |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.