Union query?

[damian]

Unsure whether I've grasped the concept of a union query but I'd appreciate confirmation that I'm on the correct track.

I have one query that is populated automatically (DataLoggerQuery) with several parameters being captured in the underlying table. I'd like to populate the underlying table every 20 seconds to avoid the database grinding to a halt over a short time period with such volumes of data (currently recording approx 6 parameters every second).

I'd then like to somehow join up to 3 other queries, from a different database, to this query so that if eg a widget (from the WidgetQuery) was produced at 11:20:23 pm, the information could be plotted on one chart (somehow join queries together) with date/time on x axis.

At moment I've got a standard query where the DataLoggerQuery and WidgetQuery are joined and will display all DataLoggerQuery records and only WidgetQuery records where date/time are equal. If I decrease the data logging frequency to 20 seconds it's highly likely that some WidgetQuery records will be omitted as it didn't have an entry for that precise time.

Will a union query be the answer to my problem? If this request doesn't make sense please let me know and I'll post a better example.

 

[The_Doc_Man]

You would not use a union query for the problem you described, though you might use a JOIN query as a data source for that case.

You would use a UNION query when you wanted to build a recordset for total number of objects among the set (widgets, doomaflodgies, and thingamajiggers).

SELECT "Widget" as OCLASS, W.SeqNum as OSEQNUM, W.LogTime as OLOGTIME FROM W UNION
SELECT "Doomaflodgies" as OCLASS, D.SeqNum as OSEQNUM, D.LogTime as OLOGTIME FROM D UNION
SELECT "Thingamajiggers" as OCLASS, T.Sequence as OSEQNUM, T.LoggedTime as OLOGTIME FROM T ;

which would give you a single query containing OCLASS, OSEQNUM, and OLOGTIME fields. This simple example could then be sorted by the log time to show when each type of object was created in a single query. OK, it's a bit contrived, but the idea for a UNION query is to take DISPARATE tables and merge their records by extracting (and giving common names to) corresponding fields from the disparate tables.

The case you described CAN be done by a UNION query but at least some of that will be better done by a JOIN. I am specifically thinking about relating datalogger entries to widget entries if they really do have something in common to support the JOIN.

I would also worry about the fact of having multiple databases involved leading to key conflicts depending on how your JOIN (or UNION) is managed.

 

[damian]

Thanks for your input DocMan. The only thing that the datalogger and widget tables have in common is that when the data is recorded or the widget produced, it is date/time stamped. There will be gaps where these dates/times aren't the same so therefore widget records will be omitted from the query.

Little confused by your response DocMan as you've gone to the trouble of posting the union SQL but sugesting a join query is more effective - is it perhaps easier to somehow drop the seconds from the date/time field format as there will always be a datalogger entry for every minute of the day?