Report Based on 2 Queries

[cbgroves]

I am trying to create a report to compile two different payments made within a date range. The two payments are not always in the same range, but I need all payments (whether it is the first or the second) made in a date range to be reported. I made a two separate queries to pull this information, but I need it to be displayed on one report and that is the part that I cannot figure out. Can anyone help me out?

[JenEveAle]

My suggestion is to take the two separate queries, make them a union query, and then base your report off of the union query.

[cbgroves]

Can you give me some guidance on this? I tried and the query wizard will not let me add fields from more than one query.

[JenEveAle]

Sure. You cannot do a union query through a wizard. Basically, you have to make sure that you have the same fields in the same order from the two tables you want to unite. Keep in mind that you do not have to pull every field from each query into the union query. Only pull in what you need in the final query, but you do need to have the same fields in the same order. The format is

SELECT Field1, Field2
FROM [Query1]
UNION SELECT Field1, Field2
FROM [Query2];

[cbgroves]

I keep getting an error that the columns do not match, but they do. Here is my SQL, am I doing something wrong?

SELECT ID, [Course Prefix], [Course #], [Section #], [Course Name], [Contact 1st Name], [Contact Last Name], Hours, [1st Payment Made], [1st Payment Amount], College
FROM 1stPayment
UNION SELECT [2nd Payment Made], [2nd Payment Amount]
FROM 2ndPayment;

[cbgroves]

I actually got it to work, but it is not doing what i need. Maybe I am going about this the wrong way.

Basically I have two separate payments that are stored in the same table, and I need to generate a report showing all payments made in a certain time frame. The two payments do not always fall into the same time frame, so I need the report to only show the payment if it occurred in the specified time frame. I would like the field to be left blank in the report if the payment has not been made or was made outside of the specified time frame. What approach should I take?

[JenEveAle]

I don't understand what you mean by "you want the field to be left blank in the report"... Also, you don't use a union query if your data is in the same table. It's not necessary. In your query statement above, I do not see where you're pulling in the date.

[cbgroves]

I posted the question again, and it was solved in this thread:

https://www.accessforums.net/reports...ort-19709.html