Add fields accross multiple records on forms?

mrk

MRK
Local time
Yesterday, 18:30
Joined
Jul 30, 2009
Messages
8
Hey thanks for having this forum-I've learned a ton already! But, I have a table 'Investors' with fourteen different fields containing the amount of money the investor put in a fund. These are displayed on an Investor Entry Form and summed. (The data is all one table.) The actual person or company behind the Investor record may be involved in more than one record so I have assigned a 'group number' to associate those records.

I want to sum the funds on the related records for a group because my boss wants a list of the investors who have paid in the most money in descending order!

I back doored this by exporting the table, summing the groups in Excel and assigning a rank and then appending that rank to the records in access. All well and good but I have to do this about every six months and I'd rather it be automated because I forget the steps every time!

I do not know SQL code and only use the interface in Access 2003. I'm not great at this! So this KISS principle applies! Thanks in advance for your patience and help!
 
First question: Have you read up on normalization? I ask because anything that has 'the data all in one table' makes me worry that you could be making things harder on yourself than you have to. But maybe you meant all of the investment data, only.

If you've done that, then you should look into the Totals button in Query Design View. It provides options for Grouping your data by Investor, then Summing the monetary values.
 
David, First, thanks! I did read normalization but I am having a problem getting my head around how that would work. I really have to be honest and say I think it's beyond my comprehension!! Or I just haven't worked with a teacher who can help me with it. I think I should probably have the funds in their own tables but the db was setup before I started here and I've been able to make it do what we need. I'm happy to say I dont have any lookup tables!

