Union Query

[carymehome]

My query is not pulling my data correctly. I have two queries, each with a month and number. Most of the months exist in both files, but there are two occurrences where there is data in one and not the other. I have used a union query and have gotten half of the problem fixed but cannot pull the month name for records that only exist in one file. Hopefully the data snapshot and code will be clearer than my description. The 8 shown is for the month of June, but the month is not showing, just the data. Any help would be much appreciated. Note: I am pretty much of a novice.

FM	MonApp	Approved	Closed
			8
1	July	6	4
2	August	6	1
3	September	4	5
4	October	9	3
5	November	8	13
6	December	4
7	January	4	2
8	February	4	5
9	March	4	11
10	April	6	8
11	May	3	6

SELECT [Approved Studies by month totals].MonApp, [Approved Studies by month totals].Approved, [Closed Studies by month totals].Closed, [Approved Studies by month totals].FM
FROM [Approved Studies by month totals] LEFT JOIN [Closed Studies by month totals] ON [Approved Studies by month totals].MonApp = [Closed Studies by month totals].MonClosed
UNION SELECT [Approved Studies by month totals].MonApp, [Approved Studies by month totals].Approved, [Closed Studies by month totals].Closed, [Approved Studies by month totals].FM
FROM [Approved Studies by month totals] RIGHT JOIN [Closed Studies by month totals] ON [Approved Studies by month totals].MonApp = [Closed Studies by month totals].MonClosed

[rpeare]

You don't need a union query to do what you're doing I don't think. Are your approved and closed numbers coming from the same table?

[carymehome]

You don't need a union query to do what you're doing I don't think. Are your approved and closed numbers coming from the same table?

They are in the same table in different fields. They are stored as dates, and I use a function to pull the month from the date, so I have a mon approved and the mon closed as two separate fields. Because I wrote a query to look at closed data and a query to look at approved data, I was pulling this report by joining those two queries. The data is a bunch of transactions and I am using a count on an ID field which is text to get the numbers. Perhaps a convoluted approach? But the table contains the ID number , status (approved or closed), date approved and date closed. Because I need to count an ID record as open and closed if both transactions occurred in my time window, I need month approved and month closed as separate fields. This report is used to track number of approved and closed transactions each month within a fiscal year.

[carymehome]

The union query is allowing me to pull the counts when I have closed transaction in a month with no open transactions and vice versa. The problem is that if I pull the name of the month from the Mon approved date, it shows up in my list only for the months that something was approved, which is why the June transactions (8) appear without a month name. If I switch it and pull the month name from the date closed I my June transactions will appear, but then my December approvals (6) show up without a month name. It would be nice if every month had both open and closed data, but alas that is not always the case.

[rpeare]

are you selecting your data by a customer or other criteria as well or is the 'missing' data for your entire dataset.

In other words does your root table have APPROVED dates that range from january to december? just look at that field and nothing else. If you do you can create a query based on the APPROVED date, select all dates between 1/1/2013 and 12/31/2013, extract the month and create a label like you are now then link that table to your two other queries (APPROVED and CLOSED) FROM the full set of month TO the APPROVED and CLOSED sub queries. What you really need to do is create a full list of months for the period you're interested in and if your root dataset has at least one record per month regardless of other data you can do it this way.

[carymehome]

No there is not necessarily one record in every month for EITHER the approved or closed dates. In the short term, I just had a record approved in June, so now I DO have at least one i each month in my approved query. However, as the new (Fiscal) year begins in July, I will likely have this problem again, with a month with one record in either query but not 1 in the other.

[rpeare]

So you're saying until you put in the june record you just had approved you had no approvals at all, anywhere in that table, with another june date?

can you give me an example of your data (fieldnames/table name)