Apr 7 2005, 01:10 PM
Ok here goes.
A form with a subform. The subform uses a query with table C and D wherein D is the Account Master.
Table C has 1 record per Person per Account per Year. I want to display 5 columns:
Year 1, Amount and Comment
Year 4, ...
The Years are fed from a dropdown in the form using a function to filter each record and which column it belongs to, 1 to 4.
Now my limit. Using standard subform, I can only display 1 year's amt & comment per record. Even if I use a filter and multiple columns, only one column per row will still display. So...
Now the ugly.
I used SELECT DISTINCT and multiple subforms to handle it (they work). But now I HAVE to have the same number of records managed for each person/account to accommodate horizontal alignment between subforms and don't even ask about Add and Delete a row.
Ugly, c'est non?
Apr 7 2005, 01:15 PM
Can you maybe post a stripped down copy of your DB?
There could be a normalisation issue which when resolved will make your life easier.
Apr 7 2005, 01:21 PM
I will work on the DB posting...but be aware that this cannot become non-normalized, that's where it started, the detail records contained 10 years, 10 fields per record and the user hit their limit this year. I can't see them reverting to that non-normalized technique.
Apr 7 2005, 03:38 PM
are you saying you have a table with 10 years as 10 seperate fields? If so then that is not normalised... You have repeating groups.
Apr 7 2005, 03:53 PM
Yes. It WAS non-normalized with 1 record having 10 separate fields, Year1, Amt1, Year2, Amt2, etc.
That is now been restructured into a normalized mode...the original record has now been broken apart into 10 separate records, normalized.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here