Approach to

G

Guest

I have a query with Name, ClassNumber, CourseName, RegistrationDate,
WaitlistDate, MovedUpFromWL, MovedDownToWL, and CancelDate. I'd like to take
the information from the query and arrange it chronologically to see a
student's registration activity in a form.

Any ideas as to how to approach getting this
Name ClassNumber CourseName RegistrationDate WaitlistDate MovedUpFromWL MovedDownToWL CancelDate
Susan Michalek 054620 TEI CRDM Tachy
Concepts 30-Nov-05 null null null 09-Mar-06
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 26-Jan-06 null null 13-Feb-06
Susan Michalek 054619 TEI CRDM Advanced Brady
Concepts 30-Nov-05 null null null 23-Jan-06
Susan Michalek 056284 TEI CRDM Technology
Overview 14-Dec-05 null null null 09-Jan-06
Susan Michalek 054523 TEI CRDM Technology
Overview 14-Nov-05 null null null 01-Jan-06
Susan Michalek 054612 TEI CRDM
Foundations null 14-Nov-05 16-Nov-05 null 29-Dec-05
Susan Michalek 053354 TEI CRDM Technology
Overview null 15-Nov-05 null null 14-Dec-05
Susan Michalek 054517 TEI CRDM Technology
Overview null 14-Nov-05 06-Jan-06 null null
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 14-Feb-06 15-Feb-06 null null
Susan Michalek 057111 TEI CRDM Advanced Brady
Concepts 01-Feb-06 null null null null

to come out like this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06

I'd like to be able to doubleclick a student name on another form and open
this form with the activity information. Your help is appreciated.

Anne
 
G

Guest

Actually, I want the outcome to reflect what the activity was for -
registering, cancelling, etc. So that the form's information has this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05 Registered
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05 Registered
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05 Registered
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05 Registered
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06 Registered
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05 Waitlisted
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05 Waitlisted
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05 Waitlisted
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05 Moved Up from Waitlist
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06 Moved Up from
Waitlist
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06 Moved Up
from Waitlist
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06 Cancelled
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06 Cancelled
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06 Cancelled
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06 Cancelled
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06 Cancelled
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05 Cancelled
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05 Cancelled
 
G

Guest

This is all about correct sorting of the recordset. Since you only show
Susan in your example, I assume this is filtering by Student, so sorting by
Student may or may not be important. It appears the primary sorting here is
by Activity, Registered, WaitListed, etc. These, in their native form,
cannot be sorted. There is a way to do this. That is to create a calculated
field in your query that will assign a value to each Activity using the
Switch function. Then sort on that calculated field. for Example:
ActivityOrder: Switch([Activity] = "Registered", 1, [Activity] =
"WaitListed", 2, [Activity] = "Moved Up", 3, [Activity] = "Moved Down", 4,
[Activity] = "Canceled",5)

Be Aware the Switch function will return Null if none of the conditions are
True.
 
G

Guest

Thank you very much for your speedy reply. I can created a calculated field
in the query, but I am confused on this point: I have not recorded each
activity in a separate record; the registration is in one field, the
cancellation in another field of the same record. I am wondering how I can
capture each of the activities on a separate "line".

Klatuu said:
This is all about correct sorting of the recordset. Since you only show
Susan in your example, I assume this is filtering by Student, so sorting by
Student may or may not be important. It appears the primary sorting here is
by Activity, Registered, WaitListed, etc. These, in their native form,
cannot be sorted. There is a way to do this. That is to create a calculated
field in your query that will assign a value to each Activity using the
Switch function. Then sort on that calculated field. for Example:
ActivityOrder: Switch([Activity] = "Registered", 1, [Activity] =
"WaitListed", 2, [Activity] = "Moved Up", 3, [Activity] = "Moved Down", 4,
[Activity] = "Canceled",5)

Be Aware the Switch function will return Null if none of the conditions are
True.

Anne said:
Actually, I want the outcome to reflect what the activity was for -
registering, cancelling, etc. So that the form's information has this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05 Registered
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05 Registered
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05 Registered
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05 Registered
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06 Registered
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05 Waitlisted
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05 Waitlisted
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05 Waitlisted
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05 Moved Up from Waitlist
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06 Moved Up from
Waitlist
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06 Moved Up
from Waitlist
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06 Cancelled
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06 Cancelled
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06 Cancelled
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06 Cancelled
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06 Cancelled
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05 Cancelled
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05 Cancelled
Click to expand...
Click to expand...
 
