By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

You have reached the limit on the number of tables that can be opened at one time

100+
P: 158
I have a bit of a problem printing a report.

I have 1 report and 6 subreports comprising the report that I need to print. I know there is a limit of 7 nested so I am under. The problem that I think it is = the amount of fields that I have open at one time. The report is about the condition of a suite at move-in and move-out stage.

Each report has about 20 multiselect fields in it. The multiselect field have 10 options that covers a range of condition from G=Good to R=Repair. I usually select one or two options from the multiselect combo box.

Question: Would having a value list "with string result" instead of a multi select combo box that must be related to each field make a difference in the size of the report and allow me to print?

If not, would a single value combo box reduce the size so I can print?

What else am I not thinking about? Does relationship or any other thing has anything to do with it?

One thing I can do is modify the design, one thing I can't is reduce the amount of fields as they all need to be there? what can I do? thanks
Feb 16 '10 #1

✓ answered by topher23

Sorry it took so long for me to get back. If you add the TenantID field to tblWalkThru, then you won't have any problems with changes to the table data that feeds the walk-thru report. You would add a new record to tblWalkThru every time you were doing a new walk-thru, along with all of the new records linked to it in tblWalkThruItems. The only change that doesn't work well is deletion - never delete a record from your data tables. It's actually best to use a check box to show whether a record is "active" or not.

For example: you have an item for the bedroom called "Bedroom Widget." You decide to replace the Widget with a "Bedroom Whatsit." You make "Bedroom Widget" Inactive, then you have code in your forms that will allow it to show up if that's what's currently in the room, but when you go to change the item it's filtered to only active items, so you have to use a "Bedroom Whatsit." It would work that way with Tenants when they leave, Buildings if you no longer manage them, etc. The records would stay on until (and if) you decide to archive them in a backup copy and erase them from the active database.

I hope that explanation did more clarifying than confusing on that issue. I worry that I may have overwhelmed you with all of the tables and the assumption of the forms that would go with them....

Share this Question
Share on Google+
12 Replies


topher23
Expert 100+
P: 234
That's kind of vague... the only advice I can give you with limited information is that I generally look at combining my data into as few subqueries as possible. It may just be that the subreports are all running queries that reference too many tables in total. If you could eliminate some or all of the subreports by combining the table data into queries, you may have better luck.
Feb 16 '10 #2

100+
P: 158
I don't really know to explain it better?

The report parent has record source referring to 4 tables.
All the other 6 Sub- report takes value from the table in which they were created. no query just a filter.
Feb 16 '10 #3

100+
P: 158
I've just checked on something and I don't know if it matters or not but here it is:

All my fields in the report and its 6 subreport have about 130 fields all together. None of those field refer to the LocCodeT table that contain the value for the 100 out of 130 fields. would that make a difference if they were all related? (relating the multiselect combo value to the fields in my report?)
Feb 16 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Instead of using combobox in a report, could you not pull the information in a query from the reports recordsource?
Feb 17 '10 #5

100+
P: 158
:0) You tell me.... I don't know? That is why I am asking lol.

If I go about what I've learned so far I have to say that my design must be flawed. As I have learned that usually, things are a lot simpler and logical than it look at first view. I tend to complicate thing given my experience level.
Short version of what I’ve tried to accomplished; Let me try the relationship way of explaining it.

I manage several apartment buildings
Each building has many suites
each suite has many rooms
Each room has many components that I must report on and have a tenant sign the bottom of the report. So all components must be listed.

Many components are the same. For example each room has a Floor, walls, ceiling, power outlets…. Not all have sink, bath, closet and so on….

Each component now can have a different state of condition
It might be “Good”,” Fair”, “Poor”, “Scratched”… I’ve used eleven different code to name these state of condition that may or may not be paired with one another. A wall can sometime be “stained”,”dirty”,”damaged” all at the same time or it can also be Just “Good”

How I’ve designed the tables:
I created 6 tables, Entry, Kitchen, Bathroom, Living, Bedroom, Patio and have created 1 lookup table for condition codes

Each table has a unique component: EntryWall, EntryCloset, EntryFloor, EntryLight…. For entry table. BedWall, BedCloset, BedLight… for Bedroom table and so on. Around 8 to 10 fields for each table.

