Is there a way to make a union query conditional??

[quest4]

Hi, folks, I have an interesting problem that I am not sure how to tack, so I figured I try you folks, one of you usually has a solution for just about everything. Here is the problem, I have 7 small tables, with a couple of fields in each and I have a E_Type equal to 100, 200, 300 or 400. If E_Type is equal to 100, I want to union tbl_1,tbl_2 and tbl_3. If E_type is 200, I want to union tbl_1, tbl_4, tbl_5 and tbl_6. If E_Type equals 300 or 400, I want to union all 7 tables. I have only done a couple of very simple union queries so far, and I am at a little lose on how to do this one. Any toughts on how to start this little gem. The union query is for a form, if that is important. Thank you very much in advance for any assistance on the problem.

 

[KenReay]

Hi

you do not say where the value E_Type is, it is in one or all of the seven tables?

But basically I would write a function which retunred a string, and set the Recordsource property of the form to the return value of the query in the on open revent of the form

So

In OnOpen Event

Me.Recordsource = BuildSQL(E_Type)
Me.Requery

and the BuildSQL function would look like so:

Public Function BuildSQL(E_Type as Long)

Dim strSQL

Select Case E_Type
Case 100
strSQL = "SELECT ... UNION SELECT ..."
Case 200
....
End Select
BuildSQL = strSQL
End Function

I have just given the bare bones of the idea, since I assume you know how to write the SQL for the Union Query etc? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[quest4]

Thank you Ken for the quick reply. I think I did not give you enough info. Basic this is an ECN dbase. Like is said this is for a form, the data entry form for new and open ECN's. ECNNo is a autonumber in tbl_1 and E_Type is there also. ECNNo is in all tbls and is the primary key in each table. Ideally, when the form is selected, I would get a popup window asking to enter the E_Type code number. Then, by witch ever E_Type is entered, I build the appropriate form, from the query. As for my SQL and VB, I am a little rusty, they have had me doing other things hear and suddenly it is go build an Access dbase for the ECN's. Thanks alot, if you know what I mean. Again, thank you for your assistance and your time, I do appreciate it.

 

[lgbatdw]

I have another question related to UNION query being conditional. I have a combo box that lists locations and uses a union query to have "(ALL)" at the top of the list.
This is the basic rowsource query:

SELECT tblMasterLocationCodes.LocationID, tblMasterLocationCodes.LocationName
FROM tblMasterLocationCodes
UNION SELECT "*", "(ALL)" from tblMasterLocationCodes
ORDER BY tblMasterLocationCodes.LocationName;

I would like to (within the query) have the

"UNION SELECT "*", "(ALL)" from tblMasterLocationCodes"

portion of code be conditional somehow. Sometimes I only want to list a specific selection of location codes (got that figured out) and then I don't want the "(ALL)" to be there. I know I can do this with code on the form's OnOpen event, but I wondered if it could be taken care of within the combo box's rowsource query. Is this possible or am I barking up the wrong tree? Anybody?
Thanks in advance.

Linda in MN

 

[PHV]

(got that figured out)
How you did that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lgbatdw]

PHV
The specific (numeric integer) locations are identified(up to 5, based on who has logged in) and stored in unbound controls on the main menu. I apply these locations as the criteria of the rowsource query to filter the dropdown to list only these locations. Since the location fld from the Location table is an autonumber fld, the instances where some users are not assigned all 5 available, the value of the remaining assignable locations is zero and would not find a match in the location table recordset because the LocationID fld (autonumber) would never be zero. So still filters correctly even if not using all 5. Hope that answers your question.

Linda in MN