Query Question

G

Guest

I have a table of nominations that includes the fields NomineeName,
NominatorName, and NominationMessage. I did not create the database, but have
been asked to create reports from it. My problem is in the NomineeName.
Users have been able to imput multiple names into the database for a single
value, so that the would Like this:

ID NomineeName Message
1 John Smith, Karen Jones Blah1
2 Jack Henderson Blah2
3 Jack Henderson; John Smith; Peggy Sue Blah3


What I need help on is how to create a query that would give something like
this:

NomineeName Message
John Smith Blah1
Karen Jones Blah1
Jack Henderson Blah2
Jack Henderson Blah3
John Smith Blah3
Peggy Sue Blah3

Any help would be greatly appreciated.. thanks
 
J

John Spencer

You would need to split all the names out into separate records. And
your example showed that names were split by commas or semi-colons. I
suspect that other methods (Colons, Slashes, or dashes might also have
been used.


First, add this function to a module so you can call it in a query

Public Function fGetSection(StrIn, iSection As Integer, _
Optional strDelimiter As String = ";")
Dim vArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn
Else
vArray = Split(StrIn, strDelimiter, , vbTextCompare)
iSection = iSection - 1
If iSection > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection)
End If
End If

End Function
 
J

John Spencer

That's strange. My response got truncated.

I suggested a UNION query using the function as the solution. And I
also modified the function significantly.

Paste the following function into a module and save the module with
modStringFunctions

Public Function fGetSection(StrIn, iSection as Integer)
Dim vArray as Variant
Dim strDelimiter as String

If Len(StrIn & vbNullString) = 0 Then
fGetSection = strIn
Else
strDelimiter = ";"
'Take care of multiple delimiters
strIn = Replace(StrIn,":",strDelimiter)
strIn = Replace(StrIn,",",strDelimiter)
strIn = Replace(StrIn,"-",strDelimiter)
strIn = Replace(StrIn,"/",strDelimiter)

vArray = Split(strIn,strDelimiter)
If iSection-1 > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection-1)
End If
End If
End Function

Now use a union query with that function (I assume a maximum of 5
nominees in this example)

SELECT ID, fGetSection([NomineeName],1) as NomName
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],2)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],3)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],4)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],5)
FROM Nominations

That will give you records for each nominee name associated with an id
number. Save that as a query (qNormNominees). OR use the query to
build a table if the data is stable - no longer being changed.

Now build another query based on the table and the union query

SELECT qNormNominees.NomName
,Nominations.NomineeMessage
FROM Nominations INNER JOIN qNormNominees
On Nominations.ID = qNormNominees.ID
WHERE qNormNominees.NomName is Not Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
G

Guest

Thank you for your help.
However, when I tried to follow your idea, I get the following error message:

Ambiguous Name. in query expression 'fGetSection([NomineeName],1)'.




John Spencer said:
That's strange. My response got truncated.

I suggested a UNION query using the function as the solution. And I
also modified the function significantly.

Paste the following function into a module and save the module with
modStringFunctions

Public Function fGetSection(StrIn, iSection as Integer)
Dim vArray as Variant
Dim strDelimiter as String

If Len(StrIn & vbNullString) = 0 Then
fGetSection = strIn
Else
strDelimiter = ";"
'Take care of multiple delimiters
strIn = Replace(StrIn,":",strDelimiter)
strIn = Replace(StrIn,",",strDelimiter)
strIn = Replace(StrIn,"-",strDelimiter)
strIn = Replace(StrIn,"/",strDelimiter)

vArray = Split(strIn,strDelimiter)
If iSection-1 > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection-1)
End If
End If
End Function

Now use a union query with that function (I assume a maximum of 5
nominees in this example)

SELECT ID, fGetSection([NomineeName],1) as NomName
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],2)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],3)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],4)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],5)
FROM Nominations

That will give you records for each nominee name associated with an id
number. Save that as a query (qNormNominees). OR use the query to
build a table if the data is stable - no longer being changed.

Now build another query based on the table and the union query

