Union Query

[robm]

Can a union query be used with 3 select queries? I have 3 queries that give me the results for Allocated, Completed, and Outstanding, and I want to bring them all together for a report.

[felton]

Easily done - just add each new query like it was a new section. The trick is to ensure you have the same number of output fields in each query and also make sure they have the same name in each section. I also found it best to get create the union query with 2 select queries first and get that working, then add the third query.

[johnhutchison]

I agree but it sounds like you don't really need a union.
Instead you could just have a single query where the criteria is "Allocated or Completed or Outstanding"

I should add that I can easily imagine situations where I am wrong about this.

Usually you only need a union if the data is coming from different tables.

[robm]

Thanks John, Felton. I think I've got that part sorted. I used a union to join two of the queries, then added that union to another query which seems to work. I can't see how a single query would work. I need to show the Allocated, Completed, Outstanding for each Actionee. There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days. I created another query, which shows just the people who have actions allocated to them this week, but doesn't show the figures for those who haven't.

[hugson]

UNION ALL is usually superior to UNION. UNION sorts the result and removes duplicate rows, while UNION ALL dcoesn't.
If you do want the effectes of UNION, then use UNION ALL between all but the last pair of queries, since you need to sort and remove duplicates only once.

[patt]

Thanks John, Felton. I think I've got that part sorted. I used a union to join two of the queries, then added that union to another query which seems to work. I can't see how a single query would work. I need to show the Allocated, Completed, Outstanding for each Actionee. There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days. I created another query, which shows just the people who have actions allocated to them this week, but doesn't show the figures for those who haven't.

I dont understand what you wish to do.

Would you give us an example of the source data and expected results also by example.

[johnhutchison]

There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days.

Add a calculated field to the query (or each of the component queries)
Last7: IIf([IssueDate] >= Date() -7, true, false)
This field will be true for any allocations issued in the last 7 days, and false for the rest. You can then Group By this for counting purposes.