Count Number of Apps and Statuses

[NY2DR]

Hello, I need help with the following:

I have 1512 Applications, Which can have a status of Enrolled, Completed, Expired, Incomplete, etc,.

The same application can have one or more statuses.

In other words, I have 1529 statuses, and 1512 applications.
I can't seem to count the #Apps for each status:think:

I would like this to show up in a form, once I get the query right, like below, with the date.

Date Application Status Number of Status Listed #Apps:help
01-Oct-07 COMPLETED 259 255
01-Oct-07 DISENROLLED 9 7
01-Oct-07 ENROLLED 1 1
01-Oct-07 EXPIRED 1 1
01-Oct-07 INCOMPLETE 39 35
01-Oct-07 MIXED 16 15
01-Oct-07 PENDED 782 776
01-Oct-07PRESUMPTIVE 1 1
01-Oct-07 REJECTED 421 421
total 1529 1512
P.S. This is ALL in the same table.

Thank you

[stanl]

The same application can have one or more statuses.


How is the data kept (in 1 table); assume you have applicationID and status fields...

would you have
ApplicationID Status <--- 1 row per status/allow duplicate ID's
1001 DISENROLLED
1001 REJECTED

or something like
ApplicationID Status <---ID as Primary key, multiple statuses
1001 DISENROLLED,REJECTED

It makes a difference. .02 Stan

[NY2DR]

Hello Stanl,

Thanks for replying.

I have it as:
ApplicationID Status <--- 1 row per status/allow duplicate ID's
DISENROLLED
REJECTED
No Primary key.

It's more like the table below

Date-------Application Status-Number of Status Listed---#Apps
01-Oct-07--COMPLETED--------------------------259-----255
01-Oct-07--INCOMPLETE---------------------------39-----35
01-Oct-07--REJECTED-----------------------------421----421

I just need to find out how many apps per status. This will be a great learning experience for me using Access. Of course, the number of apps will be the same or less than the number of statuses listed.

Thanks