By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Counting using different criteria

P: 28
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
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,418
@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

P: 28
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

P: 28
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

Expert Mod 2.5K+
P: 2,545
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

P: 28
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

Expert Mod 2.5K+
P: 2,545
Hi. Assuming for test purposes that the table is called Apps, here is a count query that does what you mention above:

Expand|Select|Wrap|Line Numbers
  1. SELECT   [Year of Entry], 
  2.          [Course Code], 
  3.          [Status], 
  4.          IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group], 
  5.          Count(AppNo) AS N
  6. FROM     Apps
  7. GROUP BY [Year of Entry], 
  8.          [Course Code], 
  9.          Status, 
  10.          IIf([Student Status] In ("H","E","P"),"HEP","OC")
  11. ORDER BY [Year of Entry], 
  12.          [Course Code], 
  13.          Status;
Test Data Extract:
Expand|Select|Wrap|Line Numbers
  1. AppNo Timestamp Course Code AreaOfStudyCode AOS_Description Decision Reply Status Year of Entry Agency Student Status
  2. 90000001 01/10/2008 TLT3 88Y3 BA (Hons) U F UF 2009 U H
  3. 90000002 02/10/2008 TLT3 88Y3 BA (Hons) C D CD 2009 U E
  4. 90000003 03/10/2008 TLT3 88Y3 BA (Hons)   OS 2009 U P
  5. 90000018 18/10/2008 TLT3 88Y3 BA (Hons) C  C 2009 U P
  6. 90000019 19/10/2008 TLT3 88Y3 BA (Hons) C  C 2009 U O
Result Extract:
Expand|Select|Wrap|Line Numbers
  1. Year of Entry  Course Code  Status  Group  N
  2. 2009            TLT3         C      HEP    1
  3. 2009            TLT3         C      OC     1
  4. 2009            TLT3         CD     HEP    1
  5. 2009            TLT3         OS     HEP    1
  6. 2009            TLT3         U      OC     2
  7. 2009            TLT3         UD     HEP    1
  8. 2009            TLT3         UF     HEP    2
  9. 2009            TLT3         UI     HEP    1
  10. 2009            TLT3         W      OC     1
Perhaps of more use would be a crosstab query which can summarise this dataset by status:
Expand|Select|Wrap|Line Numbers
  1. SELECT    [Year of Entry], 
  2.           [Course Code], 
  3.           [Status], 
  4.           IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group], 
  5.           Count(AppNo) AS N
  6. TRANSFORM CLng(Nz(Count([AppNo]),0)) AS N
  7. SELECT    [Year of Entry],   
  8.           [Course Code], 
  9.           IIf([Student Status] In ("H","E","P"),"HEP","OC") AS [Group]
  10. FROM Apps
  11. GROUP BY  [Year of Entry],   
  12.           [Course Code], 
  13.           IIf([Student Status] In ("H","E","P"),"HEP","OC")
  14. ORDER BY  [Year of Entry],   
  15.           [Course Code],   
  16.           Status
  17. PIVOT     Status;
Results:
Expand|Select|Wrap|Line Numbers
  1. Year of Entry  Course Code  Group  C  CD  CF  OS   R   U  UD  UF  UI   W
  2. 2009           TLT3         HEP    1   1   0   1   0   0   1   2   1   0
  3. 2009           TLT3         OC     1   0   0   0   0   2   0   0   0   1
  4. 2009           TLT4         HEP    0   0   0   0   0   0   0   2   1   0
  5. 2009           TLT4         OC     0   0   0   1   0   0   0   0   0   1
  6. 2009           TLT5         HEP    0   0   0   0   2   0   0   2   0   1
  7. 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
Jan 5 '09 #7

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

Happy New Year
Jan 12 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.