Subforms transferring values

[Yasir]

Hi everyone

Access newbie here. I have a problem in which subforms don't commit records and values get copied to other subforms.

I have one main form called Growth which is linked to a 'growth' table, in that form I have 6 subforms connected to a 'finance type' table, in each of those six forms I have 10 subforms linked to a table called 'disbursements'.

What is happening is that is that when I change a value in one subform, the values in the rest of the subforms also get changed. I tried adding code to save the record on lostfocus, and adding new record on gotfocus but it's not working. Any ideas?


Thanks!

 

[MarkK]

It doesn't make sense to me from a workflow perspective to have one form that ultimately hosts 66 other forms. Apart from whatever your technical problems with this arrangement might be, I'd reconsider how you think people will be using this interface. What are the specific tasks that need to be accomplished in respect to the data, and can you design a few much simpler forms that specifically targets those tasks?

 

[Yasir]

Thanks for the reply lagbolt!

The project I am working on is a 10 year projections system. In the first module, 'Projected disbursements', we have different growth rates for 10 years, in each of those ten years we have 6 different finance types and in each of those finance types, we have 10 year disbursement trends (10 percentage figures that show how the amount is expected to disburse over a ten year period).

So for example, we lend 100,000 this year and the expected growth rate is 10% for next year, we're going to be lending 110,000. Now that 110,000 is divided by percentage into six different modes of finance. For example 20% in loans, 10% in leases etc... So in this case 11,000 is going to be approved for leases for the year 2010. And this amount is given out over a number of years and this is the disbursement trend. They may say 10% in the first year, 20 in the second, 30 in the third.

10x6x10.

So all in all there are about 600 fields just for the disbursments. The way I was doing it was by having 10 disbursment trend subforms in 6 finance type subforms for one year. Is there a way I could design the form so that it looks like a 6x10 table for finance types and a 10x10 table for disbursement trends?

Yasir

 

[MarkK]

It can't be right that you have 600 fields.

- A table represents a type of thing. Each record of that table represents a single instance of that type of thing. Each field in a record represents a property of that instance.

- As an example, a beginner in relational databases with a bunch of Excel experience might create a table for Apples and a table for Oranges and a table for Grapes. Generally a better solution in a database is to create a table for Fruit and a table for FruitTypes, where one field in a Fruit record is the ID of a FruitType record.
- The problem in the first case is that if you expand your operation so now you deal with Bananas, you need to rewrite your system. In the second case you simply add a Banana record to your FruitTypes table and you're done.
- I seems very likely to me that if you are dealing with the structures you describe, that you've created something more akin to a table for Apples, a table for Oranges, and so on...
- Another common error is creating a table specifically for dates or other time periods. Time is (almost) always a variable quantity and is therefore (almost) always an attribute or measure of some other more tangible thing. ie. a field in a record. An Order table will have an OrderDate field, but there need never be a Years table to help you group your orders. GroupBy Year(OrderDate).
- In many cases the date is irrelevant. Calculating compound interest is periodic, so it's irrelevant to the math that the period happens to be, say, monthly. What matters is a count of the periods.

If this seems relevant and for more assistance, post more info on the tables you have and how they are related, something like this...

tFruit
FruitID (PK)
FruitTypeID (FK)
Price

tFruitType
FruitTypeID (PK)
FruitName

Cheers,
Mark

 

[Yasir]

Actually I meant 600 fields in the form, not in the table. I've attached an image of the diagram so you get a better idea of my problem. And I just have an issue with the form design and being new to access doesn't help! What would be the best way of doing it?

edit: In case you're wondering why there are three tables called growth. I just moved some fields around and now I have to join the first two tables and call them main or something. And the 'growth' field there is to hold the mode of all the growths across ten years, just to identify the projection. The version is there because they intend to run multiple projections at multiple growth rates.

Thanks!

 

[MarkK]

Ok, I don't understand these objects or relationships even a little bit. I don't see the difference between Growth and GeneralGrowth, for instance. Couldn't the Growth field in GeneralGrowth be called Percentage? And if so, your Growth, GeneralGrowth and Disbursement tables have no significant differences.
I don't understand what it means that one FinanceType has many related PortfolioMix records.
Sorry. Totally stumped.

 

