creating a sort field

G

Guest

I am working on creating a new process to creating student accounts.
Sometimes we get students who enroll in more than one course at a time. Our
course management system gets cranky when I upload my spreadsheet and it
contains the same user more than once. To get around this, we have 3
different upload spread sheets. I need access to recognize that a student or
two are enrolled in more than one course.

Basically what I am trying to do is I am creating a query that will create a
new field called "Sort". I am hoping the query will look something like this:

First Last Crse # ID# Sort
Jon Doe EH106 12 1
Jon Doe MA105 12 2
Mark Doe HY 101 11 1
Mary Doe EH106 10 1
Mary Doe EH204 10 2

What this will eventaully set up is I will create 3 queries that will pull
only those who have the # 1 in the sort field and another for those with a #2
in the sort field and so on. I just need some help on how to get access to
create that sort field for me.

Thanks!
 
G

Guest

Access recognizes that by having more than one record in the table. There is
no need to add an extra column. You have not explained why you need this
extra column and what you are going to do with it. You can probably do what
you want without creating an extra column since you can use the GROUP BY and
COUNT in your SQL query.

-Dorian
 
G

Guest

I want to run a 3 queries (qryUpload1, qryUpload2, qryUpload3) that will
export to an excel spreadsheet so I can upload the files. Upload 1 query will
only pull those with the # 1 in the "Sort" column and Upload 2 will pull
those with a 2 in the sort coulumn and so forth.
 
T

Tom Ellison

Dear Justin:

Perhaps I could show you how to add the Sort column. Please provide the SQL
to a query that gives you all the other column you show, and I'll try to add
that.

Tom Ellison
 
G

Guest

Thanks for the replies, guys!

Here's the SQL for my query:

SELECT qryCompareData.Concatenated, qryCompareData.ScanData.FIRST_NAME,
qryCompareData.ScanData.LAST_NAME, qryCompareData.ScanData.SOCIAL_SEC_NUMBER,
qryCompareData.ScanData.ID_NUMBER, qryCompareData.ScanData.ADDRESS,
qryCompareData.ScanData.COURSE_NUMBER,
qryCompareData.ScanData.FIRST_NAME0001,
qryCompareData.ScanData.LAST_NAME0001,
CDate(Format([ScanData.FIRST_REQ_DATE],"0000\/00\/00")) AS [Start Date],
CDate(Format([ScanData.FIRST_REQ_DATE0001],"0000\/00\/00")) AS [End Date],
CourseData.InstructorEMail, CourseData.RegisteredCourses, CourseData.Courses,
CourseData.CourseName, qryCompareData.[WebCT ID], qryCompareData.Password,
CourseData.[Online/CD ROM]
FROM qryCompareData INNER JOIN CourseData ON qryCompareData.Concatenated =
CourseData.Concatenated;


All of this information is used for our mail merge file.

Thanks for the help!
 
T

Tom Ellison

Dear Justin:

I suggest you keep the query just as you have it and write another query
based on it. I'll call the query you have so far "Query1". If you use some
other name, change the code accordingly.

SELECT *,
(SELECT COUNT(*)
FROM Query1 Q1
WHERE Q1.LAST_NAME = Q.LAST_NAME
AND Q1.FIRST_NAME = Q.FIRST_NAME
AND Q1.COURSE_NUMBER < Q.COURSE_NUMBER)
+ 1 AS Sort
FROM Query1 Q
ORDER BY LAST_NAME, FIRST_NAME, COURSE_NUMBER

There is an assumption that no student will take the same course twice.
Otherwise, there would be a tie in the ranking (Sort).

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


Justin said:
Thanks for the replies, guys!

Here's the SQL for my query:

