wlwebb
asked on
Access Query to retrieve info as One record
Hello all,
Don't know if there's any way to do this but I'll ask anyway. If I have a table containing a text field, is there a way to create a query that summarizes all of those names into one record.
Example:
Say my table has
ID (autonumber)
RecTypeID (Number - Index from a table of Record Types)
RecName - Text
Now then I know I can pull a list of all Name fields with RecTypeID 1 or 2 or 3 etc.... BUT
that will give me a list. Is there then a way to get all of those names as one temporary record within a query?
So let's say I have
ID RecTypeID RecName
1 5 Joe Smith
2 3 Jenny Jones
3 1 Mark Doe
4 5 Alvin Chip
5 5 Mary Wright
6 3 Elmer Fudd
7 5 Zack Tway
8 1 Mildred Way
So can I get all the RecTypeID 5's let's say into one query record that would be
Joe Smith, Alvin Chip, Mary Wright, Zack Tway
Don't know if there's any way to do this but I'll ask anyway. If I have a table containing a text field, is there a way to create a query that summarizes all of those names into one record.
Example:
Say my table has
ID (autonumber)
RecTypeID (Number - Index from a table of Record Types)
RecName - Text
Now then I know I can pull a list of all Name fields with RecTypeID 1 or 2 or 3 etc.... BUT
that will give me a list. Is there then a way to get all of those names as one temporary record within a query?
So let's say I have
ID RecTypeID RecName
1 5 Joe Smith
2 3 Jenny Jones
3 1 Mark Doe
4 5 Alvin Chip
5 5 Mary Wright
6 3 Elmer Fudd
7 5 Zack Tway
8 1 Mildred Way
So can I get all the RecTypeID 5's let's say into one query record that would be
Joe Smith, Alvin Chip, Mary Wright, Zack Tway
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If that does not address your specific needs, try this:
Create a function to concatenate the names, for a passed recTypeID:
You can call it from a query like this:
... which should hopefully display your name list for each RecID
As an aside, EE is now actively discouraging Experts from posting link-only solutions that involve offsite content. See the following topic:
http://support.experts-exchange.com/customer/portal/articles/1162518-using-off-site-content-on-experts-exchange?b_id=44
Create a function to concatenate the names, for a passed recTypeID:
Public Function ConcatNames(lngRecTypeID as long) as Variant
dim rs as DAO.recordset
Dim strSQL as String
dim s as string
strSQL = "RecTypeID, RecName FROM YourTable WHERE RecTypeID = " & lngRecTypeID
set rs = currentdb.openrecordset(strSQL, dbFailOnError)
if rs.Recordcount = 0 then
ConcatNames = Null
Exit Function
End If
do until rs.eof
s = s & rs!RecName & ", "
rs.MoveNext
loop
s = trim(s)
s = left(s, len(s) -1)
ConcatNames = s
end function
You can call it from a query like this:
SELECT DISTINCT RecTypeID, ConcatNames(RecTypeID) AS NameList
FROM YourTable
... which should hopefully display your name list for each RecID
As an aside, EE is now actively discouraging Experts from posting link-only solutions that involve offsite content. See the following topic:
http://support.experts-exchange.com/customer/portal/articles/1162518-using-off-site-content-on-experts-exchange?b_id=44
ASKER
Mbiz........
Thanks...couldn't get the other to work....I'm tooo dense I guess.....
In using yours I'm getting a Run-time error '3001', invalid argument on the line
Set rs - Currentdb.OpenRecordset(st rSQL, dbFailonerror)
I'm not using a table to pull the records, I'm using another query... does that make a difference?????
Thanks...couldn't get the other to work....I'm tooo dense I guess.....
In using yours I'm getting a Run-time error '3001', invalid argument on the line
Set rs - Currentdb.OpenRecordset(st
I'm not using a table to pull the records, I'm using another query... does that make a difference?????
Possibly... what is your query?
ASKER
Long......
SELECT tbl_RecordsPriorRelated.Re cID, tbl_RecordsPriorRelated.Pr iorRecID, qrytbl_Records.[Book-Pg] AS CurrentBkPg, qrytbl_PriorRecords.[Book- Pg] AS PriorBkPg, qrytbl_Records.RecDate AS CurrentDate, qrytbl_PriorRecords.RecDat e AS PriorDate, qrytbl_Parties1st.PartyNam e, qrytbl_Parties1st.RecParty TypeID AS CurrentRecPartyTypeID, qrytbl_Parties1st.RecParty Type
FROM ((tbl_Records LEFT JOIN qrytbl_Records ON tbl_Records.RecID = qrytbl_Records.RecID) LEFT JOIN qrytbl_Parties1st ON tbl_Records.RecID = qrytbl_Parties1st.RecID) RIGHT JOIN ((tbl_RecordsPriorRelated LEFT JOIN tbl_Records AS tbl_PriorRecords ON tbl_RecordsPriorRelated.Pr iorRecID = tbl_PriorRecords.RecID) LEFT JOIN qrytbl_Records AS qrytbl_PriorRecords ON tbl_PriorRecords.RecID = qrytbl_PriorRecords.RecID) ON tbl_Records.RecID = tbl_RecordsPriorRelated.Re cID
GROUP BY tbl_RecordsPriorRelated.Re cID, tbl_RecordsPriorRelated.Pr iorRecID, qrytbl_Records.[Book-Pg], qrytbl_PriorRecords.[Book- Pg], qrytbl_Records.RecDate, qrytbl_PriorRecords.RecDat e, qrytbl_Parties1st.PartyNam e, qrytbl_Parties1st.RecParty TypeID, qrytbl_Parties1st.RecParty Type
ORDER BY qrytbl_Parties1st.RecParty TypeID;
SELECT tbl_RecordsPriorRelated.Re
FROM ((tbl_Records LEFT JOIN qrytbl_Records ON tbl_Records.RecID = qrytbl_Records.RecID) LEFT JOIN qrytbl_Parties1st ON tbl_Records.RecID = qrytbl_Parties1st.RecID) RIGHT JOIN ((tbl_RecordsPriorRelated LEFT JOIN tbl_Records AS tbl_PriorRecords ON tbl_RecordsPriorRelated.Pr
GROUP BY tbl_RecordsPriorRelated.Re
ORDER BY qrytbl_Parties1st.RecParty
ASKER
Tried to simplify my original question and only used a short field name in original posting.
Also, what do you ultimately want to do with the list of names? Do you want them displayed in datasheet format with corresponding recTypeID's, on a form or report...?
Try this instead for line 8:
set rs = currentdb.openrecordset(st rSQL, dbOpenDynaset)
set rs = currentdb.openrecordset(st
ASKER
Ultimately on a Form's subform there will be a list of Grantor and Grantees...... ie: Deeds
But I want that list to show one line for all Grantors and one line for all Grantees
When I'm inputting the deed info I input a separate record for each Grantor and Grantee that is linked to the Deed Record. The Deed Record will have a Book Type (Deed, Will etc..) and a Book Nbr and Page Nbr of the record as well as the document date.
NOTE: also some contracts will have 1st Party, 2nd Party, 3rd Party, 4th Party...can be more than just 2 "Party" types to a contract
But I want that list to show one line for all Grantors and one line for all Grantees
When I'm inputting the deed info I input a separate record for each Grantor and Grantee that is linked to the Deed Record. The Deed Record will have a Book Type (Deed, Will etc..) and a Book Nbr and Page Nbr of the record as well as the document date.
NOTE: also some contracts will have 1st Party, 2nd Party, 3rd Party, 4th Party...can be more than just 2 "Party" types to a contract
ASKER
Run=time error '3078':
The MO Access database engine cannot find the input table or query 'CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated WHERE CurrentRecPartyTypeID=1'.
Make sure it exists........
This is my SQL on the new query:
SELECT DISTINCT CurrentRecPartyTypeID, ConcatNames(CurrentRecPart yTypeID) AS PartyList
FROM qrytbl_RecordsPriorRelated
This is the module I created and put the Public Function in:
Option Compare Database
Option Explicit
Public Function ConcatNames(lngRecTypeID As Long) As Variant
Dim rs As DAO.Recordset
Dim strSQL As String
Dim s As String
strSQL = "CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated WHERE CurrentRecPartyTypeID= " & lngRecTypeID
Set rs = CurrentDb.OpenRecordset(st rSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
ConcatNames = Null
Exit Function
End If
Do Until rs.EOF
s = s & rs!PartyName & ", "
rs.MoveNext
Loop
s = Trim(s)
s = Left(s, Len(s) - 1)
ConcatNames = s
End Function
The MO Access database engine cannot find the input table or query 'CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated
Make sure it exists........
This is my SQL on the new query:
SELECT DISTINCT CurrentRecPartyTypeID, ConcatNames(CurrentRecPart
FROM qrytbl_RecordsPriorRelated
This is the module I created and put the Public Function in:
Option Compare Database
Option Explicit
Public Function ConcatNames(lngRecTypeID As Long) As Variant
Dim rs As DAO.Recordset
Dim strSQL As String
Dim s As String
strSQL = "CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated
Set rs = CurrentDb.OpenRecordset(st
If rs.RecordCount = 0 Then
ConcatNames = Null
Exit Function
End If
Do Until rs.EOF
s = s & rs!PartyName & ", "
rs.MoveNext
Loop
s = Trim(s)
s = Left(s, Len(s) - 1)
ConcatNames = s
End Function
Gotcha - so you want the display in to show as a list.
Try the correction I suggested in my last comment:
You can add it as a column to a query:
NameList: ConcatNames(RecTypeID)
Try the correction I suggested in my last comment:
Public Function ConcatNames(lngRecTypeID as long) as Variant
dim rs as DAO.recordset
Dim strSQL as String
dim s as string
strSQL = "RecTypeID, RecName FROM YourTable WHERE RecTypeID = " & lngRecTypeID
set rs = currentdb.openrecordset(strSQL, dbOpenDynaset)
if rs.Recordcount = 0 then
ConcatNames = Null
Exit Function
End If
do until rs.eof
s = s & rs!RecName & ", "
rs.MoveNext
loop
s = trim(s)
s = left(s, len(s) -1)
ConcatNames = s
end function
You can add it as a column to a query:
NameList: ConcatNames(RecTypeID)
Missed your last post. Hang on...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT!!!!!!!!!!!!!!!!!!!
Glad to help out :)
ASKER
Thank you moderator for reopening. I didn't know how to fix that. I gave 50 to orig poster for pointing me to a possible solution but Mbiz as usual has FANTASTIC solutions!
ASKER