[Yasir]

Apologies for the late response, I wasn't well.

The growth in GeneralGrowth is the general growth rates for the ten years as a whole as they usually refer to projections by the year it was run, the growth and the version. So a projection could be for the year 2010, with a 15% growth rate and version (a,b,c,d....) of that. In that case they would say that a projection is for a 15% yearly growth rate, however, this doesn't necessarily mean that every year is going to grow at 15%. They might be expecting a larger growth in one year than in the other years, so every year would be 15% but one, which would be 18%. This is why there is another table called growth, with a specific growth rate for each year.

Regarding FinanceType and PortfolioMix, the portfolioMix is made up of six finance types. If, for example, 10,000 is expected to be approved in the year 2010, then it has to be divided up into different finance type. 10% could be in the form of loans, 20% in the form of a lease and another 15% in the form of equity investment. So the portfoliomix would have the financetypecode and a percentage for that.

Then the different finance types will each have a disbursement trend, i.e, for loans they might wish to disburse only 10% in the first year, 20% in the second, 50% in the third and so on. For equity investments on the other hand, they might wish to disburse 0% in the first year, 30% in the second, 30% in the third and the rest in the fourth.

Thanks a lot lagbolt, I am totally lost as to how to design the form for something like this.

 

[MarkK]

Just to be clear. You don't have a form design problem you have a table design problem. The problems you are having with your forms are a symptom.

So Growth and GeneralGrowth, for instance, differ only by type, not by structure. In this case I would not grant them each a table. This is the same as having a table for PendingOrders, a table for CompletedOrders, and a table for ProblemOrders, etc... They are all orders!!! They differ not by structure, but rather by status or type. In this case have one Orders table, and create a field called OrderStatus. To work with only PendingOrders write a query with a where clause: "WHERE OrderStatus = 'Pending'"

The ways you describe portfolio mix I would expect there to be two tables Portfolio and PortfolioMix. In this case there is the discrete and indivisible thing or concept: The Portfolio. Then there are the discrete and indivisible components of the portfolio each specifying a FinanceType as a percentage of the whole. These are your 'many' PortfolioMix records that are related to your 'one' portfolio.

Step back a little further from this structure and what you are in fact creating is a many-to-many relationship between Portfolio and FinanceType. A Portfolio is comprised of many FinanceTypes and each FinanceType might participate in many Portfolios. In this situation, each PortfolioMix record is the object that defines the relationship between the concept Portfolio and the concept FinanceType.

Your third paragraph seems to describe disbursement as a concept that modifies or defines a finance type. If this is the case it needs to be directly related to the FinanceType table. But again, your Disbursement table seems to have no functional difference from your Growth or GeneralGrowth tables. All it contains is the position in the series (year) and value (percent). Really these don't seem a lot different from x and y corrdinates.

Cheers,

 

[Yasir]

Thanks man, I've gotten rid of GeneralGrowth and added the fields to the GrowthVersion.
Regarding portfoliomix and portfolio, I think I've already done that. One growth record has one portfolio with many portfoliomix records, hence the one to many relationship with portfoliomix. I think this is the many to many relationship with finance type you mentioned (between portfolio and financetype but since one growth record has one portfolio, it is a many to many relationship between growth and financetype with portfoliomix as the junction table). Is this simillar to what you were talking about?

About disbursement, there is a one to many relationship between portfoliomix and disbursement and disbursement has the finance code. One portfolimix record is of a specific finance type and this has ten disbursements. I'm not sure how I can create a relationship between the financetype and the disbursement tables. About the disbursement table and the growth table, what should I do regarding it? I've thought about it a lot and can't come up with anything.

 

[MarkK]

Your first paragraph: Yes.
Second paragraph: I don't know. In a previous post you were talking about a disbursement like it was a classification or a detail of a finance type. In that case it would seem odd for it not to be directly related to that table. But I restate that I don't understand the problem you need to solve, so I'm not telling you how I'd solve it, just offereing my best guess with what I know about tables.
And tables are the foundation. If they're wobbly the whole house will be wobbly.