IF correct THEN UNION

[dr4ke]

Hello all,



You may have seen my previous post around Union queries, and I've just thought of a MUCH better way of doing it. To provide some context about what I'm currently doing;

I currently have a Database where I am comparing the 'Start' value and 'End' value of Servers. In order to do this I have to seperate each server into it's own Query then perform FIRST and LAST functions to compare the values, this results in 3 queries per Server. Query 1; This provides the Server data and name and calculates the Free space from the Total/Used and converts the numbers into GB (rather than KB - don't ask!). It also works the 'Date Diff' from a Form which the user will select the start date and end date from. Query 2; This provides the comparison between the FIRST used/Total/Free etc against the LAST used/Total/Free etc giving a comparison of data between the dates. Query 3; This then calculates the Daily Growth of the Server and works out how long it would take for it to run out of space. I.E. The Used Difference figure divided by the Date Difference figure. This will sometimes throw up a '0' if, for example, the numbers are the same. The next problem comes when trying to get the 'Time Left' as it has to divide the 'Free Space' by the 'Daily Growth' but obviously you can't divide something by 0!! So I get a big fat error .... Which is fine!!

Now... I want to shove all of this into a pretty report, so it would be very beneficial to have it all in the same SubReport; meaning I need to use the UNION function! Hooooowever! You can't use UNION when you have an error ... Now you see my problem!!?

So..... Here is the proposed solution (I'd hate to just shout "HELP" without at least trying to think about it first!!) ... If I could incorporate an IF/IIF or what ever it's called in Access with a UNION function, it could work like the below...

IF DailyGrowth >0 In Query3, then Select the relevant fields and move onto the next UNION command (where it will then have another IF for the next Server Query where this data resides). Otherwise just move onto the next Server... I've had a go at trying to explain this below...

[Code]
(IF
Server1Query3.DailyGrowth >0 Then Select [Field 1] AND
(IF
Server2Query3.DailyGrowth >0 Then Select UNION [Field 1] AND
(IF
Server3Query3.DailyGrowth >0 Then Select UNION [Field 1])))
Else
IF(
Server2Query3.DailyGrowth >0 Then Select Union [Field 1] AND
(IF
Server3Query3.DailyGrowth >0 Then Select UNION [Field 1]))
ELSE
IF(Server3Query3.DailyGrowth >0 Then Select UNION [Field 1])
ELSE
DO NOTHING

make sense??

[June7]

A UNION Is a query structure. You can build an SQL string that includes a UNION but then must use that string to either set the definition of an Access query object or the RecordSource of a form or report.

Dim strSQL As String
Dim dblDG AS Double
dblDG = some code, such as DLookup, to get value from Server1Query3
If dblDG > 0 Then strSQL = "SELECT [Field 1] FROM table/query name "
dblDG = some code, such as DLookup, to get value from Server2Query3
If dblDG > 0 Then strSQL = strSQL & "SELECT " & IIf(strSQL = "", "", " UNION ") & "[Field 1] FROM table/query name "
...etc
'code to set QueryDef or RecordSource with strSQL

[dr4ke]

Sorry.... That sounds good, but it has confused the hell out if me and I wouldn't know where to start!!

Could you dumb it down a bit for me?

[June7]

Can't use If Then Else inside Access query. What I am giving an example of is VBA code that builds an SQL string. It's about as 'dumb' as it can get.

I don't know enough about your data. You want to pull a DailyGrowth value from a query. VBA can pull value by opening a recordset or executing a domain aggregate function (DLookup, DSum, DCount, etc). Do each of the server queries result in a single record? Show the SQL statement of one of the server queries.

If you want to provide db for analysis, follow instructions at bottom of my post.

[recyan]

Not sure, if I have understood correctly, but have you tried something like below :

SELECT 
	qryUnionAllA.DailyGrowth, 
	qryUnionAllA.Field
FROM 
	(
		SELECT DailyGrowth,Field1 as Field FROM Server1Query3
		UNION 
		SELECT DailyGrowth,Field1 as Field FROM Server2Query3
		UNION 
		SELECT DailyGrowth,Field1 as Field FROM Server3Query3
	)
	AS qryUnionAllA
WHERE 
	(((qryUnionAllA.DailyGrowth)>0));

Thanks

[dr4ke]

SELECT 
    qryUnionAllA.DailyGrowth,

Sorry, what does the 'qryunionAllA' relate to? Is this my Query name?

[recyan]

No,
it is the alias used for the sub-query ( the union query ) in the main query which I have posted.

Have you tried running the query that I have posted ?

Thanks

[dr4ke]

No but I will do shortly

sorry, I was just trying to understand it

[recyan]

Sorry, my bad.
The reason I asked the question, was to find out, if it was throwing up some error, when you tried to adapt it.
It's definitely good, that you try to understand before trying.

Thanks

[dr4ke]

Sorry, I've just tried it and I don't think I understand properly...

qryUnionAllA.DailyGrowth

I'm not sure where this comes from? I don't have a table called qryunionAllA ? However I do have a column called DailyGrowth?

Sorry...

[dr4ke]

So my current code is .....

 SELECT 
[SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Server1Aggregate1];
UNION SELECT [SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Server2Aggregate1];
UNION SELECT [SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Server3Aggregate1];

And it will need to use the 'WHERE' function on 'DailyIncreaseGB'

[June7]

recyan is suggesting an sql with nested subquery. The subquery is the UNION sql within the parens. Subqueries must be given a name - as shown by 'AS qryunionAllA'. This alias name lives only within this sql statement. This nesting is in lieu of two saved Access queries. You could save the subquery sql as an Access query and name it qryUnionAllA then build another query (the 'outer') that calls the saved UNION query.

I thought you had tried a UNION and were encountering problems with #ERROR in results and wanted a VBA solution?

[dr4ke]

Hi,

I'm really not sure is going on today, I'm just not getting it!! I was trying resolve the problem with the #ERROR in the results, however in the past 1.5 hours I've managed to resolve this by doing so in the original queries with the below code. So I now have 3 types of data in my Union, Data which has 'No Change' in, Data which has 'Decrease Of Used' and minus numbers. Those minus numbers show the decrease in useage and how much time is left.

What I would now like is a query so if the DailyIncreaseGB field is BELOW 0, it will run the UNION, otherwise it wont.

does that make any sense?

 IIf([DailyincreaseGB]=0,'No Change',(IIf([DailyincreaseGB]>0,'Decrease Of Used',(Int(Round((LFAS02Aggr3DateComparison.LastFreeGB)/(DailyIncreaseGB),1))))))

[June7]

Unnecessary parens in the expression.

IIf([DailyincreaseGB]=0,'No Change',IIf([DailyincreaseGB]>0,'Decrease Of Used',Int(Round(LFAS02Aggr3DateComparison.LastFree GB/DailyIncreaseGB,1)))

Where are you using this expression?

I don't understand why this expression will error if DailyincreaseGB is negative.

[dr4ke]

Thank you, That does make things alot cleaner :-)

This expression essentially means, in one of my queries, that if it is trying to divide by 0 then don't!! I've just fixed what I was trying to do with a UNION WHEN

 SELECT [PayrollNo],[Name]
FROM [Table1]
WHERE [PayrollNo] > 1400

UNION SELECT [PayrollNo],[Name]
FROM [Table2]
WHERE [PayrollNo] > 1400;

Really sorry for all the trouble!! I'm obviously not very good at explaining things today!! But at least I reached a solution :-)