joining queries together?

[hazell]

Hi,
I have written a query to find the number of students we have on our books who are between 15 and 18. The query lists the students and gives the total number in data sheet view.
Now I have written another query which looks at the number of students between 15 and 18 who have completed a course. this also works fine.
What I would like to do now is write another query that expresses the number of completers as a percentage of the number of students. Can anyone tell me how to do this. Do I need to join the two queries. If so, the total doesn't appear in design view so I am not sure how to access this in designing the query.
thanks for your help
Hazell

 

[plog]

To join data sources (queries or tables) you need a field on which to join them. From your description you have 2 queries that simply provide you with one number each. If that's the case, using Dlookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) to retrieve those values would be the way to go.

From your description, you should be able to write a query to generate this as well. If you want to post sample data from your table (along with field and table names), I can help you write that query.

 

[hazell]

Ok, the first query goes something like this:

Case Number, DOB, Age Diff, Active?

And the criteria is Between 15 and 18 and Active is Yes. The total number only appears in data sheet view and not in design view

And the second query goes something like

Case Number, Course attended, Course start date, Course End date, Course Completed?, DOB, Age Diff

And the criteria is Between 15 - 18, >=[Start and End Date!Start_Date!] And <=[Start and End Date!End_Date!], Course complete? Yes.

Can you think of a way that I can express the number of course completers as a percentage of those 15 - 18 year olds who are active?

Thanks for your help. I would be lost without you all. I hope that my explanation is not too garbled.
cheers
Hazell

 

[plog]

First, that's not what I asked for. I wanted sample data from your underlying table. Mostly I wanted this because I thought your queries were redundant. With the information you provided, it seems I am correct.

Why do you even have the 1st query? The second query has all the data in the first except for the Active field. Why not just bring the Active field into the second and call it a day?

 

[hazell]

Oh, well, the queries are designed to match my monitoring form, which asks the same question in a million different ways. I agree that it would be much easier to do it differently and I have said so (at length) to our funders, but they are having none of it, I'm afraid

 

[plog]

I'll allow that excuse in some instances but not in this case. This is on you. I find it hard to believe they explicitly told you that those two queries must be joined. They pounded the table and said "No, new queries. This data must be produced in a 3rd query that joins these two queries. Even if we can receive the information we want from a query, if that query doesn't involve the joining of these two other queries, even if the data is correct; we will have none of it".

Wait, it gets better. After that outburst, when you asked them how you should join those two queries they laughed in your face and said 'That's a question for random people on the internet, we have bigger fish to fry.'

Make a copy of that second query, bring in the Active field and make it work.

 

[David R]

More constructively, WHAT did your funders ask for, in their own words?

It sounds like you want both the active and inactive users between the ages of 15 and 18. That can be done with a single Totals Query.

 

[hazell]

My monitoring report is huge, with loads of different questions, most of which don't make any sense whatsoever - statistics for statistics sake, really. So, to make it easy on myself I have developed a query that matches each of the questions, so that I just have to run the query and fill in the data.
But what I need to do now is write a query that shows the number of course completers as a percentage of the total number of active users. Any ideas?

 

[hazell]

the actual question is:

# and % of young people completing courses that they started aged 15-18 by course title.

I have the number, but now I need to work out the percentage.

thanks

 

[hazell]

This is what I have written, but it doesn't work. Any help please. I am trying to find out the percentage of those completed a course as a percentage of those who are active and I entered this in the expression builder but I got a message back that says I cannot use aggregate functions.

Expr1: Count([Course Completed?]/Count([Active?]*100))

Where have I gone wrong? thanks

 

[plog]

Post sample data from the underlying tables. Include field and table names, then also post what data should result from the query based on the sample data. Use this format for posting table data:

TableNameHere
Field1Name, Field2Name, Field3Name...
John, 12/20/2009, 17.8
David, 11/12/2009, 18.9
Sally, 9/9/2011, 20.3

 

[hazell]

Table: Courses

ID Number
Date
Course Attended
Completed?

Table: Tracking
Active?


Sample Data
1301 01/01/13 Woodwork Yes Active
1302 01/02/13 Customer Service Yes Active
1303 14/02/13 Woodwork No Active

Result of query:
Total Courses Completed
Number of course completers as a percentage of those active
Any thoughts?

 

[plog]

Yes, I think you barely read my post.

I need both data from your tables (along with the field names atop the sample data) and I need ending data. You've already used words to describe what you want, now you need to illustrate the point with data.