Multiple Query Issue (2 Viewers)

[Wulf13]

My DB tracks courses taken by people. Most of these have an expiration date. I can make a query to show me who is overdue on class "x", "y" and I can make a query to show me who hasn't taken "x", "y".

Is there a way I can combine the two queries into one report?

 

[pbaldy]

Sure; either using a subreport to display the second query, or you can use a UNION query to join the results of the 2 queries and have that be the source of the report. Of course, it may also be possible to get all the records with one query to begin with, but that would depend on the structure of the data.

 

[Wulf13]

I thought about a subreport however the names would be divided into two sections whereas I want one.

I'm not quiet familiar with a union query but could try if need be.

I'd prefer to get all the info with one query. Here is the structure:

tblOne:
name
studentNum (P/k)
organization

tblTwo:
studentNum
className
classDate

How do I do this?

 

[pbaldy]

Can you post a sample db with data in the tables and your 2 working queries? I don't see an expiration date field or a classes table. Neither is really required, I just expected to see them.

 

[Wulf13]

Sample

Here is a slimmed down version.

 

[pbaldy]

I should have mentioned that I only have A2k here. If you can't post an A2k version, I can look at it later from home.

 

[Wulf13]

Ok...here is a 2k version.

 

[pbaldy]

Am I missing the query for who hasn't taken the specified classes?

 

[Wulf13]

Oops, that was my bad. After receiving your first post, I went back and tried to put both into one. I deleted the unmatched query that I had. I can repost with that query if you'd like.

 

[pbaldy]

You can just post the SQL of it.

 

[Wulf13]

Ok, so I tried to redo the unmatched query and I made it harder than it needed to be I think. My brain is a little fried from a 5k run this morning.

Anyway, I've re-attached the DB with the unmatched query. Thank you for your patience.

 

[pbaldy]

I would still need an A2k version, but all you have to do is open the query in SQL view and copy/paste that here.

 

[Wulf13]

My bad, I forgot.

SELECT Query1.name, Query1.studentNum, className.className
FROM Query1 LEFT JOIN className ON Query1.className = className.className
GROUP BY Query1.name, Query1.studentNum, className.className
HAVING (((className.className)<>"COMSEC" Or (className.className) Is Null));

 

[Wulf13]

pbaldy, where you able to figure anything out?

 

[pbaldy]

Sorry, I had to take my wife to the hospital. Looks like she'll be fine, but I haven't been able to look at it, and I'm going to bed now. Perhaps tomorrow, if all goes well.

 

[Wulf13]

No problem. I'm sorry to hear that, I hope she is well. Take care.

 

[EMP]

Originally posted by Wulf13
My DB tracks courses taken by people. Most of these have an expiration date. I can make a query to show me who is overdue on class "x", "y" and I can make a query to show me who hasn't taken "x", "y".

Is there a way I can combine the two queries into one report?

In your db, the CourseReportCurrent query returns 6 records and the CourseReportOverdue query returns 2.

If what you want is combine them in one query showing 8 records, you can join them in a Union Query as Paul suggested in his first post.

Select [name], "Current" as [Type], [studentNum], [organization], [section], [className], [classDateTaken], [scores], "" as [lengthValid], [Date Expires]
from [CourseReportCurrent]
UNION
Select [name], "Overdue", [studentNum], [organization], [section], [className],
[classDateTaken], Null as [Scores], [lengthValid], [Date Expires]
from [CourseReportOverdue];

^