Need help to create code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help figuring out how can I create a new column in a query that counts
how many times a students number is used in the different records. So for the
first time it will place a 1 in the new column, the second time it finds the
student number a 2, and so on.
 
hi,

you can use this query:
SELECT tablename.studentnumber, Count(tablename.studentnumber) AS
pickanamefornewcolumn
FROM tablename
GROUP BY tablename.pickanamefornewcolumn;
 
Thanks, the query did count and give a total for how many times a student
number was given but I am trying to do something like this.

1234 1
1232 1
1234 2
1234 3
2225 1

and so throughout the table.
 
hi,

Just trying to figure out why you are pasting an arbitrary number in the new
column. To me, it seems that you are essentially putting a "count" in the
new column. I have a question...what is the maximum number of times a
student can appear? (if there is a maximum).

Anyway, yo umay want to try something like this:

SELECT tbl_name.fieldname,
IIf(Count([fieldname])=1,"1",IIf(Count([fieldname])=2,"2",IIf(Count([fieldname])=3,"3","4+"))) AS column_name_of_your_choice
FROM tbl_name
GROUP BY tbl_name.fieldname;

Let me know if you need more help!

geebee
 
There is no maximum. I am tring to create a query that has only one row per
student with all the parent and emergency contact info in the same record.
The problem is each parent has it's own record in the parent table with the
student ID in a field attaching it to the student. So a student appears as
many times as he or she has parent and emergency contacts. I am wanting
maybe three contacts and then it stops.

Your last post helps a lot thank you.

geebee said:
hi,

Just trying to figure out why you are pasting an arbitrary number in the new
column. To me, it seems that you are essentially putting a "count" in the
new column. I have a question...what is the maximum number of times a
student can appear? (if there is a maximum).

Anyway, yo umay want to try something like this:

SELECT tbl_name.fieldname,
IIf(Count([fieldname])=1,"1",IIf(Count([fieldname])=2,"2",IIf(Count([fieldname])=3,"3","4+"))) AS column_name_of_your_choice
FROM tbl_name
GROUP BY tbl_name.fieldname;

Let me know if you need more help!

geebee





FrankM said:
Thanks, the query did count and give a total for how many times a student
number was given but I am trying to do something like this.

1234 1
1232 1
1234 2
1234 3
2225 1

and so throughout the table.
Click to expand...
Click to expand...
 
Back
Top