Reducing One to Many Queries To 1 Unique Record

[Jorgandr]

What I would like to know is if there is any way to take a standard one to many relationship and then displaying only 1 for each related item. For instance, say i have a table:

Exceptions:
ExceptionID <-- Primary Key
LoanID <-- Foreign Key
ItemID <-- Foreign Key

Instead of in a query showing:

ExceptionID LoanID ItemID
1 204 1
2 204 2
3 204 4
4 500 1
5 500 6
6 600 8
7 999 2

I would like to see
LoanID ItemIDConcat (New Field in Query)
204 1, 2, 4
500 1, 6
600 8
999 2

If anyone has any suggestions, I would appreciate it. Thank you.

 

[MakeItSo]

Try something like this:
Function ItemIDConcat()
dim buffer as String
Dim rs as recordset
set rs=currentdb.openrecordset(paste your standard select query here,dbreadonly)
do while not rs.eof
buffer=buffer & rs![ItemID] & &quot;, &quot;
rs.MoveNext
loop
buffer=left(buffer, len(buffer)-2) 'remove last comma and space
ItemIDConcat=buffer
end function

and set the control source for one text box on your form to &quot;=ItemIDConcat()&quot;

Never tried this before, but it should lead you on a practical path.

regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Jorgandr]

Thanks for your help... I'm not sure if that will work for what I want. Ultimately I want to be able to display this on a report and include totals for loan amt and the such but to only display one line of data for each loan instead of multiple ones because there are multiple exception items for each loan. I tried writing the code in the report itself but it is buggy and doesn't always produce correct results. I figured if I could do it in a query instead it would greatly simplify the report. If I were to create a query using a macro or function, how would I then use that query in my report?

Tables:

Tbl_Loans
LoanID
LoanNum
LoanAmt

Tbl_Exceptions
ExceptionID
ExceptionName
ExceptionType

Tbl_LoanExceptions
LoanExceptionID
LoanID
ExceptionID

Then Report would look like:

Loan # Loan Amount Exception Items
123456 $1,000.00 Final Title, Mtg, Rec Mtg
333444 $2,000.00 Mtg
394856 $2,345.00 Disc, RecMtg

Total Loans: $5,345.00 (3 Loans)

Thanks again...

 

[MakeItSo]

YOu could simply bind the report to one query (e.g. &quot;qryReport&quot, and then use this:

Dim qdf as QueryDef
Dim dbs as Database
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryReport")
qdf.SQL = "SELECT whatever FROM whatever..."

This way you can always create any SQL you'd like and yet have it as a stored Query, i.e. you always overwrite the existing qryReport with your new SQL statement.

Does that help you?

 

[dhookom]

There is a generic concatenate function at faq701-4233. A sample that uses this can be found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane
MS Access MVP
Find out how to get great answers faq219-2884.