Difficulty Creating a Report with a Union Query

[Guest]

I have two tables. One table looks similar to this:

ID Appoint Name Date1 Date2 Date3

1 FU Paul 1-Jan-07 10-Jan -07 1-Feb-07
2 NEW Steve 1-Feb-07
3 FU John 10-Jan-07 14-Jan-07 1-Feb-07
4 NEW Mike 1-Feb-07


The second table looks like this:

ID Appoint Name Date1 Date2 Date3

1 NEW Greg 14-Jan-07
2 FU Dave 10-Jan-07 1-Feb-07

My goal is to create a report that will calculate the dates in the following
format:

Date Appoint Count

1-Jan-07 FU 1
10-Jan-07 FU 3
14-Jan-07 FU 1
14-Jan-07 NEW 1
1-Feb-07 FU 3
1-Feb-07 NEW 2

I was able to achieve this with a union query and then basing the report off
of a group-by query, but only for data in the field Date1. I was not able to
create a union query that would combine all dates in fields Date1, Date2, and
Date3 for both tables into one field. I would sincerely appreciate any help
you can provide. Thanks for your time!

Glen

 

[Pat Hartman \(MVP\)]

You need six select queries within your union query.
Select AppointName, Date1 As AppointDate From tbl1
Union Select AppointName, Date2 As AppointDate From tbl1
Union Select AppointName, Date3 As AppointDate From tbl1
Union Select AppointName, Date1 As AppointDate From tbl2
Union Select AppointName, Date2 As AppointDate From tbl2
Union Select AppointName, Date3 As AppointDate From tbl2;

This is what happens with non-normalized tables - your queries get more
complicated.