Help normalize my Union Query (1 Viewer)

josephbupe · May 21, 2018

Hi,

I am using a union query to generate a bar-chart based on data for year 2018 thus far. However, the query returns a wrong bar-chart when I enter a year that does not have data. For instance, open the form named "F_VENTS_STATISTICS" and enter 2017 in the prompt window; the data generated for Training does not exist; I expect to see a blank chart.

Please, i have attached a copy of the db file.

Any help is appreciated in advance.

Thanks.

plog · May 21, 2018

A UNION query should simply UNION data together. No more. The SQL in it should be uber-simple:

Code:

SELECT Field1, Field2 FROM Source1
UNION ALL
SELECT Field1, Field2 FROM Source2
UNION ALL
SELECT Field1....

When you try and have it do other things (accept parameters, apply criteria, GROUP, etc.) you are going to screw it up. So, divide and conquer.

Make sub queries that handle just 1 datasource. Apply your grouping and criteria that apply to just that one data source there. Give aliases to your fields so that when you do UNION it to other sources the names match. Do this for every datasource that will feed the UNION.

Then, UNION all those sub queries together as I demonstrated above. Then, if you have criteria or grouping that applies to all the data, use the UNION as the source of another query. Apply criteria and grouping there as well as any parameters you want to use. That query will then be the one you use.

Divide and conquer. The UNION only contains simple SQL and the heavy lifting is done by other sub queries so that you know you do it right going into the UNION.

plog · May 21, 2018

Now I am looking at your query closer and it makes no sense. What are you trying to do?

That' query is going to produce essentially static data, with 5 rows. Why not just make a table and call it a day?

josephbupe · May 21, 2018

Weird as it may seem, believe me - this is what works in the context of what was required of the database: generating quarterly graphs per year. It took me quite a while to get where it now is.

What I am thinking now is probably to revisit the data range in the chart it self.

Thanks anyways.

isladogs · May 21, 2018

I also had a look at this and am of the same opinion as plog.
You want quarterly values for each category for a specified year.

There is no reason to do this using a union query let alone one as complex as yours. Even if it always worked, it would be unnecessarily complex. However it doesn't work reliably so I suggest you scrap it

Try creating an aggregate query grouping by event type and date by quarter.
Then use that for your chart

josephbupe · May 21, 2018

I hear you, I can try that too.

Thanks.

June7 · May 21, 2018

Year and Month are reserved words (they are both intrinsic functions). Recommend not using them as names for anything. Use something like EventYear, EventMonth, Yr, Mo. Same for Count.

The "Trainings" SELECT lines will always return records so this means the CROSSTAB will count those records. Why are you doing UNION?

josephbupe · May 21, 2018

My original query used to be simple with just a crosstab query. But things become complicated because I needed a method to generated a graph by year interactively.

I wish some here could just modify the file i attached. Its just driving me crazy now:banghead:

isladogs · May 21, 2018

OK here you are:

Code:

SELECT Q_EVENTS.Category, Count(Q_EVENTS.Category) AS Events, Q_EVENTS.Year, Q_EVENTS.Quarter
FROM Q_EVENTS
GROUP BY Q_EVENTS.Category, Q_EVENTS.Year, Q_EVENTS.Quarter;

OR if you want the parameter selection for year

Code:

SELECT Q_EVENTS.Category, Count(Q_EVENTS.Category) AS Events, Q_EVENTS.Year, Q_EVENTS.Quarter
FROM Q_EVENTS
WHERE (((Q_EVENTS.Year)=[Select Year]))
GROUP BY Q_EVENTS.Category, Q_EVENTS.Year, Q_EVENTS.Quarter;

BTW I agree with June's point about renaming fields

josephbupe · May 21, 2018

ridders said:
OK here you are:

Code:

SELECT Q_EVENTS.Category, Count(Q_EVENTS.Category) AS Events, Q_EVENTS.Year, Q_EVENTS.Quarter FROM Q_EVENTS GROUP BY Q_EVENTS.Category, Q_EVENTS.Year, Q_EVENTS.Quarter;

OR if you want the parameter selection for year

Code:

SELECT Q_EVENTS.Category, Count(Q_EVENTS.Category) AS Events, Q_EVENTS.Year, Q_EVENTS.Quarter FROM Q_EVENTS WHERE (((Q_EVENTS.Year)=[Select Year])) GROUP BY Q_EVENTS.Category, Q_EVENTS.Year, Q_EVENTS.Quarter;

BTW I agree with June's point about renaming fields

Okay ridders, that's it!

And YES i will comply with the field naming rule. I will be back if i need more help.

Thanks alot

Help normalize my Union Query (1 Viewer)

josephbupe

Registered User.

Attachments

plog

Banishment Pending

plog

Banishment Pending

josephbupe

Registered User.

isladogs

MVP / VIP

josephbupe

Registered User.

June7

AWF VIP

josephbupe

Registered User.

isladogs

MVP / VIP

josephbupe

Registered User.

Similar threads

Users who are viewing this thread