SELECT qryCompareData.Concatenated, qryCompareData.ScanData.FIRST_NAME,
qryCompareData.ScanData.LAST_NAME,
qryCompareData.ScanData.SOCIAL_SEC_NUMBER,
qryCompareData.ScanData.ID_NUMBER, qryCompareData.ScanData.ADDRESS,
qryCompareData.ScanData.COURSE_NUMBER,
qryCompareData.ScanData.FIRST_NAME0001,
qryCompareData.ScanData.LAST_NAME0001,
CDate(Format([ScanData.FIRST_REQ_DATE],"0000\/00\/00")) AS [Start Date],
CDate(Format([ScanData.FIRST_REQ_DATE0001],"0000\/00\/00")) AS [End Date],
CourseData.InstructorEMail, CourseData.RegisteredCourses,
CourseData.Courses,
CourseData.CourseName, qryCompareData.[WebCT ID], qryCompareData.Password,
CourseData.[Online/CD ROM]
FROM qryCompareData INNER JOIN CourseData ON qryCompareData.Concatenated =
CourseData.Concatenated;


All of this information is used for our mail merge file.

Thanks for the help!


Tom Ellison said:
Dear Justin:

Perhaps I could show you how to add the Sort column. Please provide the
SQL
to a query that gives you all the other column you show, and I'll try to
add
that.

Tom Ellison
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,




SELECT a.FirstName, a.LastName, a.Course, COUNT(*) as Sort
FROM myTable As a INNER JOIN myTable As b
ON a.FirstName=b.FirstName
AND a.LastName = b.Lastname
AND a.Course <= b.Course
GROUP BY a.FirstName, a.LastName, a.Course




I have no clue about the "id" field. I also assumed there is no duplicated
{ FirstName, .LastName, Course } group (ie, a student have not registered
twice to a given course, or two different students, but with the same name,
having registered to a same given course).


Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Tom,

Thanks for the reply. I run a compare query before I run the query I just
showed you. This compare query compares the new student data that I receive
to a table that contains all of the students I have already set up. So to
answer your assumption, no students will not be put in to the same course
twice.

I'll give this a try and let you know how it works.

Tom Ellison said:
Dear Justin:

I suggest you keep the query just as you have it and write another query
based on it. I'll call the query you have so far "Query1". If you use some
other name, change the code accordingly.

SELECT *,
(SELECT COUNT(*)
FROM Query1 Q1
WHERE Q1.LAST_NAME = Q.LAST_NAME
AND Q1.FIRST_NAME = Q.FIRST_NAME
AND Q1.COURSE_NUMBER < Q.COURSE_NUMBER)
+ 1 AS Sort
FROM Query1 Q
ORDER BY LAST_NAME, FIRST_NAME, COURSE_NUMBER

There is an assumption that no student will take the same course twice.
Otherwise, there would be a tie in the ranking (Sort).

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


Justin said:
Thanks for the replies, guys!

Here's the SQL for my query:

SELECT qryCompareData.Concatenated, qryCompareData.ScanData.FIRST_NAME,
qryCompareData.ScanData.LAST_NAME,
qryCompareData.ScanData.SOCIAL_SEC_NUMBER,
qryCompareData.ScanData.ID_NUMBER, qryCompareData.ScanData.ADDRESS,
qryCompareData.ScanData.COURSE_NUMBER,
qryCompareData.ScanData.FIRST_NAME0001,
qryCompareData.ScanData.LAST_NAME0001,
CDate(Format([ScanData.FIRST_REQ_DATE],"0000\/00\/00")) AS [Start Date],
CDate(Format([ScanData.FIRST_REQ_DATE0001],"0000\/00\/00")) AS [End Date],
CourseData.InstructorEMail, CourseData.RegisteredCourses,
CourseData.Courses,
CourseData.CourseName, qryCompareData.[WebCT ID], qryCompareData.Password,
CourseData.[Online/CD ROM]
FROM qryCompareData INNER JOIN CourseData ON qryCompareData.Concatenated =
CourseData.Concatenated;


All of this information is used for our mail merge file.

Thanks for the help!


Tom Ellison said:
Dear Justin:

Perhaps I could show you how to add the Sort column. Please provide the
SQL
to a query that gives you all the other column you show, and I'll try to
add
that.

Tom Ellison


I am working on creating a new process to creating student accounts.
Sometimes we get students who enroll in more than one course at a time.
Our
course management system gets cranky when I upload my spreadsheet and
it
contains the same user more than once. To get around this, we have 3
different upload spread sheets. I need access to recognize that a
student
or
two are enrolled in more than one course.

