Counting using different criteria

Mysterydave

Hi,
I have a table of applications including what someone's applied for, which year, what type of applicant they are, and progress of the application etc.

I want to be able to break this down into subject areas and numbers of applications, number of offers per year per type of student etc. It would be nice if this is in a table.

I can generate the required counts using queries, but each one is seperate and I would like a table with these queries as fields basically? So I could see: ah this course had this may applications for 2008, this many for 2009, this many offers, this many rejections, this many withdrawals etc. All in one table.

In the end I want to be able to make nice reports for each department?

I have a basic grasp of SQL but am learning fast so be as patronising as you like!

Cheers

Dec 22 '08 #1

NeoPa

@Mysterydave
I love it :D I wonder if that is in response to some threads you've already browsed through.

As for your question - well, I'm not really sure there IS a question. What exactly are you after from us. We work best when posed with specific questions.

What I will do for now, is point you at an article that I and many others around here have found to be extremely helpful in our understanding. It's Normalisation and Table Structures. Enjoy it and Welcome to Bytes!

Dec 22 '08 #2

Mysterydave

At the moment I have seperate select queries which look at my big table of applications. For example. 1 query will select courses in the Education Faculty, that have been applied to by foriegn students, and counts the number of times the year of entry is listed where the course code is the same. Another will be identical but includes the offer field the say conditional, another for unconditional, so I have many queries.

I want to have a table that has the fields: course code, year of entry, student status (Foriegn or Home based), then the count results from the above queries. Such as total applications, number of conditional offers, number of unconditional offers etc.

I hope this gives a clearer understanding of my problem.

Cheers

Dec 23 '08 #3

Mysterydave

OK, I think I'll let Excel do the hard work for me using Pivot tables and then copy and paste that to a new sheet which I will then import to Access. Few......I think I'm nearly there!

Dec 23 '08 #4

Stewart Ross

Hi MysteryDave. Excel is probably a good way to go to start with, as it is a lot simpler to try some things out than to work through how to get the results you need using SQL-based queries.

An applicant can submit many course applications. The result of such an application can include: an unconditional offer, conditional offer, interview, rejection, waiting list, or simply application pending (reflecting no action so far). An applicant who receives an unconditional or conditional offer may choose to accept that offer or reject it - which in turn should be recorded on the applicant record, as a rejected offer is not going to lead to a course place being filled.

It is also common to have conditional offers subsequently superseded by unconditional offers for the same course after fulfilment of particular conditions - so in practice there can be many outcomes relating to the offer made to the applicant for any one application.

It is consequently not a trivial task in SQL to create summary queries which count the number of conditional and unconditional offers etc by applicant, and more importantly by course (where it is vital to know how many applicants have applied and how many conditional and unconditional offers have been made and accepted). It is certainly possible - and in my last job I used to routinely calculate such statistics using SQL-based queries - but it requires a detailed knowledge of the way your applicant system is set up, and how responses to applicants are coded and recorded. It is difficult to advise you without such detailed knowledge in your case, and unfortunately you have left us with no real detail of your current tables or applicant recording methods - which is what NeoPa is referring to in saying that there was not much of a question in your original post.

Anyway, trial things in Excel by all means, and if you need more detailed assistance we'd be glad to help. You would need to provide much more in the way of detail for us about the tables concerned, but that should be straightforward for you after you do your own investigations.

-Stewart

Dec 23 '08 #5

Mysterydave

Thanks Stewart,

It sounds like you have a very good idea of what i'm doing. OK I'm comparing applications from 2008 to 2009 at this time of the year. I have got the application information by exporting from the applications using the date/timestamp.

This gave me lots of applications from 2008 and 2009 which I want to compare. The table is set up as follows:

[App No] (8 numbers, text field),
[TimeStamp] (Date/Time field)
[Course Code] (e.g.TLT3),
[AreaOfStudyCode] (e.g.88Y3),
[AOS_Description] (E.g.BA(HONS)Amer...),
[Decision] - U,C,R,W,S,G,I,M, or blank. (1, Char) Unconditional, conditional, rejection withdrawn etc.
[Reply] - F,D,I or blank Firm, Decline, Insurance
[Status] - Combination of the above fields inc. OS outstanding for both blanks
[Year of Entry] - e.g.2009 or 2008
[Agency] - U or G
[Student Status] - H,E,P,O,C - indicates where the applicant is from
+more which aren't really important, unless you specifically require?

Basically I want a count of the [Status] field per [course code] per [Student Status] - 2 groups H,E,P and O,C ; and per [year of entry] in a big table.

Then, I can seperate in to faculties using the [AOS Code] producing reports to show the difference in the progress of applications across the 2 years.

I think I'm nearly losing understanding of this myself....sorry.

Please don't fret over this as it is a) Christmas, nearly and b) I have come up with a solution using Excel and pivot tables to make a table to stick in to Access.

Cheers

Dec 23 '08 #6

Stewart Ross

Hi. Assuming for test purposes that the table is called Apps, here is a count query that does what you mention above:

Test Data Extract:
Result Extract:
Perhaps of more use would be a crosstab query which can summarise this dataset by status:
Results:
These are just two of the many possibilities which you can experiment with. Good luck in your investigations.

-Stewart

Jan 5 '09 #7

Mysterydave

Thankyou very much for your endeavor! This is exactly what I was looking for!

Happy New Year

Jan 12 '09 #8