How to eliminate duplicate information in report

[Nathan-bfhd]

I have tables and queries that track child immunizations at given locations.
The children are in one table with a unique ID. The Visit information
(location and date) are in another table with a unique ID. The immunization
visit consists of looking at a child’s information regarding 17 different
immunizations and recording the status of them, such as current or needs.
The immunization info is also in its own table and is linked to the other two
with the ChildID and the VisitID.

For the report, I need to display all the immunizations needed for each
child at a specific visit.

I’ve created a query that gives me all the info I need and went ahead and
attempted the report. I have created a report that groups first by VisitID
(giving me the location and date), then by ChildID (giving me each individual
child at that visit) – so far so good. The problem I’m having is in
displaying the needed immunizations. I have the query set up to only display
the immunizations marked “Needsâ€, so that’s taken care of, but the way it is
listing them is not what I need. Since the immunizations are in their own
table and linked to the children by a ChildID, each immunization is its own
record. So, I end up with (for example):

FirstName1 LastName1 HepB1 Needs
FirstName1 LastName1 HepB2 Needs
FirstName1 LastName1 Hib2 Needs
FirstName1 LastName1 Hib3 Needs

FirstName2 LastName2 HepB2 Needs
FirstName2 LastName2 Hib1 Needs
FirstName2 LastName2 Hib2 Needs
FirstName2 LastName2 IPV1 Needs

If the child has 10 needed immunizations, I get 10 records with the child’s
name, immunizations and the status of needs.

Needless to say, this takes up way more space than it needs to and is not
what I’m looking for. What I want is:

FirstName1 LastName1 HepB1, HepB2, Hib2, Hib3 Needs
FirstName2 LastName2 HipB2, Hib1, Hib2, IPV1 Needs

I can do without the commas if that is more difficult, and I really don’t
need the “Needs†status there, but I would like the list of immunizations.
Anybody have an idea of how I can do this or where I could look to find this
– I’m at a total loss.

~Nathan

 

[Marshall Barton]

I have tables and queries that track child immunizations at given locations.
The children are in one table with a unique ID. The Visit information
(location and date) are in another table with a unique ID. The immunization
visit consists of looking at a child’s information regarding 17 different
immunizations and recording the status of them, such as current or needs.
The immunization info is also in its own table and is linked to the other two
with the ChildID and the VisitID.

For the report, I need to display all the immunizations needed for each
child at a specific visit.

I’ve created a query that gives me all the info I need and went ahead and
attempted the report. I have created a report that groups first by VisitID
(giving me the location and date), then by ChildID (giving me each individual
child at that visit) – so far so good. The problem I’m having is in
displaying the needed immunizations. I have the query set up to only display
the immunizations marked “Needs”, so that’s taken care of, but the way it is
listing them is not what I need. Since the immunizations are in their own
table and linked to the children by a ChildID, each immunization is its own
record. So, I end up with (for example):

FirstName1 LastName1 HepB1 Needs
FirstName1 LastName1 HepB2 Needs
FirstName1 LastName1 Hib2 Needs
FirstName1 LastName1 Hib3 Needs

FirstName2 LastName2 HepB2 Needs
FirstName2 LastName2 Hib1 Needs
FirstName2 LastName2 Hib2 Needs
FirstName2 LastName2 IPV1 Needs

If the child has 10 needed immunizations, I get 10 records with the child’s
name, immunizations and the status of needs.

Needless to say, this takes up way more space than it needs to and is not
what I’m looking for. What I want is:

FirstName1 LastName1 HepB1, HepB2, Hib2, Hib3 Needs
FirstName2 LastName2 HipB2, Hib1, Hib2, IPV1 Needs

I can do without the commas if that is more difficult, and I really don’t
need the “Needs” status there, but I would like the list of immunizations.

You have to create a function to do this kind of thing.
Here's a popular one:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[Nathan-bfhd]

Thanks for your prompt response. The information you have provided looks
promising, unfortunately I'm not able to view the example you provided (yet).
I downloaded the example, but when I go to view it, Access gives me a
security error saying that the file is located outside my intranet or on an
untrusted site. It then tells me to copy it to my machine or local network
to open the file. The file is currently on my local machine, so maybe it's
trying to access something off of the internet somewhere, I don't know. If
you have some bit of code available for me in regard to my original question,
or a possible work around of the above mentioned issue - it would be much
appreciated.

~Nathan

 

[Marshall Barton]

Don't you just love security features ;-)

Here's Duane's code module. Be sure to read the comments
about ADO vs DAO.
--
Marsh
MVP [MS Access]

*************************************************************
Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
**************************************************************

 

[Duane Hookom]

You may need to right-click the downloaded file in Explorer and choose
properties. There might be an "unblock" option that requires checking or
something.

 

[Nathan-bfhd]

Just wanted to thank you again for your response. I was finally able to get
it to work on another PC...still not sure what that security thing was all
about. Anyways, it worked great -- now I'm back with another issue, if you
have a minute maybe you could take a crack at the new one.

Don't you just love security features ;-)

Here's Duane's code module. Be sure to read the comments
about ADO vs DAO.
--
Marsh
MVP [MS Access]

*************************************************************
Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
**************************************************************

Thanks for your prompt response. The information you have provided looks
promising, unfortunately I'm not able to view the example you provided (yet).
I downloaded the example, but when I go to view it, Access gives me a
security error saying that the file is located outside my intranet or on an
untrusted site. It then tells me to copy it to my machine or local network
to open the file. The file is currently on my local machine, so maybe it's
trying to access something off of the internet somewhere, I don't know. If
you have some bit of code available for me in regard to my original question,
or a possible work around of the above mentioned issue - it would be much
appreciated.