Moving through records and counting

[Ian]

I have created a recordset from my data of hospital patients whereby a
patient ID is associated with a series of test results. In the user form
navigation is made using the in-built Access navigation buttons. However,
some patients have more than one set of results for the same test. The user
just sees a second (or third etc.) record where the patient ID is the same
but one of the five tests has a different result. In order to make it clear
to the user I would like to display (in a txtbox next to the ID field) an "X
of Y" message for that particular PatID, independant of the Access nav
buttons. I have tried various DAO recordset techniques, but my skills are not
good enough yet.

Can this be done and if so can anyone start me off. Thanks in advance.

 

[John Nurick]

Hi Ian,

I don't see why you need recordset operations for this: you can just use
calculated fields in a query to return the "X" and "Y" for each record.
Here's an example that works in the Northwind sample database (ItemSeq
is X, ItemCount is Y):

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS ItemSeq,
(SELECT COUNT(D.OrderID)
FROM [Order Details] AS D
WHERE D.OrderID = A.OrderID) AS ItemCount,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you don't want to include the two subqueries in the query to which
the form is bound (maybe because you need the query to be updatable),
you can use the same technique in a parameter query that calculates and
concatenates "X" and "Y" for the current record only. Then use the query
in a DLookup() expression in the textbox's ControlSource property, or in
the form's Current event procedure.

 

[Ian]

Hi John,

Thanks very much for your reply, I'm going to have to work through your
suggestion as it fits my data. I'll get back to you if I'm struggling, but
thanks for your ideas.

Hi Ian,

I don't see why you need recordset operations for this: you can just use
calculated fields in a query to return the "X" and "Y" for each record.
Here's an example that works in the Northwind sample database (ItemSeq
is X, ItemCount is Y):

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS ItemSeq,
(SELECT COUNT(D.OrderID)
FROM [Order Details] AS D
WHERE D.OrderID = A.OrderID) AS ItemCount,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you don't want to include the two subqueries in the query to which
the form is bound (maybe because you need the query to be updatable),
you can use the same technique in a parameter query that calculates and
concatenates "X" and "Y" for the current record only. Then use the query
in a DLookup() expression in the textbox's ControlSource property, or in
the form's Current event procedure.

I have created a recordset from my data of hospital patients whereby a
patient ID is associated with a series of test results. In the user form
navigation is made using the in-built Access navigation buttons. However,
some patients have more than one set of results for the same test. The user
just sees a second (or third etc.) record where the patient ID is the same
but one of the five tests has a different result. In order to make it clear
to the user I would like to display (in a txtbox next to the ID field) an "X
of Y" message for that particular PatID, independant of the Access nav
buttons. I have tried various DAO recordset techniques, but my skills are not
good enough yet.

Can this be done and if so can anyone start me off. Thanks in advance.

 

[Ian]

Hi John,

I just tried your solution in the Northwind sample, that's exactly what I'm
looking for, you are a genius and have bailed me out yet again, thank you.
Now I see how it works I can modify it to fit my data.

Just as an aside slightly off topic, how does one aquire this kind of
knowledge? I would have never thought about solving it this way. I seem to
come to these forums all the time looking for help. Is this knowledge you
have read in a book (? ones) or gained through working on many different
projects? I'm a sponge keen to learn but often feel bad coming here for
answers and unable to give anything back.

Thanks again.

Hi John,

Thanks very much for your reply, I'm going to have to work through your
suggestion as it fits my data. I'll get back to you if I'm struggling, but
thanks for your ideas.

Hi Ian,

I don't see why you need recordset operations for this: you can just use
calculated fields in a query to return the "X" and "Y" for each record.
Here's an example that works in the Northwind sample database (ItemSeq
is X, ItemCount is Y):

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS ItemSeq,
(SELECT COUNT(D.OrderID)
FROM [Order Details] AS D
WHERE D.OrderID = A.OrderID) AS ItemCount,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you don't want to include the two subqueries in the query to which
the form is bound (maybe because you need the query to be updatable),
you can use the same technique in a parameter query that calculates and
concatenates "X" and "Y" for the current record only. Then use the query
in a DLookup() expression in the textbox's ControlSource property, or in
the form's Current event procedure.

I have created a recordset from my data of hospital patients whereby a
patient ID is associated with a series of test results. In the user form
navigation is made using the in-built Access navigation buttons. However,
some patients have more than one set of results for the same test. The user
just sees a second (or third etc.) record where the patient ID is the same
but one of the five tests has a different result. In order to make it clear
to the user I would like to display (in a txtbox next to the ID field) an "X
of Y" message for that particular PatID, independant of the Access nav
buttons. I have tried various DAO recordset techniques, but my skills are not
good enough yet.

Can this be done and if so can anyone start me off. Thanks in advance.

 

[John Nurick]

Hi Ian,

Thank you for the kind words.

As for acquiring knowledge, partly it's a matter of time. I've been
programming things for more than 20 years and using Access for ten, and
one can't help picking things up. I've even read a few books - and
looked things up in many more, not to mention having asked quite a few
questions in these newsgroups myself over the years. So keep sponging it
up!

As for using SQL instead of writing VBA that iterates through a
recordset counting things: those few lines of SQL are neater and faster
and less likely to contain bugs than the equivalent VBA. If you're
working with relational data IMHO it's always worth looking for a SQL
solution before resorting to procedural code (it took me years to learn
this<g>).

Hi John,

I just tried your solution in the Northwind sample, that's exactly what I'm
looking for, you are a genius and have bailed me out yet again, thank you.
Now I see how it works I can modify it to fit my data.

Just as an aside slightly off topic, how does one aquire this kind of
knowledge? I would have never thought about solving it this way. I seem to
come to these forums all the time looking for help. Is this knowledge you
have read in a book (? ones) or gained through working on many different
projects? I'm a sponge keen to learn but often feel bad coming here for
answers and unable to give anything back.

Thanks again.

Hi John,

Thanks very much for your reply, I'm going to have to work through your
suggestion as it fits my data. I'll get back to you if I'm struggling, but
thanks for your ideas.

Hi Ian,

I don't see why you need recordset operations for this: you can just use
calculated fields in a query to return the "X" and "Y" for each record.
Here's an example that works in the Northwind sample database (ItemSeq
is X, ItemCount is Y):

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS ItemSeq,
(SELECT COUNT(D.OrderID)
FROM [Order Details] AS D
WHERE D.OrderID = A.OrderID) AS ItemCount,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you don't want to include the two subqueries in the query to which
the form is bound (maybe because you need the query to be updatable),
you can use the same technique in a parameter query that calculates and
concatenates "X" and "Y" for the current record only. Then use the query
in a DLookup() expression in the textbox's ControlSource property, or in
the form's Current event procedure.






I have created a recordset from my data of hospital patients whereby a
patient ID is associated with a series of test results. In the user form
navigation is made using the in-built Access navigation buttons. However,
some patients have more than one set of results for the same test. The user
just sees a second (or third etc.) record where the patient ID is the same
but one of the five tests has a different result. In order to make it clear
to the user I would like to display (in a txtbox next to the ID field) an "X
of Y" message for that particular PatID, independant of the Access nav
buttons. I have tried various DAO recordset techniques, but my skills are not
good enough yet.

Can this be done and if so can anyone start me off. Thanks in advance.