Consolidating fields from multiple records into one record

[Ryan Scott]

Hi,



In Access 2010, I have a table with two fields. The first field (Investor Name) contains several occurrences of the same text (i.e. Investor A occurs 2 times, Investor B occurs 3 times, etc.). The second field (Investment) may or may not contain duplicates (i.e. more than one Investor may have invested in the same Investment). I'm trying to create a query that will show me, in one record, all the Investments that each Investor has made.

So for the following data...

Investor Name	Investment
Investor A	Investment 1
Investor A	Investment 2
Investor B	Investment 1
Investor B	Investment 3
Investor B	Investment 4

I want the query to report back the following...

Investor Name	1st Investment	2nd Investment	3rd Investment
Investor A	Investment 1	Investment 2
Investor B	Investment 1	Investment 3	Investment 4

To clarify further, I'm NOT looking to concatenate the results into one field. Instead, I want each Investment to appear in its own field, so that I can pull each one into a report later. Ideally, there should be no limit to the number of Investments that each Investor has made, although practically speaking, it likely won't exceed 10 or so.

Is this possible? Any suggestions? I really appreciate any help in advance!

[Ryan Scott]

I'm close - the following code gets me the first and last Investments for each Investor:

SELECT [Investor Name],
FIRST (TestTable.Investment) AS [First Investment],
LAST (TestTable.Investment) AS [Last Investment]
FROM TestTable
WHERE ((TestTable.Investment) is Not Null)
GROUP BY TestTable.[Investor Name];

I wish there were SECOND, THIRD, FOURTH functions to get me the intervening occurences.

[Ryan Scott]

Since I'll usually only have a maximum of ten Investments, maybe I could build a series of nested SELECT functions. What would the syntax be for selecting the last investment out of the first 10, the last out of the first 9, etc? If I did it this way, and if an Investor owns less than ten Investments, how could I also prevent Access from pulling the same Investment more than once for a given Investor?

[orange]

Take a look at this site and function - see if it helps

http://allenbrowne.com/func-concat.html

[Ryan Scott]

Thanks, orange. Will that function concatenate each Investment into one field? If so, I'm not sure that's what I'm looking for, unless there's a way for a report or a query to call out each Investment in the concatenated string of text.

[orange]

It will not put the various Investments into 1 field.
You can use that function to list the investor and his/her investments on 1 line ( unless there is a width problem)

Investor1 Investment1, Investment4, Investment6
Investor2 Investment1, Investment2
Investor3 Investment2, Investment3, Investment7, Investment8

May be you should clearly state what exactly you are trying to do and why the format is so important.

[Ryan Scott]

I tried that function and it looks like it does put all the Investments into the same field and separates them with commas. It's very likely that the Investments themselves will also have commas in their names (ex: "ABC Company, LLC"), so using some sort of comma delimiter won't be helpful to split them apart once they've been concatenated.

Anyway, my end goal is to create a one-page report of each Investor; within that report, I want a section that lists their Investments. I figured that if I can get their Investments to exist within a single record with pre-defined field names, then I could create text boxes in the report that pull in the "1st Investment" field, the "2nd Investment" field, etc.

[orange]

Your issue is that your table structure is working against you.

You have Investors and Investments where
an Investor can have 1 or more Investments, and
one Investment could be associated with 1 or More Investors.
In my view you are missing a table that basically says ThisInvestorHasThisInvestment ( a junction Table)

Then you could have a report/subreport set up to easily give you what you are looking for.

How many records do you have? Do you have any Investors that do NOT have any Investments?

There are youtube videos for Report/subreport that may help.

see this one
https://www.youtube.com/watch?v=ZmVFcOMbShM

[Ryan Scott]

Doesn't the table itself already say which Investments each Investor has? I just need to be able to pull out the first investment, then the second, etc. Once I get that, inserting the Investment names into a report or a sub report will be easy.

Any thoughts on creating those nested SELECT statements? I think I'd be golden if I could just figure out the syntax to pull the last Investment from the top 10, then the last from the top 9, all the way up to the first Investment in the list. The only hurdle after that would be returning null values for duplicate Investments in cases where an Investor owns less than 10 Investments.

Also, I'm usually working with around 500 unique Investors. But with an average of 3-4 Investments each, this particular table will run to around 1,500 - 2,000 records. And yes, there are plenty of Investors without Investments, but none of them are recorded in this particular table.

[orange]

I haven't seen your tables with real data.
Perhaps you could show 10 or 15 records so we can see what exactly you have.

Did you look at the youtube link on reports/subreports?
Are you aware of Normalization?

[Ryan Scott]

SampleDatabase.accdb

