Union Query in ACCESS

[jmw229]

Hi Guys,

I'm new to SQL and just know the basics. My boss has asked me to help him with a new system they are developing using MS. Access.

In the form, the user enters their information as well as checks the applicable check boxes. When they have completed the form, they click the 'Generate Match Report' button and it matches the programs the user is eligible for based on what the user has input. I've looked at numerous union query examples, however, I haven't been able to figure out exactly how to do this.

For example:

Form:
- The client enters their age and employment status
Client Age
Employment Status
Preferred Location (if any)

The criteria is matched up with the services/programs provided.

Some guidance and/or assistance would be greatly appreciated.

Thanks,

Jay

[timothybard]

I need additional details to give you precise help, such as what tables should the search be performed on and what are the relevant fields in the tables.

However, just a general comment... UNION queries are used to return results from tables that have similar data in common. Are you searching for results from multiple tables? For example, two tables may be clients and employees; they have fields in common such as First Name and Last Name. A UNION query would allow you to combine those tables so you just have a long list of both clients and employees.

Secondly, if all your data is in one table and you just need to search that single table for matching criteria, then you just need to use a WHERE clause in your SQL statement.

[jmw229]

Sorry for the late reply. I appreciate your quick response. Without giving you the extremely long list. ill give you some samples

Whats making this harder is the fact its coming from a form not from the recorded data.

tblPrograms (has a ridiculous amount of fields)
Program 1 (Yes/No)
Program 2 (yes/No)
MinimumAge (Number)
MaximumAge(Number)
ServiceArea

tblServiceArea (just one field):
City 1
City 2
City 3
etc

temp (some of tblPrograms combiend withed tblServiceArea)

---

frmFront:
users clicks program that interests them
enters their age
service area close to them

Any suggestions .. keep in mind I'm new to this. I didn't design or create the database but just trying to create this query

thanks!
jay

[timothybard]

I'm sorry for the late reply; I didn't realize you replied to my post. I suppose I'm still not clear on your objective. From your previous post, it appears to me that you only need a query that joins two tables and not a union query. When speaking about database, a union query is a very specific type of query to list data from two or more tables that have very similar data as one list. The only reason I mention this is because the title you gave the thread threw me off, which isn't a big deal :)

From your post, it sounds like you have a list of people and then you need to find a list of programs that meet their requirements. This is done by building a query that joins two tables.

I will post specific code in my next post from the information in your previous post.

[timothybard]

What you are trying to do is more complicated than just building a query, so I created a sample database that does what I think you are trying to do. I have attached it to this post.

When you open the frmFront form, you can enter the programs to search for. When you click the button, another form shows up with the results.

Here are some things to realize... the first form does not have a record source; the controls in the first form do not have control sources, but the combobox does have a row source.

The command button on the first form contains the code to select specific records based on the criteria selected by the user.

The record source of the second form is simply a join between the two tables:

SELECT tblPrograms.[Program 1], tblPrograms.[Program 2], tblPrograms.MinimumAge, tblPrograms.MaximumAge, tlbServiceArea.City FROM tblPrograms LEFT JOIN tlbServiceArea ON tblPrograms.ServiceArea=tlbServiceArea.ID;

It sounds like you are new to databases, so this may be a learning experience for you; I would not expect someone new to databases to fully understand what I mentioned above or the details of the code. Please feel free to ask me any additional questions.

Lastly, I realize that you did not design the database, but I would recommend against using the Program1, Program2, etc. checkboxes; the database can be normalized to avoid them. However, that does require restructuring the database and is not directly related to your original post; it's just a suggestion.

[jmw229]

Hi Timothy,

Thanks for the great response. That really helped alot espescially since your database is more simplified and less information. Again Sorry for the late response, it is not my top priority/task. I am going to try to understand and practice what you've done. I'll likely have questions every now and then, do you have email?

[jmw229]

also, do you know of a good site or book that will walk me through SQL in Access- from beginner to this level??

[timothybard]

also, do you know of a good site or book that will walk me through SQL in Access- from beginner to this level??

You can try this site:

http://www.w3schools.com/sql/default.asp

I have never used w3schools.com for SQL, but I have used it often as a reference for HTML and CSS. I have found the site to be very easy to understand.

Also, MS Access has a powerful SQL editor that you can use. Many queries can be created with it without learning SQL at all.