Basically what I am trying to do is I am creating a query that will
create
a
new field called "Sort". I am hoping the query will look something like
this:

First Last Crse # ID# Sort
Jon Doe EH106 12 1
Jon Doe MA105 12 2
Mark Doe HY 101 11 1
Mary Doe EH106 10 1
Mary Doe EH204 10 2

What this will eventaully set up is I will create 3 queries that will
pull
only those who have the # 1 in the sort field and another for those
with a
#2
in the sort field and so on. I just need some help on how to get access
to
create that sort field for me.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tom,

It worked! Thank you all for your help!

~ Justin

Justin said:
Tom,

Thanks for the reply. I run a compare query before I run the query I just
showed you. This compare query compares the new student data that I receive
to a table that contains all of the students I have already set up. So to
answer your assumption, no students will not be put in to the same course
twice.

I'll give this a try and let you know how it works.

Tom Ellison said:
Dear Justin:

I suggest you keep the query just as you have it and write another query
based on it. I'll call the query you have so far "Query1". If you use some
other name, change the code accordingly.

SELECT *,
(SELECT COUNT(*)
FROM Query1 Q1
WHERE Q1.LAST_NAME = Q.LAST_NAME
AND Q1.FIRST_NAME = Q.FIRST_NAME
AND Q1.COURSE_NUMBER < Q.COURSE_NUMBER)
+ 1 AS Sort
FROM Query1 Q
ORDER BY LAST_NAME, FIRST_NAME, COURSE_NUMBER

There is an assumption that no student will take the same course twice.
Otherwise, there would be a tie in the ranking (Sort).

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


Justin said:
Thanks for the replies, guys!

Here's the SQL for my query:

SELECT qryCompareData.Concatenated, qryCompareData.ScanData.FIRST_NAME,
qryCompareData.ScanData.LAST_NAME,
qryCompareData.ScanData.SOCIAL_SEC_NUMBER,
qryCompareData.ScanData.ID_NUMBER, qryCompareData.ScanData.ADDRESS,
qryCompareData.ScanData.COURSE_NUMBER,
qryCompareData.ScanData.FIRST_NAME0001,
qryCompareData.ScanData.LAST_NAME0001,
CDate(Format([ScanData.FIRST_REQ_DATE],"0000\/00\/00")) AS [Start Date],
CDate(Format([ScanData.FIRST_REQ_DATE0001],"0000\/00\/00")) AS [End Date],
CourseData.InstructorEMail, CourseData.RegisteredCourses,
CourseData.Courses,
CourseData.CourseName, qryCompareData.[WebCT ID], qryCompareData.Password,
CourseData.[Online/CD ROM]
FROM qryCompareData INNER JOIN CourseData ON qryCompareData.Concatenated =
CourseData.Concatenated;


All of this information is used for our mail merge file.

Thanks for the help!


:

Dear Justin:

Perhaps I could show you how to add the Sort column. Please provide the
SQL
to a query that gives you all the other column you show, and I'll try to
add
that.

Tom Ellison


I am working on creating a new process to creating student accounts.
Sometimes we get students who enroll in more than one course at a time.
Our
course management system gets cranky when I upload my spreadsheet and
it
contains the same user more than once. To get around this, we have 3
different upload spread sheets. I need access to recognize that a
student
or
two are enrolled in more than one course.

Basically what I am trying to do is I am creating a query that will
create
a
new field called "Sort". I am hoping the query will look something like
this:

First Last Crse # ID# Sort
Jon Doe EH106 12 1
Jon Doe MA105 12 2
Mark Doe HY 101 11 1
Mary Doe EH106 10 1
Mary Doe EH204 10 2

What this will eventaully set up is I will create 3 queries that will
pull
only those who have the # 1 in the sort field and another for those
with a
#2
in the sort field and so on. I just need some help on how to get access
to
create that sort field for me.

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

Sort by number of unique entries in a datafield 3
dynamic calculated field in query 3
Crosstab w/ aggregate criteria 4
Form field lookups to next record-compex 0
Multi-Value Field Query 0
Help with Concatenated / Nested Query results 1
Creating a Data Source 5
hide duplicates in a query 2

Top