Now I have to double up these fields because there is a Condition Report that must be made up at the beginning of the tenancy and a Condition Report made at the end of the tenancy. So all my fields have an "IN" and "OUT" at the end of it: EntryLightIN, ENtryLightOUT and so on...

In other words the Condition Report of the “Leaving” Tenant becomes the report for the next “Arriving tenant” once the cycle is started.

A quick way to gather all this information is to use MULTI-SELECT combo boxes on a form and walk through a suite with a Tablet Laptop and fill it in as you walk and then create a report from what I've gathered.
IS there a better design? I’m sure there is! I’m only 6 months old into programming in access so I still have a lot to learn.
Feb 17 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Which Access version are you using?

Is the tenant required to fill in all information for each item, or only for some items?
Feb 17 '10 #7

100+
P: 158
I use Access 2007. I also need to consider that I will migrate this database into Sharepoint at some point in time.

I fill-in all information of the suite as a "walk through"(all field must be filled-in) with the tenant at the time they sign up the lease, and repeat the report when they leave and deduct amounts from their security deposit if required.

Two report for each tenant.

The reason why I want to bring this in into database is obvious as these report gathering events can be time consuming in nature. I also must keep a "Current" state of condition for each suite. This mean when I change a light fixture or I paint the suite. I must update the record so it is reflected as being something that was done during the tenancy and annex this information to the original Move-In report.
Feb 17 '10 #8

topher23
Expert 100+
P: 234
Your design should be guided by what you need in the long term and not just right now. Based on your current design, you may be able to collect the precise data you want right now, but if something changes there may be issues.

**WARNING** This post is long and may get complicated and disconnected. I apologize in advance - I wrote it with a lot of distractions.

Remember that it is more compact and good practice to use an Autonumber field as a primary key and reference that key in query instead of text data. You should have the following tables to hold physical data:

Expand|Select|Wrap|Line Numbers
  1. tblTenant   'holds your tenants' info
  2.     FIELDS
  3. PK  TenantID
  4.     TenantName
  5.     etc... (other tenant-specific data)
  6.  
  7. tblBuilding   'This holds all of the data for a specific building 
  8. FIELDS
  9. PK  BuildingID
  10.     BuildingName
  11.     BuildingAddress
  12.     etc...
  13.  
  14.  
  15. tblSuite  'This holds all of the data for the specific suite
  16.     FIELDS 
  17. PK  SuiteID
  18. FK  BuildingID
  19. FK  TenantID
  20.     SuiteNumber
  21.     etc...
  22.  
  23. tblRoom   'a lookup table to give you all of the room names (kitchen, bathroom, etc.)
  24.     FIELDS
  25. PK  RoomID
  26.     RoomName
  27.     RoomDescription  'in case you want to provide a long description of the room
  28.  
  29. tblSuiteRoom   'An Interim table to select Rooms for each Suite
  30.     FIELDS
  31. PK  SuiteRoomID
  32. FK  SuiteID
  33. FK  RoomID
  34.  
  35. tblRoomComponent  'what component belongs in each room.
  36.     FIELDS
  37. PK  RoomComponentID
  38. FK  RoomD
  39. FK  ComponentID 
  40.  
  41. tblComponent 'The table that holds every possible component.
  42.     FIELDS
  43. PK  ComponentID
  44.     Component
  45.     ComponentDescription
  46.     etc...
  47.  
I would say that doubling the fields by having one in and one out is flawed. What you should do is have tables to hold all of your walk-thru data, like so:

Expand|Select|Wrap|Line Numbers
  1. tblWalkThru
  2.  
  3. Key FIELDS          TYPE         
  4. PK  WalkThruID      AutoNumber
  5.     WalkThruType    Text     'Combo with In, Out, Interim as values
  6. FK  SuiteID         Number  'the SuiteID from the Suites table
  7.  
  8. tblWalkThruItems
  9.  
  10. Key FIELDS          TYPE         
  11. PK  WalkThruItemsID AutoNumber
  12. FK  WalkThruID      Number  'the PK from tblWalkThru
  13. FK  RoomID          Number  'the PK from the Rooms Table
  14. FK  ComponentID     Number  'the PK from the Components table
  15.     Condition       Text?   'combo box with a condition from the conditions table
  16.  
I know it looks like a lot of tables, but even then it's not strictly third normal form.

