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

[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!

[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

[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!

[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

[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

[Stewart Ross]

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

SELECT   [Year of Entry], 
         [Course Code], 
         [Status], 
         IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group], 
         Count(AppNo) AS N
FROM     Apps
GROUP BY [Year of Entry], 
         [Course Code], 
         Status, 
         IIf([Student Status] In ("H","E","P"),"HEP","OC")
ORDER BY [Year of Entry], 
         [Course Code], 
         Status;

Test Data Extract:

AppNo Timestamp Course Code AreaOfStudyCode AOS_Description Decision Reply Status Year of Entry Agency Student Status
90000001 01/10/2008 TLT3 88Y3 BA (Hons) U F UF 2009 U H
90000002 02/10/2008 TLT3 88Y3 BA (Hons) C D CD 2009 U E
90000003 03/10/2008 TLT3 88Y3 BA (Hons)   OS 2009 U P
90000018 18/10/2008 TLT3 88Y3 BA (Hons) C  C 2009 U P
90000019 19/10/2008 TLT3 88Y3 BA (Hons) C  C 2009 U O

Result Extract:

Year of Entry  Course Code  Status  Group  N
2009            TLT3         C      HEP    1
2009            TLT3         C      OC     1
2009            TLT3         CD     HEP    1
2009            TLT3         OS     HEP    1
2009            TLT3         U      OC     2
2009            TLT3         UD     HEP    1
2009            TLT3         UF     HEP    2
2009            TLT3         UI     HEP    1
2009            TLT3         W      OC     1

Perhaps of more use would be a crosstab query which can summarise this dataset by status:

SELECT    [Year of Entry], 
          [Course Code], 
          [Status], 
          IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group], 
          Count(AppNo) AS N
TRANSFORM CLng(Nz(Count([AppNo]),0)) AS N
SELECT    [Year of Entry],   
          [Course Code], 
          IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group]
FROM Apps
GROUP BY  [Year of Entry],   
          [Course Code], 
          IIf([Student Status] In ("H","E","P"),"HEP","OC")
ORDER BY  [Year of Entry],   
          [Course Code],   
          Status
PIVOT     Status;

Results:

Year of Entry  Course Code  Group  C  CD  CF  OS   R   U  UD  UF  UI   W
2009           TLT3         HEP    1   1   0   1   0   0   1   2   1   0
2009           TLT3         OC     1   0   0   0   0   2   0   0   0   1
2009           TLT4         HEP    0   0   0   0   0   0   0   2   1   0
2009           TLT4         OC     0   0   0   1   0   0   0   0   0   1
2009           TLT5         HEP    0   0   0   0   2   0   0   2   0   1
2009           TLT5         OC     0   0   2   0   0   0   0   2   0   0

These are just two of the many possibilities which you can experiment with. Good luck in your investigations.

-Stewart

[Mysterydave]

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

Happy New Year