Switch fields to records? Should I?

[leicklda]

I am a relatively new user trying to get a work database going. Can someone please help me??

I have a table in my database with about 75 fields in it. There are about 30 records in this table. Conceptually, the records have 3 tiers which means that there is one "master record", 4 secondary records, and about 20 tertiary records.

Structurally (in the table), all records are equal - each is a single line entry in the table. The tiering system only comes into play in the following way:
The master record has all 75 fields filled in.
The secondary record only has some of the fields filled in. If any field in these secondary records are blank, the values are to default to the master record values (although for now this just means that they stay blank).
The same goes for the tertiary records is just another level on top of this.

Here are my problems:

I cannot make a report with so many fields, Access won't let me. It seems to cut off capability to make a report at around 25 fields.

I will want to print condensed lists of records in the second and third levels that show only the NONBLANKS - that is, that show only the "upgrades" to the master record. However, I don't know how to tell access not to print blank FIELDS. I can easily tell it not to print blank RECORDS with the "is not null" statement in a query, but I don't know how to tell it to leave out blank fields.

I know that in a query, I can just not include the fields in the query, but this wouldn't work because A) every record will have different blank fields and I don't want to make a different query for each of the 20 records. and B) the blank fields will change periodically as the levels undergo changes.

The easiest solution seems to be to just switch the fields and records - that is, make the records into fields and vice versa. But there are two problems with this:

I need the records to remain records because they are linked with other tables in the database

I can't figure out a quick way to switch all the records and fields without retyping everything anyway.

I know this is a long post, but any help would be appreciated!

Thanks, Darleen

 

[WildHare]

I cannot make a report with so many fields, Access won't let me. It seems to cut off capability to make a report at around 25 fields.

First off, not so - it may not go WIDE enough to fit more fields on a page, all on one line, but the number of FIELDS you can have on a report is pretty much unlimited.

Second off, you've got worse problems that that. I fear your design is flawed (you appear to realize this too..) and while I'm sure someone here could work you up a solution to it, I'm certain that all you'd be doing is shoring up the sand castle until the next wave comes in.

Can you give us a slightly more detailed idea of what your application is designed to do, what your table structure is, and how the users view their data relationships? Perhaps we can pull this guy out of the fire yet.


Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com