I can't attach my actual data for confidentiality reasons, but the attached SampleDatabase is an identical replication. The table titled "DesiredResult" is meant to show how I'd like the query to look after it does its black magic on the Data table. Also, the Report shows where I'm trying to go with everything.

And yes, I did watch the YouTube video (very cool accent), but I'm not sure how creating a sub report will make my job easier, since I'm struggling to get the data into a layout that is readable by a report in the first place.

And although I'm also familiar with the concept of normalization, I'm not sure how to further reduce any redundancies my particular set of data. Perhaps you'll have some recommendations after seeing the database.

Thanks again for all your help here, orange!

P.S. Sorry for not posting the file as a .mdb - it wasn't an option when I clicked Save As. Let me know if that's an issue and I can try again.

[orange]

I have adjusted your database by adding the Tables that I mentioned previously
tblINvestors, tblInvestments, tblInvestorHasInvestments(junction table)

NOTE: SUBREPORT NOT needed.

I did this by creating table designs for investors and investments.
I used your Data table and data records to populate these tables by means of 2 append queries with names beginning Populate...

I created a form with 2 comboboxes and a button.
CboInvestor rowsource tblInvestor and
cboInvestment rowsource tblInvestment

The button(see below), when clicked, inserts a new record into tblInvestorHasInvestments.

tblInvestorHasInvestments was created by means of the table designer, and has a unique index on Investor and Investment. These are FKs to the related tables.

I used the form to populate the tblInvestorHasInvestments.

I then created a Report ReportFromQuery that uses a query QueryForReport as data source

It is grouped by Investor and shows that Investor's Investments in Investment name order.

The button has a click event where I take the values from the combo box selections and create an sql string to be executed as an INSERT INTO.

I did find 1 issue with Acc2010-- Even though I have a unique index on Investor + Investment in tblInvestorHasInvestments, Access is not signalling an error condition. It is not adding the duplicate record, but it is NOT identifying an error.
I set up an error handler to trap a 3022 duplicate, but Access was not giving the error.

I then added code to do a DCount to check for duplicates myself-- it worked - but I have commented it out in the code here.

I wil investigate this separately -- it may be that I have to set a Flag or something. I''m new to Access 2010.

Post if you have any issues. and as I said earlier -- table design is key to database.

[Ryan Scott]

Remarkable! You're a wizard! Your screen name should be Merlin!

I see now what you meant when you said that I needed a junction table. My "Data" table was already similar to a junction table, but the database lacked the two other tables that housed data about just Investors and just Investments. Turns out I didn't need to "consolidate fields from multiple records into one record" as the title of the thread says. I just needed a better table design.

Speaking of tables, what are those "sub tables" within the tblInvestor and tblInvestments tables? Are they the result of adding records via the append queries? Or of each table's one-to-many relationship with the junction table? Do they play a role in the process, or are they just byproducts of some other role-playing feature?

Regarding sub reports, I wonder if I may still need one. This Investor/Investment report that you created will be just one "section" of a larger report that lists other data specific to each Investor, which will be pulled from data sources other than the query you created. What do you think? Will that be an issue?

I saw one of your posts in another thread that lists several resources for learning Access, and I'm going to go through them. In my office of six people, I recently became the de facto "Access guy," which is not saying much for my firm's Access capabilities. Obviously, I need to study up.

Again, I can't thank you enough for your patience and generosity.

Ryan

[orange]

You are very welcome. It's comforting to see someone actually take some advice; search for other sources of info (videos); and then post a comment. Often people are convinced they know something or other and proceed along some strange paths. Get frustrated and complain and continue on their merry way.

There is a lot to database (concepts-wise) and a lot of it isn't obvious until you experience a problem. And M$oft isn't helping things by alluding that, with Access, anybody can be a "database person". The software is only a small part of database - a key part, but not the only or most critical part. A good database design can be implemented in any number of software RDBMS.

You may still be able to use subreport(s). I don't seem to have a copy of the Northwind database, but there are several good techniques and approaches in that database. There are examples for most everything.

There are good and bad samples on youtube -- mostly good.

I'm not sure what other posts you saw - probably the videos - but there are a lot of people on the forums who have a lot of experience and are willing to share.
If you have any issues post to the forum or send me a PM.

Good luck in your new role as "Access guy". Just remember the table and relationships are most import.

[orange]

Ryan,

I found the answer to the no error on duplicate record attempt.
It was my issue with the coding.

see https://www.accessforums.net/access/...ror-30382.html

for the answer.


This code should replace the corresponding lines in the button's on click event.

   
    CurrentDb.Execute strSQL, dbFailOnError
    On Error GoTo 0
    Exit Sub

Command4_Click_Error:
    If Err.Number = 3022 Then    ' attempt to add duplicate record
    MsgBox "That combination already exists. Please select another combination"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command4_Click of VBA Document Form_Form1"
    End If