Union Query Problem

[Eljefegeneo]

I have a command button on my form frmMain that I used to bring up all the client records that are "Active" (having an open contract). qryActive is the first query shown below. The following code works fine; it filers all the records to show only those that meet the criteria.

DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryActive WHERE CustomerStatus='Active' )"

But I had to add a T/F check box to include those records for which we wanted to include [KeepActive]= True, which is the second query shown below qryKeepActive.

The Union Query seems to work fine. It runs very fast, less than one second to filter 20K records. But when I tried to use the following code with the Union Query instead of the DoCmd code above, it seems to be in an endless loop or isn't working, no errors, just hangs up.

DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryOnTheAirUnion)"

What cannot figure out is how to use it to use it to filter the records to select only those that meet the criteria.

SELECT tblMain.ClientID, Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")) AS CustomerStatus
FROM tblSales INNER JOIN tblMain ON tblSales.ClientID = tblMain.ClientID
GROUP BY tblMain.ClientID
HAVING (((Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")))="Active"));

UNION ALL SELECT tblMain.ClientID, tblMain.KeepActive
FROM tblMain
WHERE (((tblMain.KeepActive)=True));

What I am attempting with this code is to select all records that meet the first query criteria, that is, are "Active" and the second query criteria, KeepActive = True. The two queries are mutually exclusive; any that are [KeepActive] = True will not be "Active" in the first query.

Any suggestions?

 

[vbaInet]

A couple of things:

1. >>>DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryOnTheAirUnion)"<<<
You can't use that in the WHERE part of the OpenForm method. That's not criteria, it's a subquery.

2. Why do you need a union query? Can you elaborate on this.

 

[Eljefegeneo]

Code #1 works fine if that is the only criteria for selecting "Active" Clients.
I tried to combine both criteria in a query but the results were no records selected with either CustomerStatus = Active or KeepActive = True

[FONT=&quot]SELECT tblMain.ClientID, tblMain.KeepActive
FROM tblSalesAndSchedules INNER JOIN tblMain ON tblSalesAndSchedules.ClientID = tblMain.ClientID
GROUP BY tblMain.ClientID, tblMain.KeepActive
HAVING (((Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")))="Active")) OR (((tblMain.KeepActive)=True));

However since you are telling me that I cannot use this in the filter criteria(the Union Query), I need to know if it is possible to use something to select all those where the CustomerStatus is active (the Min(... statement, OR the T/F field KeepActive = True.

I thought that a Union Query would solve this but I am finding out the hard way it won't.

Any suggestions as to how to use the criteria CustomerStatus = Active Or KeepActive = True in a filter when opening a form to select only those records that meet either of the two criteria? Thanks.
[/FONT]

 

[vbaInet]

However since you are telling me that I cannot use this in the filter criteria(the Union Query), I need to know if it is possible to use something to select all those where the CustomerStatus is active (the Min(... statement, OR the T/F field KeepActive = True.

No, it will work if the name of the subquery doesn't clash with the parent query. I think it's not working because there's a name clash somewhere.

But you still haven't explained why you need a union query?

 

[Eljefegeneo]

What I want to do is select the "Active" and KeepActive = True records. The Union query was the only way to select both. If that is not the way to go, then how can it be accomplished.

 

[vbaInet]

Ok, the use of a union query kinda makes sense. Can you throw up a mock up db?

 

[Eljefegeneo]

It took some time but I finally was able to get a watered down version of the DB.

What I am trying to do is (1) select only those records that meet the "Active" criteria as is shown in the DoCmd.Open code in back of the "Active Only" button on frmMain. As mentioned above, there are certain records with [KeepActive} = True, although they do not meet the criteria:

DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryOnAirActive WHERE CustomerStatus='Active' )"

This code works but doesn't show any of the records with [KeepActive] = True of which there are six.

I tried a union query, but it just show "query running" in the right hand corner of the DB screen.

Is there any way to do this?

Thanks.

 

[vbaInet]

Right, let's work on the following criteria. I don't think you've written it properly:

HAVING (((Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")))="Active"));

So copy and paste the following into a new query and tell me which date values for ContractEnds and DateCancelled the criteria should be applied against:

SELECT tblSalesAndSchedules.ClientID, tblSalesAndSchedules.ContractsEnds, tblSalesAndSchedules.DateCancelled
FROM tblMain INNER JOIN tblSalesAndSchedules ON tblMain.ClientID = tblSalesAndSchedules.ClientID
WHERE (((tblSalesAndSchedules.ClientID)=146));

 

[Eljefegeneo]

Thank you for your help so far. I think this is what you are asking me.

To be active, the DateCancelled has to be either Null or = "" or ContractEnds has to be greater than Date(). This will produce a result of True, which means that the Client is Active. Otherwise, the result is False (there are no current contracts still open) and the Client is "Inactive". The "Min" will produce one result for the particular ClientID. That is, if the ClientID has at least one contract still open as of today, then they are "Active". So that part of the code in the query did produce the desired result.

The problem arises when I try to include an OR of KeepActive = True. I thought a Union query would work, but I guess not.


If I have not explained myself correctly, please let me know. The end result is to filter for only those clients who have at least one contract open as of today OR have KeepActive = True.

 

[Eljefegeneo]

DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryOnAirActive WHERE CustomerStatus='Active') Or ClientId In(Select ClientId FROM qryKeepActive Where KeepActive = True)"

The query called qryKeep Active: SELECT tblMain.ClientID, tblMain.KeepActive
FROM tblMain;

So, the above filter produces 109 results
The following filter produces 103 results.
DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryOnAirActive WHERE CustomerStatus='Active' )"
And the filter
DoCmd.OpenForm "frmMain", , , " ClientId In(Select ClientId FROM qryKeepActive Where KeepActive = True)"
Produces 6 results.

So it would appear to be solved.

 

[Eljefegeneo]

And I forgot to thank you for your time and help. You prodded me int he right direction.

 

[Eljefegeneo]

Well, solved and not solved. It works great on my sample DB, but when I put the code in my regular DB, with 21.5K records in tblMain and 4.4K records in tblSalesAndSchedules, when I click on the command button to show all "Active" and all "KeepActive" = True records, the query hangs up - there is a message at the bottom of the screen that says the query is running. The test Db had 125 records in tblMain and 1000 in tblSalesAndSchedules.

I then proceeded to "index" the fields KeepActive in tblMain and "ContractStarts", "ContractEnds" and "DateCancelled", hoping that would speed up the query. Alas no.

So the size of the two tables seems to be a problem. And I am back to seeking a solution. I do not know how to mark the query "unsolved".

 

[vbaInet]

Thank you for your help so far. I think this is what you are asking me.

I work better with examples that was why I gave you the query and asked you to show me which records match the two conditions.

 

[Eljefegeneo]

I do appreciate your help but once again I do not understand what you are requesting. I looked at the query that you posted, put it into my Expanded DB – the one which includes all the records in tblMain and tblSalesAndSchedules. For your specific query, that is with clientID=146, the following dates are :

Contract Ends: 11/26/2014, 08/25/2015, 03/28/2015 and 3/30/2015
DateCancelled: none

In general, the dates that fit the criteria for all clients are:

ContractEnds > Tomorrow's date
DateCancelled > Tomorrow's date


The following query produces all the records with an open contract OR KeepActive = True

SELECT tblMain.ClientID, tblSalesAndSchedules.ContractsEnds, tblSalesAndSchedules.DateCancelled, tblMain.KeepActive
FROM tblMain LEFT JOIN tblSalesAndSchedules ON tblMain.ClientID = tblSalesAndSchedules.ClientID
WHERE (((tblSalesAndSchedules.ContractsEnds)>Date()) AND ((tblSalesAndSchedules.DateCancelled) Is Null Or (tblSalesAndSchedules.DateCancelled)>Date())) OR (((tblMain.KeepActive)=True));

But when I leave out the OR criteria and try to put it into a ONClick event:

DoCmd.OpenForm "frmMain", , , "ClientId In(Select ClientId FROM qryActiveKeepActive WHERE KeepActive = True)"

The result is an unknown error. No error code is shown.


The "what" I am trying to achieve is shown in post #9. All those that have open contacts or have KeepActive = True. The attached DB is all the pertinent records and which code works and those that cause a hangup or unknown error.

If I have not explained what I have done or answered your questions, please let me know. I do know that I can get the records I want as per the above query. But I just cannot use is as a filter for a form.

Thanks again.

 

[Eljefegeneo]

I thought this was solved, but the first solution worked if there were only a few records, 200 or so. But when I tested it on the full DB with 20K records, the filters hung up. that is, they resulted in the screen freezing, probably because the query was either in some endless loop or was taking too long to get any results.

Is what I am trying to do possible? That is, filter with and OR condition?

The last sample DB has the large number of records. The command buttons of the form show my attempts to solve this.

I'd just like to know if it is possible or not. If not, I won't lose any sleep over this, but logic says that you should be able to filter with two conditions. I have searched endlessly for an answer but have not come up with a solution. Thanks.

 

[vbaInet]

What I'm asking for is very simple. I don't want to spend too much time trying to understand your tables and how they're setup that's why I'm asking for examples, it makes it easier.

What I want to see is an Excel spreadsheet with some data and you highlight the records that should meet the criteria. Makes sense now?

 

[Eljefegeneo]

OK, the attached is a spreadsheet showing the data. I want all those that are either highlighted in yellow as "Active" or highlighted in red as "TRUE". Active comes from the
(((Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")))="Active")) = "Active"

And true come from the check box KeepActive.

Thanks. I hope I am being more clear now.

 

[Eljefegeneo]

Sorry, it didn't seem to upload. Will try again.

 

[vbaInet]

That all makes sense but what you've shown me is the finished product. What I'm getting at is, disregard the union query and any other queries you've built. Show me the raw records, and tell me which ones you're trying to retrieve. I don't want to work with your union query and subqueries, I want to build mine and in order for me to do that I need to understand the raw data and which values you want. Makes sense?

 

[Eljefegeneo]

I have been trying to figure out how to present the data, and it has proven difficult for me. So I am attaching a new excel file. The first page shows tblSalesAndSchedules. Those highlighted in yellow are all ContractEnds >09/28/2014 (an arbitrary date – the one I would use it Date() or Now(), giving me today's date) that do not have a cancellation date that is prior to 09/28/2014 thus at the present time, would have an open contract.. Since there may be a [ClientId] with several sales that meet this criteria I used the Min(etc.) to just give me one record for filtering purposes. That is, a ClientID may have more than one [SequenceNumber], the identifying field of a particular sale. The second page shows tblMain with those highlighted that have [KeepActive] = True.
I do appreciate you taking a look at this, but again if I have not given you the data that you requested, let me know. The reason I am not letting go of this is because it would seem logical that you could filter by two conditions, albeit one with a more complex criteria.
The desired outcome is to filter for all those Clients (distinct ClientId0 that have at least one contract OR have the checkbox KeepActive = True.