union query + calculated field (1 Viewer)

[Misiek]

Hello all,
A union query counts how many records has a date entry, the second field counts how many records has another date entry.
my query:

SELECT Sum(Q1.Found) AS SumOfFound, Sum(Q1.Fixed) AS SumOfFixed
FROM (SELECT count([dDateFound]) as Found, '' as Fixed
FROM Q_Defects
WHERE DatePart("m",[dDateFound])=[Forms]![F_MainMenu]![txtMonth]
UNION ALL
SELECT '' as Found, count([dDateClosed]) as Fixed
FROM Q_Defects
WHERE DatePart("m",[dDateClosed])=[Forms]![F_MainMenu]![txtMonth]
) AS Q1;

Trying to calculate field in union query, but gives me this error:

The Expression is typed incorrectly, or it is too complex to be evaluated. For Example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to vartiables.

I need to do this calculation: Fixed/Found=Result

Can anyone advice please?

 

[arnelgp]

SELECT Count([dDateFound]) As Found, Count([dDateClosed]) As Fixed, [Fixed]/[Found] As Expr1 FROM Q_Defects;

 

[plog]

The reason for your error is: You can't SUM text. In your subquery you have these:

... '' as Fixed....
... '' as Found...

In your main query when you try to SUM those empty strings (yes, they are empty strings, not NULLs) you get an error.

Now, here's the advice: get rid of the UNION query. In fact, get rid of the subquery--its not doing anything you can't accomplish in one SELECT. The FROMs and WHEREs of the subquery are the same--this can logically be combined.

 

[Misiek]

@arnelgp,
Thanks, but I won't work this way as those 2 date fields are in the same record, therefore I am restricting count of Fixed (dateClosed).

@plog,
Yeah I thought the error might be caused by ""

I took @arnelgp's idea and @plog's advice and heres my working query:

SELECT
sum(iif(DatePart("m",[dDateFound])=[Forms]![F_MainMenu]![txtMonth],1,0)) AS Found,
sum(iif(DatePart("m",[dDateClosed])=[Forms]![F_MainMenu]![txtMonth],1,0)) AS Fixed,
round(([Fixed]/[Found])*100,0) AS MnthCompletion
FROM Q_Defects;

Thank you both for your input

 

[Misiek]

Is there a way of changing this query int pivot query instead of having 12 different ones for each month?

I in pivot query I need to have a month field, If I select dDateFound, it will limit my results when calculating fixed in current month, because a record can be found in one month but, but closed in another.
If I select dDateClosed, the Fixed/Found will always give me 100% as a result which isn't right.

How can I go around this?

 

[plog]

Now its time to provide sample data. Give me 2 sets:

A. Sample data from Q_Defects. Include field names and enough data to cover all cases.

B. Expected results from A. Show me what you expect the end result to look like based on using data A as its starting data.

 

[Misiek]

OK, please see attached file. Open Form, I made comments inside.

Thank you

 

[plog]

To get the calculations you want, use these queries:

SELECT Month([dDateFound]) AS M, Count(T_Defects.dDateFound) AS [Found]
FROM T_Defects
WHERE (((T_Defects.dDateFound)>#11/1/2015#))
GROUP BY Month([dDateFound]);

Name that 'PercentComplete_sub1'. That finds the Found records by month.

SELECT Month([dDateClosed]) AS M, Count(T_Defects.dDateClosed) AS Fixed
FROM T_Defects
WHERE (((T_Defects.dDateClosed)>#11/1/2015#))
GROUP BY Month([dDateClosed]);

Name that PercentComplete_sub2. It gets the fixed records by month. Finally this query will give you the data you want:

SELECT PercentComplete_sub1.M, PercentComplete_sub1.Found, PercentComplete_sub2.Fixed, [Fixed]/[Found] AS Completed
FROM PercentComplete_sub1 LEFT JOIN PercentComplete_sub2 ON PercentComplete_sub1.M = PercentComplete_sub2.M;

With that said, your Percent Complete concept is misleading. Like you acknowledged before, something can be found in one month and fixed in another (or not at all). That means Fixed In a Month isn't a subset of Found In A Month--thus a month where you went over 100%.

 

[Misiek]

Ok, now I need to pivot the query to make it calculate data for all months at once. How do I do it without adding date field to the last query you provided?

 

[plog]

You are not going to be able to get your total and each month in the same query. Choose one.

Also, why does this need to be pivoted? Why can't that data be displayed as 1 column as opposed to 1 row? Exporting to Excel and pivoting might be the better option, there you can get your months and total in same row easily.

 

[Misiek]

I want to display calculation data on the main form through report layout like I have at the moment.
Do I need a separate query for each month then?

 

[plog]

No, I would use the report for the months based on your pivot query. Then build a new query to get the total and use a Dlookup to put it on the month report where you want it.

 

[Misiek]

I'm struggling with the pivot query:

TRANSFORM round(([Fixed]/[Found])*100,0) AS Compl
SELECT Q_Completion_sub1.M, Q_Completion_sub1.Found, Q_Completion_sub2.Fixed, round(([Fixed]/[Found])*100,0) AS Completed
FROM Q_Completion_sub1 LEFT JOIN Q_Completion_sub2 ON Q_Completion_sub1.M = Q_Completion_sub2.M
GROUP BY ?????
PIVOT ??????
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Can you help me please?

 

[plog]

Just fake it:

TRANSFORM Max([Fixed]/[Found]) AS Completed
SELECT "Current Year Data" AS RowValue
FROM PercentComplete_sub1 LEFT JOIN PercentComplete_sub2 ON PercentComplete_sub1.M = PercentComplete_sub2.M
GROUP BY "Current Year Data"
PIVOT PercentComplete_sub1.M
In (1,2,3,4,5,6,7,8,9,10,11,12);

Actually, grouping by a year would probably be better. That way you can use your table to accomodate all your data and not clean it out every new year.

 

[Misiek]

YOU ARE AWESOME!!!!
Thank you.

1.
Works great.... filters data depending on my cbo choices....even without the WHERE clause, but how?
I don't understand where it takes the filters from, if there is no WHERE in the query?

edit: ignore the above, silly question. Sub queries have WHERE clause.


2.
I also noticed after update to any of my cbo on the form, the data refreshes instantly for this percentage completion, where for all other calculation it takes like a second or two.
Can you explain please ?

 

[plog]

1. You must be workign with different query than I gave you. Mine didn't use any form references.

2. You have to perfform an action to requery data in a query. You could resort the query and it would refresh, you could have an update event on a form to update the query. How it refreshes is all in the context of how you are acting with it. So, I don't know.

 

[Misiek]

Just fake it:

TRANSFORM Max([Fixed]/[Found]) AS Completed
SELECT "Current Year Data" AS RowValue
FROM PercentComplete_sub1 LEFT JOIN PercentComplete_sub2 ON PercentComplete_sub1.M = PercentComplete_sub2.M
GROUP BY "Current Year Data"
PIVOT PercentComplete_sub1.M
In (1,2,3,4,5,6,7,8,9,10,11,12);

Is there a way of presenting this data in simple bar chart, month after month?

When I try, it either combines all months and shows only 1 bar, or displays multi coloured bard all together without clear gap between months.

 

[plog]

Is there a way of presenting this data in simple bar chart, month after month?

Yes, use the queries I initially gave you to create a chart.

 

[Misiek]

Ok, got it working

Now I want to create similar chart, but depending on selection in combo boxes the result will change.

The previous chart was showing data for entire department as one bar for each month.

The new chart needs to show each area in the department as individual bar, in each month, if none of combo have selection.
If I select an area in one combo box, it will show only this area on the chart for each month.

here is my query:

SELECT Q_YTDmonthlyAREAchart3_sub1.Y, Q_YTDmonthlyAREAchart3_sub1.M, Q_YTDmonthlyAREAchart3_sub1.Area, Q_YTDmonthlyAREAchart3_sub1.Found, Q_YTDmonthlyAREAchart3_sub2.Fixed, round(100*([Fixed]/[Found]),0) AS Completed
FROM Q_YTDmonthlyAREAchart3_sub1 LEFT JOIN Q_YTDmonthlyAREAchart3_sub2 ON Q_YTDmonthlyAREAchart3_sub1.M = Q_YTDmonthlyAREAchart3_sub2.M;

what I don't understand is why the query produces multiple lines for each area?

here's print screen:

Thank you for assistance.

 

[plog]

You've travelled too far from where I jumped off for me to help you specifically any more. However...

FROM Q_YTDmonthlyAREAchart3_sub1 LEFT JOIN Q_YTDmonthlyAREAchart3_sub2 ON Q_YTDmonthlyAREAchart3_sub1.M = Q_YTDmonthlyAREAchart3_sub2.M;

My guess is you are not joining your two data sources precisely enough. My guess is that you need to JOIN on more than those 2 data sources' M fields.