SELECT qNormNominees.NomName
,Nominations.NomineeMessage
FROM Nominations INNER JOIN qNormNominees
On Nominations.ID = qNormNominees.ID
WHERE qNormNominees.NomName is Not Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
You would need to split all the names out into separate records. And
your example showed that names were split by commas or semi-colons. I
suspect that other methods (Colons, Slashes, or dashes might also have
been used.


First, add this function to a module so you can call it in a query

Public Function fGetSection(StrIn, iSection As Integer, _
Optional strDelimiter As String = ";")
Dim vArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn
Else
vArray = Split(StrIn, strDelimiter, , vbTextCompare)
iSection = iSection - 1
If iSection > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection)
End If
End If

End Function
Click to expand...
Click to expand...
 
J

John Spencer

The ambiguous name error indicates to me that you have a module with the
name fGetSection or another function with that name.

Do you have more than one function named fGetSection? perhaps in another
module?

Did you name the module fGetSection? You can't do that the module and the
function cannot share the same name. Try renaming the module to
modGetSection or modStringFunctions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

trainsteve said:
Thank you for your help.
However, when I tried to follow your idea, I get the following error
message:

Ambiguous Name. in query expression 'fGetSection([NomineeName],1)'.




John Spencer said:
That's strange. My response got truncated.

I suggested a UNION query using the function as the solution. And I
also modified the function significantly.

Paste the following function into a module and save the module with
modStringFunctions

Public Function fGetSection(StrIn, iSection as Integer)
Dim vArray as Variant
Dim strDelimiter as String

If Len(StrIn & vbNullString) = 0 Then
fGetSection = strIn
Else
strDelimiter = ";"
'Take care of multiple delimiters
strIn = Replace(StrIn,":",strDelimiter)
strIn = Replace(StrIn,",",strDelimiter)
strIn = Replace(StrIn,"-",strDelimiter)
strIn = Replace(StrIn,"/",strDelimiter)

vArray = Split(strIn,strDelimiter)
If iSection-1 > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection-1)
End If
End If
End Function

Now use a union query with that function (I assume a maximum of 5
nominees in this example)

SELECT ID, fGetSection([NomineeName],1) as NomName
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],2)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],3)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],4)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],5)
FROM Nominations

That will give you records for each nominee name associated with an id
number. Save that as a query (qNormNominees). OR use the query to
build a table if the data is stable - no longer being changed.

Now build another query based on the table and the union query

SELECT qNormNominees.NomName
,Nominations.NomineeMessage
FROM Nominations INNER JOIN qNormNominees
On Nominations.ID = qNormNominees.ID
WHERE qNormNominees.NomName is Not Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
You would need to split all the names out into separate records. And
your example showed that names were split by commas or semi-colons. I
suspect that other methods (Colons, Slashes, or dashes might also have
been used.


First, add this function to a module so you can call it in a query

Public Function fGetSection(StrIn, iSection As Integer, _
Optional strDelimiter As String = ";")
Dim vArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn
Else
vArray = Split(StrIn, strDelimiter, , vbTextCompare)
iSection = iSection - 1
If iSection > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection)
End If
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

New problem: It states that:

The specified field 'Nominations.NomineeMessage' could refer to more than
one table listed in the FROM clause of your SQL statement.

How do I fix this??

John Spencer said:
That's strange. My response got truncated.

I suggested a UNION query using the function as the solution. And I
also modified the function significantly.

Paste the following function into a module and save the module with
modStringFunctions

Public Function fGetSection(StrIn, iSection as Integer)
Dim vArray as Variant
Dim strDelimiter as String

If Len(StrIn & vbNullString) = 0 Then
fGetSection = strIn
Else
strDelimiter = ";"
'Take care of multiple delimiters
strIn = Replace(StrIn,":",strDelimiter)
strIn = Replace(StrIn,",",strDelimiter)
strIn = Replace(StrIn,"-",strDelimiter)
strIn = Replace(StrIn,"/",strDelimiter)

