Hi, I'm still a beginner in programming and the .Recordset stuff is troubling me a bit. Hope you guys can help a bit.
I have a database in which patient appointments are stored in a Table called 'appointments'. Some days we have more than one patient and on others, we have only one or none at all.
For those days with multiple patients, I'd like the database to automatically assign an ID number to each patient on that day. For example, if someone adds three patients, Adam, Bob, and Carl on May 4, Adam would have a value of 1 in the field called 'ID', Bob 2, Carl 3. If Bob is the only one to show up on May 5, [ID]=1.
I've made a count query that shows the number of patients per given appointment date. The query's name is 'DateTally' and that number is displayed in a field called 'Totals'.
I think the way to do this in vB is to execute a FOR EACH NEXT loop somehow, but my syntax ability is terrible.
Dim n as integer
n = 1
FOR EACH [Totals] IN [datetally]
IF [Totals] > 1 THEN
FOR EACH [Patient] IN [appointment]
[appointment].[ID] = n
n= n + 1
NEXT
END IF
NEXT
EXIT FOR
I know this doesn't work, but I believe Recordsets would be more useful. Any ideas? Thanks.
I have a database in which patient appointments are stored in a Table called 'appointments'. Some days we have more than one patient and on others, we have only one or none at all.
For those days with multiple patients, I'd like the database to automatically assign an ID number to each patient on that day. For example, if someone adds three patients, Adam, Bob, and Carl on May 4, Adam would have a value of 1 in the field called 'ID', Bob 2, Carl 3. If Bob is the only one to show up on May 5, [ID]=1.
I've made a count query that shows the number of patients per given appointment date. The query's name is 'DateTally' and that number is displayed in a field called 'Totals'.
I think the way to do this in vB is to execute a FOR EACH NEXT loop somehow, but my syntax ability is terrible.
Dim n as integer
n = 1
FOR EACH [Totals] IN [datetally]
IF [Totals] > 1 THEN
FOR EACH [Patient] IN [appointment]
[appointment].[ID] = n
n= n + 1
NEXT
END IF
NEXT
EXIT FOR
I know this doesn't work, but I believe Recordsets would be more useful. Any ideas? Thanks.