To do a walk-thru, you first select the building. This gives you a number of suites. You then select the suite you want to do the walk-thru on. This information goes into tblWalkThru. You then have code that runs that populates tblWalkThruItems with all of the components and all of the rooms in the suite. The form now opens with all of the rooms, items, and condition possibilities. Then you can do the walk-thru and fill out the form.

Once the form is filled out, you need to print out a report. This report should show all current data for this suite, from the last move-in walk-thru to the current walk-thru.

To generate a report with all of the data, create a query as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. tblWalkThru primary key inner join to foreign key of tblWalkThruItems
  3. tblBuilding primary key inner join to foreign key of tblSuite
  4. tblSuite primary key inner join to foreign key of tblWalkThru
  5. tblRooms primary key inner join to foreign key of tblWalkThruItems
  6. tblComponent primary key inner join to foreign key of tblWalkThruItems
  7. tblTenant primary key inner join to foreign key of tblSuite
  8. WHERE TenantID = [the TenantID from your form] AND SuiteID=[the SuiteID from your form]
  9.  
If you first set up a report that groups by WalkThruType (In, Interim, Out), then put in a subreport that joins on the WalkInID and sorts by Room, you should have a pretty coherent report that will work for you.
Feb 17 '10 #9

100+
P: 158
GOODNESS.... Thanks a million for your hard work at structuring my tables and next. wow. I will copy and paste this into a Word sheet and see if I can rename some of the fields with what I currently have and make sense of all this....

I will be having fun to finally see a guide to help me figure this out. I really appreciate this Topher23. I am speachless after these couple of weeks of building this thing. Looks like I will need to reconfigure (better now then later like you said)

Cheers

PS: I will come back after I've done this and tell you how it went. Muchos gracias Amigo
Feb 18 '10 #10

100+
P: 158
I've uploaded a trimmed down copy of my database to show the report (conditionrepQR) as I originally built it. Something funny happened, I tested to see if it would load after taking so many tables outs and by eliminating personal information. Hold and behold the report works and I am able to print it and save it as a pdf also.

in the best of world this is how I would like the report to look like.

I have trouble understanding which part I took out that could have made it worked.

I've tried the new structure that you so patiently wrote for me and after a while I started thinking on how these tables would react to a change???? From what I can see of the structure I have no idea how I would be able to ad a new record. The way I see it I would have to "update" the table when part of a record would change which doesn't work for me because I have to keep old records. I really don't know anymore my brain is mush.
Attached Files
File Type: zip EvaluateAlainDB (3).zip (581.8 KB, 33 views)
Feb 20 '10 #11

topher23
Expert 100+
P: 234
Sorry it took so long for me to get back. If you add the TenantID field to tblWalkThru, then you won't have any problems with changes to the table data that feeds the walk-thru report. You would add a new record to tblWalkThru every time you were doing a new walk-thru, along with all of the new records linked to it in tblWalkThruItems. The only change that doesn't work well is deletion - never delete a record from your data tables. It's actually best to use a check box to show whether a record is "active" or not.

For example: you have an item for the bedroom called "Bedroom Widget." You decide to replace the Widget with a "Bedroom Whatsit." You make "Bedroom Widget" Inactive, then you have code in your forms that will allow it to show up if that's what's currently in the room, but when you go to change the item it's filtered to only active items, so you have to use a "Bedroom Whatsit." It would work that way with Tenants when they leave, Buildings if you no longer manage them, etc. The records would stay on until (and if) you decide to archive them in a backup copy and erase them from the active database.

I hope that explanation did more clarifying than confusing on that issue. I worry that I may have overwhelmed you with all of the tables and the assumption of the forms that would go with them....
Feb 25 '10 #12

100+
P: 158
No worries Topher23 I understood perfectly what you had written down and started to build the tables as you mentioned until I realized that the design you had written was the first design that I was going to use until I saw that there was too many variables to account for and reason why I had decided to go the way I built my table today.

I'm happy to say that I fixed the problem. I did use some of your wizdom
"I would say that doubling the fields by having one in and one out is flawed."
I followed your advice and this alone fixed my problem. So I deleted the new tables i had created and went back to my old design which works great now that everything is working and that I can get output out of it. thank you for all your great advices

Cheers
Jaad
Feb 25 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.