Full Version: Too Many Subforms
UtterAccess Forums > Microsoft® Access > Access Forms
TiredEyes
Hi,
would like to get some advice on the best solution for my problem. I have 3 forms in my project that each have 70 subforms, and my 1 year old computers have trouble displaying the forms.
Each form is essentially a datasheet where each cell has a different query, only each cell is actually a subform.
I have 6 employees and I am tracking their output over 9 past intervals, for instance 0-30d, 31-60d, etc. With total fields for employees and time intervals, that is 70 subforms per form.
There is a form each for Production Amount, Production Count, and Historical Average Amounts, so I can track their performance. That is a lot of queries.
I would have liked to have 210 subforms on one form, but split it into 3 forms to get it to display, and now sometimes it still won't display. My search found nothing about maximum number of subforms on a form.
http://www.utteraccess.com/wiki/index.php/Access_Limits
I am hoping there is a more elegant way of displaying all this calculated data. Again I would love to have it all on one form to look at all the calculations together. I hope I don't have to have a form for each employee, since that would make comparisons harder.
Thanks for your help in advance.
Dan
pere_de_chipstick
Hi dan
0 subforms ???? 70 sub forms will only serve to slow down your main form to the point it becomes unuseable.
Oreally would reconsider your design here - the trick (IMHO) is too display only the relevant information a user wants at a specific time without any other clutter which might distract or confuse.
From your description, you don't intend to use these subforms to edit the underlying data so perhaps it would be better tu use a query to create the recordsets you need and display it in a report.
hth
dblife
Wow, that's a lot of forms.
I am sneakingly suspicious you may have structure problems too.
Could you post a brief description of what you are trying to achieve, what you're tracking about each employee and any business rules you think are pertinant. A screen-cap of your entity relationship diagram would be useful too.
ounds intriguing.
TiredEyes
Thanks,
ounds great
My new screen name is Tired Brain
Dan
missinglinq
This has got to be the understatement o end all understatements! There's no way in the world that this database is designed properly! In point of fact, what you're describing here is not a relational database at all, but rather a spreadsheet application.
think you really need to step back and re-examine your entire approach here!
TiredEyes
Didn't see your post dblife. I think I got it solved with pere_de_chipstick's suggestion, so hesitant to manipulate it to upload.
nyway to give you more information:
The columns are 0-30d, 31-60d, 61-90d, 91-120d, 121-150d, 151-180d, 181-270d, 271-365d, 366-730d, and Total for 10 columns
The rows are 6 employees and Total for 7 rows
Odesigned the form with a separate query for each cell of the above "table" and where each cell is a subform. Basically the hardest way possible to display the data, but the one my tired brain chose to do very late at night.
Pere has suggested consolidating the queries by another level of queries and then using a report. So I will make a query for each employee to combine 10 queries with one field into one query with 10 fields. Each employee's calculated data will be displayed in a report. The form will have 7 subreports to replace the rows. I'm sure I could combine all 70 queries with one field into one query with 70 fields as well and have only 1 report too.
Dan
TiredEyes
missinglinq,
I was not asking a question about the relational database that 6 employees are working with 40 hours a week. I was asking about displaying the results of all that work as it changes over time so I could monitor their performance.
Yes, I could use excel to do that, but didn't want to.
Dan
dblife
No problem..
would still like to see the ER diagram if I may, I am intrigued now.
dontknow.gif
pere_de_chipstick
Hi TiredEyes
I am pleased that we may have been able to suggest a working solution for you. However the concern is that your db table structure looks very much like a spreadsheet and is not optimised for Access.
If that is the case then as your development proceeds it is going to become more and more complex for you to get your database to achieve what you want it to and ever more difficult to put it right in the future.
If you post your db here, you could delete all data in it, C&R it and then (most importantly) ZIP it before posting and I'd be happy to take a look and make any suggestions.
TiredEyes
Alright, and appreciate your help.
This is a database program is basically a tickler file to record and monitor collection attempts and to remind the staff of which claims from insurance companies should be reassessed for our medical practice.
tblCollections
ClaimID (AutoNumber)
PatientNumber FK (Number)
DateOfService (Date)
ClaimType FK (Number)
Amount (Currency)
InsuranceNumber FK (Number)
StaffMember FK (Number)
CollectionAttempts (Memo)
NextReview (Date)
DateEntered (Date)
DateFinished (Date)
tblPatients
PatientID (AutoNumber)
ChartNumber (text)
LastName (text)
FirstName (text)
DateOfBirth (text)
tblClaimType
ClaimTypeID (AutoNumber)
ClaimType (text)
tblStaff
StaffID (AutoNumber)
StaffName (text)
Password (text)
tblInsurance
InsuranceID (AutoNumber)
InsuranceName (text)
InsuranceCode (text)
The problem with private medical insurance billing or collections is that 17-20% of all claims on average are never paid by the insurance companies. That makes up most of their profits in my opinion. The battle is to compromise with how much billing staff manhours are appropriate for the percentage of claims that will get paid. There is a point where more wages paid is not cost effective for the marginal improvement in increased revenue.
What I wanted to do was display a form showing how different employees perform based on claim amounts, claim counts, and to their historical average for claim amounts.
Dan
dblife
Thanks for the explanation.
If you have a solution you are already happy with, I do not want to interfere.
Is I understand it, your staff spend their time chasing insurance companies for money and you need to assess at a glance when the profit margin for each claim is about to be wiped out by the money you will have to pay your staff for chasing the claim.
Each member of your staff is assigned a particular claim.
You need to know which members of your team are processing claims quickly and effectively and 'paying their own way'
Is that an accurate description?
TiredEyes
That's basically it dblife, although we won't stop chasing individual claims as much as assigning a different priority to some claims. Also, we will be able to make better decisions on whether we need more or less man-hours for the department, should we hire another biller. That's the business side benefit for wanting to display all the calculations
lthough the deeper problem is that after using the database for 8 months, one of my employees is becoming much less productive from my observations, and I wanted to get data.
Oneeded to mine the data to show my partners that there has been a change so we can monitor that employee's improvement after we show her the drop-off in her production. The scuttlebutt in the office is that she is having an affair and we need her to focus on business at work.
I can keep employees off of facebook on their workstations, but not on their telephones. People that are young enough to have grown up texting know how to do it with one arm below the desk and just an occasional glance at the screen. I wish it was legal to jam phone signals sometimes. Facebook and social networking at work has been a serious drain on productivity I think.
I know, too much information, but venting is good.
Dan
dblife
I bet it's a very common problem.
Let us know if there is anything further we can do.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.