?
Solved

Access Query to retrieve info as One record

Posted on 2013-12-18
18
Medium Priority
?
366 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
18 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 200 total points
ID: 39726488
0
 

Author Comment

by:wlwebb
ID: 39726563
Thanks.....That appears to be it.....  I will attempt and comment back but looks like what I'm after
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726608
If that does not address your specific needs, try this:

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

Open in new window



You can call it from a query like this:

SELECT DISTINCT RecTypeID, ConcatNames(RecTypeID) AS NameList
FROM YourTable

Open in new window



... 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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wlwebb
ID: 39726658
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(strSQL, dbFailonerror)

I'm not using a table to pull the records, I'm using another query... does that make a difference?????
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726665
Possibly... what is your query?
0
 

Author Comment

by:wlwebb
ID: 39726671
Long......

SELECT tbl_RecordsPriorRelated.RecID, tbl_RecordsPriorRelated.PriorRecID, qrytbl_Records.[Book-Pg] AS CurrentBkPg, qrytbl_PriorRecords.[Book-Pg] AS PriorBkPg, qrytbl_Records.RecDate AS CurrentDate, qrytbl_PriorRecords.RecDate AS PriorDate, qrytbl_Parties1st.PartyName, qrytbl_Parties1st.RecPartyTypeID AS CurrentRecPartyTypeID, qrytbl_Parties1st.RecPartyType
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.PriorRecID = tbl_PriorRecords.RecID) LEFT JOIN qrytbl_Records AS qrytbl_PriorRecords ON tbl_PriorRecords.RecID = qrytbl_PriorRecords.RecID) ON tbl_Records.RecID = tbl_RecordsPriorRelated.RecID
GROUP BY tbl_RecordsPriorRelated.RecID, tbl_RecordsPriorRelated.PriorRecID, qrytbl_Records.[Book-Pg], qrytbl_PriorRecords.[Book-Pg], qrytbl_Records.RecDate, qrytbl_PriorRecords.RecDate, qrytbl_Parties1st.PartyName, qrytbl_Parties1st.RecPartyTypeID, qrytbl_Parties1st.RecPartyType
ORDER BY qrytbl_Parties1st.RecPartyTypeID;
0
 

Author Comment

by:wlwebb
ID: 39726675
Tried to simplify my original question and only used a short field name in original posting.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726676
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...?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726684
Try this instead for line 8:


set rs = currentdb.openrecordset(strSQL, dbOpenDynaset)
0
 

Author Comment

by:wlwebb
ID: 39726687
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
0
 

Author Comment

by:wlwebb
ID: 39726716
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(CurrentRecPartyTypeID) 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(strSQL, 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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726719
Gotcha - so you want the display in to show as a list.

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

Open in new window


You can add it as a column to a query:  

NameList: ConcatNames(RecTypeID)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726724
Missed your last post.  Hang on...
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1800 total points
ID: 39726747
My fault there.  We're missing a SELECT keyword.

<<
strSQL = "CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated  WHERE CurrentRecPartyTypeID= " & lngRecTypeID
>>

Should be:

strSQL = "SELECT CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated  WHERE CurrentRecPartyTypeID= " & lngRecTypeID

Open in new window



Public Function ConcatNames(lngRecTypeID As Long) As Variant
Dim rs As DAO.Recordset
Dim strSQL As String
Dim s As String

strSQL = "SELECT CurrentRecPartyTypeID, PartyName FROM qrytbl_RecordsPriorRelated  WHERE CurrentRecPartyTypeID= " & 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!PartyName & ", "
    rs.MoveNext
Loop

s = Trim(s)
s = Left(s, Len(s) - 1)

ConcatNames = s

End Function

Open in new window

0
 

Author Comment

by:wlwebb
ID: 39726777
PERFECT!!!!!!!!!!!!!!!!!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39726787
Glad to help out :)
0
 

Author Closing Comment

by:wlwebb
ID: 39727208
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!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question