vArray = Split(strIn,strDelimiter)
If iSection-1 > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection-1)
End If
End If
End Function

Now use a union query with that function (I assume a maximum of 5
nominees in this example)

SELECT ID, fGetSection([NomineeName],1) as NomName
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],2)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],3)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],4)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],5)
FROM Nominations

That will give you records for each nominee name associated with an id
number. Save that as a query (qNormNominees). OR use the query to
build a table if the data is stable - no longer being changed.

Now build another query based on the table and the union query

SELECT qNormNominees.NomName
,Nominations.NomineeMessage
FROM Nominations INNER JOIN qNormNominees
On Nominations.ID = qNormNominees.ID
WHERE qNormNominees.NomName is Not Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
You would need to split all the names out into separate records. And
your example showed that names were split by commas or semi-colons. I
suspect that other methods (Colons, Slashes, or dashes might also have
been used.


First, add this function to a module so you can call it in a query

Public Function fGetSection(StrIn, iSection As Integer, _
Optional strDelimiter As String = ";")
Dim vArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn
Else
vArray = Split(StrIn, strDelimiter, , vbTextCompare)
iSection = iSection - 1
If iSection > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection)
End If
End If

End Function
Click to expand...
Click to expand...
 
J

John Spencer

I don't know. Could you post the entire SQL of the query that is failing?
(View: SQL; then copy and paste).

This worked when I tested it.

I'm using Access 2003, but I also tested it in Access 2000.

My best guess is that you have somehow gotten Nominations table into the
query two times without an alias on the second instance.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

trainsteve said:
New problem: It states that:

The specified field 'Nominations.NomineeMessage' could refer to more than
one table listed in the FROM clause of your SQL statement.

How do I fix this??

John Spencer said:
That's strange. My response got truncated.

I suggested a UNION query using the function as the solution. And I
also modified the function significantly.

Paste the following function into a module and save the module with
modStringFunctions

Public Function fGetSection(StrIn, iSection as Integer)
Dim vArray as Variant
Dim strDelimiter as String

If Len(StrIn & vbNullString) = 0 Then
fGetSection = strIn
Else
strDelimiter = ";"
'Take care of multiple delimiters
strIn = Replace(StrIn,":",strDelimiter)
strIn = Replace(StrIn,",",strDelimiter)
strIn = Replace(StrIn,"-",strDelimiter)
strIn = Replace(StrIn,"/",strDelimiter)

vArray = Split(strIn,strDelimiter)
If iSection-1 > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection-1)
End If
End If
End Function

Now use a union query with that function (I assume a maximum of 5
nominees in this example)

SELECT ID, fGetSection([NomineeName],1) as NomName
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],2)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],3)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],4)
FROM Nominations
UNION ALL
SELECT ID, fGetSection([NomineeName],5)
FROM Nominations

That will give you records for each nominee name associated with an id
number. Save that as a query (qNormNominees). OR use the query to
build a table if the data is stable - no longer being changed.

Now build another query based on the table and the union query

SELECT qNormNominees.NomName
,Nominations.NomineeMessage
FROM Nominations INNER JOIN qNormNominees
On Nominations.ID = qNormNominees.ID
WHERE qNormNominees.NomName is Not Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
You would need to split all the names out into separate records. And
your example showed that names were split by commas or semi-colons. I
suspect that other methods (Colons, Slashes, or dashes might also have
been used.


First, add this function to a module so you can call it in a query

Public Function fGetSection(StrIn, iSection As Integer, _
Optional strDelimiter As String = ";")
Dim vArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn
Else
vArray = Split(StrIn, strDelimiter, , vbTextCompare)
iSection = iSection - 1
If iSection > UBound(vArray) Then
fGetSection = Null
Else
fGetSection = vArray(iSection)
End If
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to combine columns 2
UNION Query 1
Function with Two Values 6
Search by product & alsoshow other products purchased by same cust 2
separating first and last names when they are in the same cell 4
Access query 1
Concatenate Question 2
crosstab query question 1

Top