Okay - I forget about that totals button! I think this is what I should do: On each record create a Expression that adds all the Fund values and then create a query that include the GroupID field and press the total button. (that's way easier than my back door!)

Question: If I want that to show on the Investor Entry form do I need to make it a subform? Then can I sort the all records by that?
 
Are you authorized to change the database structure? If so, you're going to be a LOT happier in the long run.

Without knowing your current structure, I'll take a guess that it's got InvestorID, InvestorName, InvestorOtherFieldsAsNeeded, GroupID, Fund1, Fund2... Fund14?

What you really want is about five main tables:
  1. tableInvestors: This is for 'people'. Store InvestorID is your Primary Key (autonumber is best, but if you already have an unique internal tracking number you can use that too), InvestorFirstName, InvestorLastName, etc.
  2. tableGroups: This is for 'groups' (companies, plans, whatever). GroupID is your PK, any company address fields, categorization/tax-exemption, etc go here.
  3. tableRelationships: This table should only need three fields: RelationshipID (autonumber PK), GroupID (Long Integer), and InvestorID (Long Integer). You can also make your PK multifield (GroupID and InvestorID together), which will automatically prevent you from duplicating connections. That may or may not be appropriate for your data. What this table is doing is allowing your investors and your groups to have a Many-to-Many relationship.
  4. tableFunds: Like Groups, this is a 'lookup' table. It contains the FundID Primary key, of course, anything like FundName, FundTickerCode, etc that isn't dependent on the investor doing something in the Fund! This table should be fairly static, since I think you said you had 14 funds.
  5. tableInvestments: This is another Many-to-Many table, but this is where the meat and potatoes of your investment tracking goes. InvestmentID is your Primary Key, and absolutely needs to be autonumber here, because you're going to have a lot of entries. FundID (Long Integer) and InvestorID (Long Integer) are exactly what you will expect; you'll also probably need InvestmentDate, InvestmentAmount (Currency, probably), and any other details you track on individual deposits/cashouts. You may need a boolean field for dividend reinvestments, tax penalties, whatever... you know your business model better than I do.
Finally, you need to make sure all of your tables are linked correctly in Tools>Relationships.
  • tableRelationships's InvestorID is the Many (∞) side of a One-to-Many relationship with tableInvestors' InvestorID (1).
  • tableRelationships's GroupID is the Many (∞) side of a One-to-Many relationship with tableGroups' GroupID (1).
  • tableInvestments's InvestorID is also the Many (∞) side of a One-to-Many relationship with tableInvestors' InvestorID (1).
  • tableInvestments's FundID is the Many (∞) side of a One-to-Many relationship with tableFunds' FundID (1).
When you're done with all of that, all you'll have to do to get the data you need is open a query on tableInvestments. Group By GroupID, Sum InvestmentAmount, and put a Where restriction on InvestmentDate (you can do this automagically by quarter, or prompt the user from a form to run the query). I may be a little unclear on the nature of your 'grouping' ID, so adapt as needed.

This may seem like a lot of changes for a simple query; it's only 14 fields, right? Now wait until a year from now, when your firm has discarded 3 of those funds, added 4 others, and also wants a report based on individual investors, as well as by group. Changing it with a normalized database is going to be orders of magnitude simpler!

And yes, with a normalized database it will be easy to show all of an Investors' transactions as a subform of the Investors form (Parent/Child field will be InvestorID; the subform will be based on a query of just tableInvestments and tableFunds).
 
Wow David. Thank you! I've just been sitting here thinking about this kind of structure change and I think I can do it! I'm excited to try. A number of questions.
**************
First inexperienced and probably dumb question…when you add new records to these fields how do the tableRelationship records get created? Through the forms? Or maybe I should just create the tables and watch it happen – skip this if the answer will come to me when I go through this process!
**************
The one weird thing that will happen is the InvestmentName is the same as the InvestorName. In these cases when I create mailing labels it duplicates the person’s name and the Investment Name:

David R (tableInvestments)
David R (tableInvestors)
122 Anylong Street (part of tableInvestors)
City, ST 12345 (part of tableInvestors)

How would you prevent that on mailing labels?
Remember I don't know SQL!
**************
I want to clarify my structure a little better to ask another question:
tableInvestors. These are the people. (InvestorsID, FName, LName, address, SpouseName, mailing pieces sent here)
tableFunds. These are the 14 funds. (fundID, fundName)
tableInvestments. These are the actual entities (like an account name) that investment in fund(s). (InvestmentsID, InvestmentName, TaxId, EntityTypeCode)
tableRelationships. Yikes this is going to be scarry. RelationshipID (autonumberPK), InvestorID, InvestmentsID

Where would you hold the amount the tableInvestments committed to invest in the fund(s)? It seems like if I put it in tableInvestments it will duplicate data. Maybe it has to be in its one table tableCommittment?

(In English: Where would you store the information that David R is going to commit to investing $200 into the XYZ Fund in the name of David R Children’s Trust? Specifically “$200”)
***************
I’m going to be soooo much more efficient and smarter after this, I can tell!! Thank you so much.
 
I may reply in several smaller posts, depending on how long these get.
Back up your database before you start this process!!
when you add new records to these fields how do the tableRelationship records get created?
tableRelationships is probably going to come out of reverse-engineering your 'grouping' field. Go into a query based on your current Investors table and have it display the fields for InvestorID (if you have one already, probably Autonumber unless it's internally generated) and the field for GroupingID (or whatever you're calling it now - the thing that tells you what Investment Entity the person is acting on behalf of). In the menu bar of Query Design View, there's a dropdown just called 'Query' (there's also a button for this, just to the left of the !) - change this to say Append Query. When it asks for what table to append to, choose your new tableRelationships.The Show: bar of query design will change to Append To:. Under each of the fields you're showing in the query, select the relevant new field in tableRelationships in this Append To: line. You can preview this 'action' query (without changing any data) like a normal query; if the fields show up blank, you've done something wrong (RelationshipID will take care of itself, because it's an Autonumber PK). Then*, back in Design View, click the ! button and you'll get a couple of popups asking if you're SURE you want to do this. Choose yes, and then you should be able to go into your new table and see those two key fields.

* once you build your new tables and perhaps BEFORE you populate them with data, I would recommend going into the Relationships window and connecting the Primary key/Foreign Key fields (just click-drag one to the other) and enforce Referential Integrity in the popup that comes up. This'll help you spot bad data a LOT easier later in the process, and going forward. Generally you'll also want to enforce Cascade Update and Cascade Delete, unless your workflow indicates otherwise. http://support.microsoft.com/kb/304466 has a lot more detail on this, if you like, though it does lack screenshots.
This will also affect what order you can fill your new tables (you may be able to keep your old tableInvestors and tableFunds (and maybe tableInvestments, if it exists), just deleting the extraneous fields when you're done); you'll have to do 'reference' tables first, then connecting tables. I bet you can figure out which is which.
 
The one weird thing that will happen is the InvestmentName is the same as the InvestorName. In these cases when I create mailing labels it duplicates the person’s name and the Investment Name:
Easiest way is to use OnFormat code for collapsing one of the two fields in your label report. Here's one example to get you started: http://www.access-programmers.co.uk/forums/showpost.php?p=257487&postcount=3 - just change it to compare the two fields; if they're identical, hide the one you don't want.

Alternatively, can you leave off the InvestmentName if the investor is a single person, not a trust/corp/LLC? This would require your workflow to be able to deal with ignoring tableInvestments if the person is by themselves. It may mean fiddling with your report structure a bit, because they won't have an InvestmentID or whatever when you run the report, which may mess up your grouping. But if it can be done, just make the Relationship between those two one-way (under Join Type, pick "Include ALL records from 'tableInvestors' and only those records from 'tableRelationships' where the joined fields are equal"). Then, in theory at least, you should be able to make that InvestmentName field collapse (Can Shrink = Yes) on your labels, and it should disappear fairly neatly. Two caveats: Don't have the fields touch each other on the form (at least in some versions of Access, this interferes with CanShrink - but they can be as close to each other as you like, by adjusting the height/position manually rather than with the cursor). Second thing is DON'T set your entire label Detail section to CanShrink - I did this a few times and my labels kept creeping up the page!
 
Where would you hold the amount the tableInvestments committed to invest in the fund(s)? It seems like if I put it in tableInvestments it will duplicate data. Maybe it has to be in its one table tableCommittment?
Not sure if I understand this one. In even simpler English, this is money that David R is going to invest in the Trust Fund, but hasn't yet?

I would probably make a checkbox in your tableTransactions (or whatever you're calling it, my tableGroups became your tableInvestments) that says 'This transaction is promised/pledged/committed, but not executed yet.' Then make sure to check that anything with a True (-1) value in this field is EXCLUDED from your "actuals" reports. You may want to create a query or form that goes through and warns of uncommitted transactions that are older than X days/months - another option would be to put a ExpectedDate field that shows when that transactions should be executed by.
 
Okay WOW! This is a lot of stuff to learn! I'll be out of questions for a while to get this going. I'm building a test datbase to try this. But I want to be sure I'm on the right track. This is an example of my raw data in a spreadsheet layout.

Group...InvestorFName.InvestorLName..Investor Addr.....InvestmentName......InvType....Fund1AmtCommitted...Fund2AmtCommitted
001.....Andy..........Anderson.......122 Anylong St....Anderson Trust......A..........$200................$0
001.....Andy..........Anderson.......122 Anylong St....Andy's Kids Trust...B..........$150................$150
002.....Mike..........Smith..........35 Oak Street.....Mike Smith..........A..........$200................$0
002.....Mike..........Smith..........35 Oak Street.....Mike Smith..........A..........$150................$150
002.....Mike..........Smith..........100 Business Rd...Smith Co. LLC.......B..........$200................$250
003.....Johnnie.......Smith..........100 Business Rd...Smith Co. LLC.......B..........$0..................$150
004.....Johnnie.......Smith..........100 Business Rd...J&J Partners........A..........$300................$0
004.....Dawn..........Smith..........100 Business Rd...J&J Partners........A..........$300................$0

For how it lives now:
See how Johnnie is in two groups? BAD.
See how Andy's address repeats...bad for address changes.
See how Mike gets stuff at two places...okay except when I only want to send one invitation to him at his home.

Can you remake the suggested relationships for me? I feel good that I'll be able to do this to have a 'best practices' database that will survive the test of time. Thank you a bunch!
 
For how it lives now:
See how Johnnie is in two groups? BAD.
See how Andy's address repeats...bad for address changes.
See how Mike gets stuff at two places...okay except when I only want to send one invitation to him at his home.
Good, now you're seeing what a properly normalized database will gain you here. :) Fixing Andy's address will be a snap. Johnnie's presence in two groups isn't a problem if that's the way your data actually works - are you worried that you'll miscredit investments to the wrong group? Right now we've got it set up (I think) so that transactions are tied between tableFunds and tableInvestors - you may need to shift that so it's between tableFunds and tableInvestments, if I'm reading your data correctly. That way Mike's contributions to Mike Smith (InvestmentName) are separated correctly in your report from Mike's contributions to Smith Co. LLC. Is it important to the report who (from tableInvestors) did the actual transaction?

Not quite sure what to do for the case where Mike has two addresses. Do you only have to send him stuff at home if he's made a donation to Mike Smith (InvestmentName)?
 
Thanks again for your help David. I appreciate your feedback and help! I feel good that I have the many-to-many thing set up!! Now I made some forms and would like your advise!

I wanted to run the forms from a query I wrote so I could limit them to showing investors or prospective investors from the tableContacts.

I made a ContactType field as a ComboBox using a value list that I keyed in on the table.

It seems as if when I use the wizard for forms, it shows the data source as the table and it isn't using the query I created to limit as determined by the Contact Type.

Am I going about this right? Maybe I just need to try and create it without the wizard? Thanks again!
 
I made a ContactType field as a ComboBox using a value list that I keyed in on the table.

It seems as if when I use the wizard for forms, it shows the data source as the table and it isn't using the query I created to limit as determined by the Contact Type.

Am I going about this right? Maybe I just need to try and create it without the wizard? Thanks again!
Sorry for the delay in responding, work and life are kicking my butt.

Is your query using a WHERE clause to limit the values shown? If so, you can program your combobox to update the recordsource with that WHERE clause (it's one of the default options when you use DoCmd.OpenForm). Otherwise, you can refresh your form's own recordsource, but it may lose track of what record it was on, unless you're talking about a subform.

Here's an example of code that may get you started:
Code:
If Forms!Contacts.RecordSource <> "queryContacts" Then
            Forms!Contacts.RecordSource = "queryContacts"
        End If
        Forms!Contacts.Filter = searchString
        Forms!Contacts.FilterOn = True
...where searchString is that WHERE clause you built up.
 
David R - No problem on delay - I just appreciate all your help! I hope life is more sane for you soon! I have a new question for you regarding queries I've built.

I have three tables, lets say they are:
tblContacts Fields: AutonumConPKey FName LName Address
tblInvestmentEntity Fields: EAutonumEntPKey EntityName Status(active/inactive)
tblRelationships links the primary key of tblContacts and tblInvestmentsEntity and defines the relationship with field: RelType, MailPieceCodes

A record in tblContacts can be related to many tblInvestmentEntity and vise versa

I created query with all three tables and then choose the people I want a mailing label for by using Like for the RelType and MailPieceCodes and Status

I want to print a mailing label with the a contact's address only once but I want it to show at least one of their related EntityNames. If I show the EntityName in a join query it is a unique record and therefore prints the address twice with the different name. My only guessed solution is to remove the EntityName? I tried the Group command but it doesn't work. I played with SELECT DISTINCT in the SQL window but that doesn't work either.

I'm frustrated! I'm sure I'm missing some wise solution! Please help!
 
If you don't care which EntityName from their set is the one that shows, try using the 'First' option in the Total: line when you've got a Grouped Query. 80% of the time you run labels, it'll show the same EntityName, but Access will occasionally shuffle it for no reason I can determine.

If it matters which name, you may be able to sort them by 'priority' or size of firm or some other field, and order the query that way to have the First entry be the one that shows, consistently the same.
 
Last edited:
Thanks David. The FIRST command works when I concatenate the first and last name!! You've been a great help. I'm making good progress now...You're a great teacher!
 

Users who are viewing this thread

Back
Top Bottom