Union Query and Count At Same Time

[rfear]

I have 2 tables ( tbl1 and tbl2 ). One contains arrival dates and the other departure dates. Some of these dates may be the same and/or repeated.

First off I want to union the data ( unionqry )

Then I want to collapse the arrivals and departures on repetitive dates to a single date i.e. merge duplicates. I've done this before using a count term ( countqry ).

Finally I want to change the date field to a year and week number ( endqry ).

Is there an easy way to go directly from tbl1 and tbl2 to endqry ?

 

[Bilbo_Baggins_Esq]

Hmmm, well, the actual question is; "Is there an easy way to do" this?

I'll be curious to hear from others, but I can't think of a way to get all the dates from the two tables into one query column which would qualify to be described as "easy".

The rest of the steps seem inconsequential after getting all the dates into one query column.

As far as I can see, probably the least technically advanced method, but the method requiring the most steps, would be to append the dates from each table into a temp table and do a "GroupBy" query using that temp table. This would require two seperate append queries (one for each table) and they each would need to append to the same column in the temp table. Then a GroupBy would filter that down to only the individual dates.

More advanced would be to write a special function in VBA to build a single array of the dates from each table (I think that's what you'd call it) and show that as the result in the query.

 

[rfear]

Righty o.

I'll keep playing then and post the answer I come up with for future posterity.

Thanks.

 

[Dennisk]

try this

use the distinct clause in your SQL statements i.e.

Select Distinct Date01 from tbl01
UNION
Select Date02 from tbl02

the second select statement is distinct by default.


then use this query as the recordset for any further processing.

 

[rfear]

Huummmm.... I tried 'DISTINCT' but I lost one of my records, mind you the original approach is not correct.

Close but no cigar.

 

[Bilbo_Baggins_Esq]

Hmmm, it's a good thing that UNION wasn't a poisinous snake!
I can't believe I spaced out on that.

Ok, my mistake, but there still is some issues with the UNION in that to get where you want to go, you'll need to do individual "GroupBy" queries for each table Left Joined with the UNION query because if the two table include a common date, the common dates are counted in each field if you try to do it all together.

Once the two GroupBy's are done, they can come together in one common Sum query.

I am not aware of a built in Week function in Access, so unless I'm about to get snake bit (again), you may need to write a fiscal week function because just doing by the numeric day of the year will not result in correctly considering 1/1/8 to be week 1 while the rest should be week 2.

Attached is a sample DB using your original schema

 

[Bilbo_Baggins_Esq]

Yeah, I can see from your recent PDF that you're encountering the same issue I described in my last post (where the two tables share a common date).

Take a look at the sample I uploaded. It overcomes that problem by using a "Totals" (GroupBy with SUM) before bringing the two fields together.

 

[rfear]

I will take a look later at your attached database, thanks, work can get funny about downloading stuff off the net.

In the meantime I have had a blinding flash as to how to merge the two tables.

I can set up a query that changes the field names to common names and at the same time populate the column that is missing from the original table with zeros, for example :-
SELECT tbl1.[Order Date] AS JOBDATE, tbl1.[Arrival Rate], 0 AS [Departure Rate] FROM tbl1;

and,
SELECT tbl2.[Compl Date] AS JOBDATE, tbl2.[Departure Rate], 0 AS [Arrival Rate] FROM tbl2;

I can then use a UNION ALL statement to join the 2 queries I have created,
SELECT qry1.JOBDATE, qry1.[Arrival Rate], qry1.[Departure Rate] FROM qry1 UNION ALL SELECT qry2.JOBDATE, qry2.[Arrival Rate], qry2.[Departure Rate] FROM qry2;


... and as if by magic the 2 tables are joined. One hurdle over, now need to collapse the recurring dates and count up the number of arrivals / departures for each date. Then change the date to a year and week number.

 

[rfear]

Re: Union Query and Count At Same Time ( Solved )

Last piece of the puzzle ( modification to qry4 using SUM instead of COUNT ),
SELECT Year([JOBDATE]) AS Yr, Val(Format([JOBDATE],"ww")) AS Wk, Sum(qry3.[Arrival Rate]) AS [SumOfArrival Rate], Sum(qry3.[Departure Rate]) AS [SumOfDeparture Rate]
FROM qry3
GROUP BY Year([JOBDATE]), Val(Format([JOBDATE],"ww"))
ORDER BY Year([JOBDATE]), Val(Format([JOBDATE],"ww"));

It can be done but took 4 queries to reach the end point. I'll settle for that. Just need to test out on a much larger data set.