G

Guest

Oh no, you have a real problem. That is that your data structure is not well
normalized. Each row in what you posted should be a separate record in your
table. If you think about it, each of the Activities is a separate event and
should be recorded that way. What about the date? How do you know what date
corresponds with what Activity? Are there separate date fields for each
Activity?
Please post back the record layout of this table, and let's see if there is
a way we can construct a query to give you a separate row for each Activity.

Anne said:
Thank you very much for your speedy reply. I can created a calculated field
in the query, but I am confused on this point: I have not recorded each
activity in a separate record; the registration is in one field, the
cancellation in another field of the same record. I am wondering how I can
capture each of the activities on a separate "line".

Klatuu said:
This is all about correct sorting of the recordset. Since you only show
Susan in your example, I assume this is filtering by Student, so sorting by
Student may or may not be important. It appears the primary sorting here is
by Activity, Registered, WaitListed, etc. These, in their native form,
cannot be sorted. There is a way to do this. That is to create a calculated
field in your query that will assign a value to each Activity using the
Switch function. Then sort on that calculated field. for Example:
ActivityOrder: Switch([Activity] = "Registered", 1, [Activity] =
"WaitListed", 2, [Activity] = "Moved Up", 3, [Activity] = "Moved Down", 4,
[Activity] = "Canceled",5)

Be Aware the Switch function will return Null if none of the conditions are
True.

Anne said:
Actually, I want the outcome to reflect what the activity was for -
registering, cancelling, etc. So that the form's information has this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05 Registered
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05 Registered
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05 Registered
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05 Registered
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06 Registered
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05 Waitlisted
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05 Waitlisted
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05 Waitlisted
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05 Moved Up from Waitlist
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06 Moved Up from
Waitlist
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06 Moved Up
from Waitlist
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06 Cancelled
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06 Cancelled
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06 Cancelled
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06 Cancelled
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06 Cancelled
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05 Cancelled
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05 Cancelled


:

I have a query with Name, ClassNumber, CourseName, RegistrationDate,
WaitlistDate, MovedUpFromWL, MovedDownToWL, and CancelDate. I'd like to take
the information from the query and arrange it chronologically to see a
student's registration activity in a form.

Any ideas as to how to approach getting this:
Name ClassNumber CourseName RegistrationDate WaitlistDate MovedUpFromWL MovedDownToWL CancelDate
Susan Michalek 054620 TEI CRDM Tachy
Concepts 30-Nov-05 null null null 09-Mar-06
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 26-Jan-06 null null 13-Feb-06
Susan Michalek 054619 TEI CRDM Advanced Brady
Concepts 30-Nov-05 null null null 23-Jan-06
Susan Michalek 056284 TEI CRDM Technology
Overview 14-Dec-05 null null null 09-Jan-06
Susan Michalek 054523 TEI CRDM Technology
Overview 14-Nov-05 null null null 01-Jan-06
Susan Michalek 054612 TEI CRDM
Foundations null 14-Nov-05 16-Nov-05 null 29-Dec-05
Susan Michalek 053354 TEI CRDM Technology
Overview null 15-Nov-05 null null 14-Dec-05
Susan Michalek 054517 TEI CRDM Technology
Overview null 14-Nov-05 06-Jan-06 null null
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 14-Feb-06 15-Feb-06 null null
Susan Michalek 057111 TEI CRDM Advanced Brady
Concepts 01-Feb-06 null null null null

to come out like this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06

I'd like to be able to doubleclick a student name on another form and open
this form with the activity information. Your help is appreciated.

Anne
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks again for all your help. I did come up with a solution - a union query.

Thanks, too, for you comments about normalization. I had set up the table
to have a record for each student for each class and for the fields to
individually reflect the activity dates. This happened mainly because I was
originally tracking this stuff in excel and didn't want to take much time to
restructure the data. I may have to give that more thought as I go forward.
In the meantime, I have my solution.

Klatuu said:
Oh no, you have a real problem. That is that your data structure is not well
normalized. Each row in what you posted should be a separate record in your
table. If you think about it, each of the Activities is a separate event and
should be recorded that way. What about the date? How do you know what date
corresponds with what Activity? Are there separate date fields for each
Activity?
Please post back the record layout of this table, and let's see if there is
a way we can construct a query to give you a separate row for each Activity.

