Create a numerical sequence based on second field

[KiwiPhD]

Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student ID GUID
al003072 274
al154636 303
al154636 463
al595048 272
al941626 429
am180614 426
am750033 300
am750033 462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student ID GUID Sequence
al003072 274 1
al154636 303 1
al154636 463 2
al595048 272 1
al941626 429 1
am180614 426 1
am750033 300 1
am750033 462 2
Any help with this SQL is greatly appreciated.

 

[Uncle Gizmo]

Why do some end with 1 and some end with 2?

 

[KiwiPhD]

Thank you for helping.
Some students took one quiz and other students took two quizzes. You can see the instances of the student ID. So, students may take the quiz once, twice, three times, etc

 

[vbaInet]

1. Sort your original query by StudentID and GUID in ascending order
2. Save that query, giving it a name
3. Create a new query based on the query in step 2 and select all the fields in it
4. Add this as a new field in the new query replace QueryName with the name of the query from step 2:

Sequence: (SELECT Count(*) 
           FROM [COLOR="blue"]QueryName [/COLOR]AS A 
           WHERE A.[Student ID] = [COLOR="blue"]QueryName[/COLOR].[Student ID] AND
                 A.GUID <= [COLOR="blue"]QueryName[/COLOR].GUID
           GROUP BY A.[Student ID]
           ORDER BY A.GUID)

 

[jdraw]

Kiwi,

Can you post a jpg of your tables? You may have to make a zip file because of your post count.

 

[KiwiPhD]

I believe I followed the 4 steps correctly. I receive error message "You tried to execute query ... does not include A.GUID as part of an aggregate function".
SELECT Query1.[Student ID], Query1.GUID,
(SELECT Count(*)
FROM Query1 AS A
WHERE A.[Student ID] = Query1.[Student ID] AND
A.GUID <= Query1.GUID
GROUP BY A.[Student ID]
ORDER BY A.GUID) AS Expr1
FROM Query1
So I amended SQL to:
SELECT Query1.[Student ID], Query1.GUID,
(SELECT Count(*)
FROM Query1 AS A
WHERE A.[Student ID] = Query1.[Student ID] AND
A.GUID <= Query1.GUID
GROUP BY A.[Student ID], A.GUID
ORDER BY A.GUID) AS Expr1
FROM Query1
New error message "Invalid argument to function".

 

[KiwiPhD]

I tried to post a link to a jpg - but I do not have the requisite 10 post, yet.

 

[vbaInet]

Replace the GROUP BY:

GROUP BY A.[Student ID], A.GUID

 

[KiwiPhD]

Thanks vbaInet - I did that (see above) and got new error message "Invalid argument to function"

 

[vbaInet]

I wrote that in haste. Here:

SELECT Query1.[Student ID], Query1.GUID, 
       (SELECT Count(*) 
        FROM Query1 AS A 
        WHERE A.[Student ID] = Query1.[Student ID] AND
              A.GUID <= Query1.GUID) AS Sequence
FROM Query1

 

[KiwiPhD]

Thanks vbaInet
SELECT Query1.[Student ID], Query1.GUID,
(SELECT Count(*)
FROM Query1 AS A
WHERE A.[Student ID] = Query1.[Student ID] AND
A.GUID <= Query1.GUID) AS Sequence
FROM Query1
Gives error "Invalid argument to function"

 

[KiwiPhD]

Thanks vbaInet - this works - location of GROUP BY
SELECT Query1.[Student ID], Query1.GUID,
(SELECT Count(*)
FROM Query1 AS A
WHERE A.[Student ID] = Query1.[Student ID] AND
A.GUID <= Query1.GUID) AS Sequence
FROM Query1
GROUP BY Query1.[Student ID], Query1.GUID

 

[vbaInet]

Let's see the SQL of Query1.

 

[vbaInet]

Thanks vbaInet - this works - location of GROUP BY

You shouldn't need the GROUP BY clause there. Can you upload a sample db?

 

[KiwiPhD]

Query1 SQL
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Student INNER JOIN (Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]) ON Student.[Student ID] = StudentHeader.[Student ID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2) AND ((Student.Active)=True))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;

 

[KiwiPhD]

The Access db is live - 200 students interacting with it 24/7
The SQL of this thread is for an elaborate report I am creating.

 

[vbaInet]

Take a copy of the two tables (structure only) and enter some test data.

 

[KiwiPhD]

Will post link to "reduced" Access database.

 

[KiwiPhD]

http://www.bus.ucf.edu/faculty/pgoldwater/file.axd?file=2014%2f8%2fAssignmentSystem.accdb

Access - has relevant two tables and two queries. Thanks vbaInet

 

[KiwiPhD]

vbaInet - this service automatically abbreviated my link. Consequently the link is not valid.