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

[jaad]

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

[topher23]

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.

[jaad]

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.

[jaad]

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?)

[TheSmileyCoder]

Instead of using combobox in a report, could you not pull the information in a query from the reports recordsource?

[jaad]

: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.

[TheSmileyCoder]

Which Access version are you using?

Is the tenant required to fill in all information for each item, or only for some items?

[jaad]

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.

[topher23]

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:

tblTenant   'holds your tenants' info
    FIELDS
PK  TenantID
    TenantName
    etc... (other tenant-specific data)
 
tblBuilding   'This holds all of the data for a specific building 
 FIELDS
PK  BuildingID
    BuildingName
    BuildingAddress
    etc...
 
 
tblSuite  'This holds all of the data for the specific suite
    FIELDS 
PK  SuiteID
FK  BuildingID
FK  TenantID
    SuiteNumber
    etc...
 
tblRoom   'a lookup table to give you all of the room names (kitchen, bathroom, etc.)
    FIELDS
PK  RoomID
    RoomName
    RoomDescription  'in case you want to provide a long description of the room
 
tblSuiteRoom   'An Interim table to select Rooms for each Suite
    FIELDS
PK  SuiteRoomID
FK  SuiteID
FK  RoomID
 
tblRoomComponent  'what component belongs in each room.
    FIELDS
PK  RoomComponentID
FK  RoomD
FK  ComponentID 
 
tblComponent 'The table that holds every possible component.
    FIELDS
PK  ComponentID
    Component
    ComponentDescription
    etc...
 

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:

tblWalkThru
 
Key FIELDS          TYPE         
PK  WalkThruID      AutoNumber
    WalkThruType    Text     'Combo with In, Out, Interim as values
FK  SuiteID         Number  'the SuiteID from the Suites table
 
tblWalkThruItems
 
Key FIELDS          TYPE         
PK  WalkThruItemsID AutoNumber
FK  WalkThruID      Number  'the PK from tblWalkThru
FK  RoomID          Number  'the PK from the Rooms Table
FK  ComponentID     Number  'the PK from the Components table
    Condition       Text?   'combo box with a condition from the conditions table
 

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:

 
 tblWalkThru primary key inner join to foreign key of tblWalkThruItems
 tblBuilding primary key inner join to foreign key of tblSuite
 tblSuite primary key inner join to foreign key of tblWalkThru
 tblRooms primary key inner join to foreign key of tblWalkThruItems
 tblComponent primary key inner join to foreign key of tblWalkThruItems
 tblTenant primary key inner join to foreign key of tblSuite
WHERE TenantID = [the TenantID from your form] AND SuiteID=[the SuiteID from your form]
 

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.

[jaad]

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

[jaad]

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.

[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....

[jaad]

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