Anne said:
Thank you very much for your speedy reply. I can created a calculated field
in the query, but I am confused on this point: I have not recorded each
activity in a separate record; the registration is in one field, the
cancellation in another field of the same record. I am wondering how I can
capture each of the activities on a separate "line".

Klatuu said:
This is all about correct sorting of the recordset. Since you only show
Susan in your example, I assume this is filtering by Student, so sorting by
Student may or may not be important. It appears the primary sorting here is
by Activity, Registered, WaitListed, etc. These, in their native form,
cannot be sorted. There is a way to do this. That is to create a calculated
field in your query that will assign a value to each Activity using the
Switch function. Then sort on that calculated field. for Example:
ActivityOrder: Switch([Activity] = "Registered", 1, [Activity] =
"WaitListed", 2, [Activity] = "Moved Up", 3, [Activity] = "Moved Down", 4,
[Activity] = "Canceled",5)

Be Aware the Switch function will return Null if none of the conditions are
True.

:

Actually, I want the outcome to reflect what the activity was for -
registering, cancelling, etc. So that the form's information has this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05 Registered
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05 Registered
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05 Registered
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05 Registered
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06 Registered
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05 Waitlisted
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05 Waitlisted
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05 Waitlisted
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05 Moved Up from Waitlist
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06 Moved Up from
Waitlist
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06 Moved Up
from Waitlist
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06 Cancelled
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06 Cancelled
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06 Cancelled
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06 Cancelled
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06 Cancelled
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05 Cancelled
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05 Cancelled


:

I have a query with Name, ClassNumber, CourseName, RegistrationDate,
WaitlistDate, MovedUpFromWL, MovedDownToWL, and CancelDate. I'd like to take
the information from the query and arrange it chronologically to see a
student's registration activity in a form.

Any ideas as to how to approach getting this:
Name ClassNumber CourseName RegistrationDate WaitlistDate MovedUpFromWL MovedDownToWL CancelDate
Susan Michalek 054620 TEI CRDM Tachy
Concepts 30-Nov-05 null null null 09-Mar-06
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 26-Jan-06 null null 13-Feb-06
Susan Michalek 054619 TEI CRDM Advanced Brady
Concepts 30-Nov-05 null null null 23-Jan-06
Susan Michalek 056284 TEI CRDM Technology
Overview 14-Dec-05 null null null 09-Jan-06
Susan Michalek 054523 TEI CRDM Technology
Overview 14-Nov-05 null null null 01-Jan-06
Susan Michalek 054612 TEI CRDM
Foundations null 14-Nov-05 16-Nov-05 null 29-Dec-05
Susan Michalek 053354 TEI CRDM Technology
Overview null 15-Nov-05 null null 14-Dec-05
Susan Michalek 054517 TEI CRDM Technology
Overview null 14-Nov-05 06-Jan-06 null null
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 14-Feb-06 15-Feb-06 null null
Susan Michalek 057111 TEI CRDM Advanced Brady
Concepts 01-Feb-06 null null null null

to come out like this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06

I'd like to be able to doubleclick a student name on another form and open
this form with the activity information. Your help is appreciated.

Anne
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Glad you solved it. That is a common problem coming from Excel to a database
without experience or formal training in database design. Going forward, do
some reading on database design and think in database terms rather than
spreadsheet terms.

Please understand this is not a criticism of you, but a helpful pointer to
make your life easier.

Anne said:
Thanks again for all your help. I did come up with a solution - a union query.

Thanks, too, for you comments about normalization. I had set up the table
to have a record for each student for each class and for the fields to
individually reflect the activity dates. This happened mainly because I was
originally tracking this stuff in excel and didn't want to take much time to
restructure the data. I may have to give that more thought as I go forward.
In the meantime, I have my solution.

Klatuu said:
Oh no, you have a real problem. That is that your data structure is not well
normalized. Each row in what you posted should be a separate record in your
table. If you think about it, each of the Activities is a separate event and
should be recorded that way. What about the date? How do you know what date
corresponds with what Activity? Are there separate date fields for each
Activity?
Please post back the record layout of this table, and let's see if there is
a way we can construct a query to give you a separate row for each Activity.

Anne said:
Thank you very much for your speedy reply. I can created a calculated field
in the query, but I am confused on this point: I have not recorded each
activity in a separate record; the registration is in one field, the
cancellation in another field of the same record. I am wondering how I can
capture each of the activities on a separate "line".

:

This is all about correct sorting of the recordset. Since you only show
Susan in your example, I assume this is filtering by Student, so sorting by
Student may or may not be important. It appears the primary sorting here is
by Activity, Registered, WaitListed, etc. These, in their native form,
cannot be sorted. There is a way to do this. That is to create a calculated
field in your query that will assign a value to each Activity using the
Switch function. Then sort on that calculated field. for Example:
ActivityOrder: Switch([Activity] = "Registered", 1, [Activity] =
"WaitListed", 2, [Activity] = "Moved Up", 3, [Activity] = "Moved Down", 4,
[Activity] = "Canceled",5)

Be Aware the Switch function will return Null if none of the conditions are
True.

:

Actually, I want the outcome to reflect what the activity was for -
registering, cancelling, etc. So that the form's information has this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05 Registered
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05 Registered
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05 Registered
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05 Registered
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06 Registered
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05 Waitlisted
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05 Waitlisted
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05 Waitlisted
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06 Waitlisted
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05 Moved Up from Waitlist
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06 Moved Up from
Waitlist
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06 Moved Up
from Waitlist
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06 Cancelled
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06 Cancelled
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06 Cancelled
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06 Cancelled
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06 Cancelled
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05 Cancelled
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05 Cancelled


:

I have a query with Name, ClassNumber, CourseName, RegistrationDate,
WaitlistDate, MovedUpFromWL, MovedDownToWL, and CancelDate. I'd like to take
the information from the query and arrange it chronologically to see a
student's registration activity in a form.

Any ideas as to how to approach getting this:
Name ClassNumber CourseName RegistrationDate WaitlistDate MovedUpFromWL MovedDownToWL CancelDate
Susan Michalek 054620 TEI CRDM Tachy
Concepts 30-Nov-05 null null null 09-Mar-06
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 26-Jan-06 null null 13-Feb-06
Susan Michalek 054619 TEI CRDM Advanced Brady
Concepts 30-Nov-05 null null null 23-Jan-06
Susan Michalek 056284 TEI CRDM Technology
Overview 14-Dec-05 null null null 09-Jan-06
Susan Michalek 054523 TEI CRDM Technology
Overview 14-Nov-05 null null null 01-Jan-06
Susan Michalek 054612 TEI CRDM
Foundations null 14-Nov-05 16-Nov-05 null 29-Dec-05
Susan Michalek 053354 TEI CRDM Technology
Overview null 15-Nov-05 null null 14-Dec-05
Susan Michalek 054517 TEI CRDM Technology
Overview null 14-Nov-05 06-Jan-06 null null
Susan Michalek 054614 TEI CRDM Advanced Brady
Concepts null 14-Feb-06 15-Feb-06 null null
Susan Michalek 057111 TEI CRDM Advanced Brady
Concepts 01-Feb-06 null null null null

to come out like this:
Susan Michalek 054523 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 14-Nov-05
Susan Michalek 054517 TEI CRDM Technology Overview 14-Nov-05
Susan Michalek 053354 TEI CRDM Technology Overview 15-Nov-05
Susan Michalek 054612 TEI CRDM Foundations 16-Nov-05
Susan Michalek 054620 TEI CRDM Tachy Concepts 30-Nov-05
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 30-Nov-05
Susan Michalek 056284 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 053354 TEI CRDM Technology Overview 14-Dec-05
Susan Michalek 054612 TEI CRDM Foundations 29-Dec-05
Susan Michalek 054523 TEI CRDM Technology Overview 01-Jan-06
Susan Michalek 054517 TEI CRDM Technology Overview 06-Jan-06
Susan Michalek 056284 TEI CRDM Technology Overview 09-Jan-06
Susan Michalek 054619 TEI CRDM Advanced Brady Concepts 23-Jan-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 26-Jan-06
Susan Michalek 057111 TEI CRDM Advanced Brady Concepts 01-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 13-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 14-Feb-06
Susan Michalek 054614 TEI CRDM Advanced Brady Concepts 15-Feb-06
Susan Michalek 054620 TEI CRDM Tachy Concepts 09-Mar-06

I'd like to be able to doubleclick a student name on another form and open
this form with the activity information. Your help is appreciated.

Anne
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine 2 Charts 2007 Excel 1
Averaging seconds 3
creating a table from twenty worksheets 3
When building a chart Dates are been included that are not in thedate range 1
Excel check if dates are under a range of week 4
BSOD minidump analysis 2
BSOD minidump analysis 5
Graphing problem - data with multiple series 1

Top