Best way to get data from Access to display in a format reports can't handle

The requirements you are describing are very specific to your situation and will require custom code and/or queries (potentially quite complex) to manipulate and format your data, whether you develop the report in Access or export it to some other product.  The same general custom data manipulation would be required whether you keep it in Access or export the data.

Off hand it SOUNDS doable in Access - 

For your first requirement - there are a couple of methods to prevent vertical space from appearing.   One is the CanShrink/CanGrow properties of textboxes.  The catch for making CanShrink work is to have NOTHING (no labels, etc) on the same horizontal level as the textbox in question.

Another method, which I use for Address data which may or may not have an "Address 2" line, is to concatenate the fields representing the different parts of the address into a single string, with carriage returns separating the various lines, and displaying the concatenated string in a single textbox, rather than the individual components of the address in separate textboxes.  This can be accomplished through VBA code and/or queries.

< I left it blank, or in certain cases, it's a boolean field set to "false">

In your case, perhaps the data can be concatenated to fields that appear above it in the report.  You would also have to include custom logic to handle your checkboxes differently.

<if and only if at least one of them is true, there should be, in parentheses, a list of the fields that are true>

This again would require custom code to loop through these fields and create a concatenated list of the associated names, based on the checkbox/boolean field values.  The names could be defined in a separate table, or they could be defined in your code....

These are just general ideas...

With requirements this complex, developing a report IS going to be challenging whether in Access or exported, and you are not going to find a boxed solution to meet your needs. 

I don't think it's as difficult as mbizup makes it appear and I suspect it can be done with no VBA code.  Depending on your attitude, it may seem to be complicated, tedious or tricky though.

CanShrink is the key to squeezing out blank vertical space.  The important thing to remember is that a text box will shrink if it is invisible (using a little VBA) OR if the control's value is either Null or a ZLS (Zero Length String).

Note that there are two aspects to shrinking, first the text box must shrink and second the section needs to shrink.  When a text box shrinks, it will appear as if nothing happened if something else in that horizontal band of the section does not or can not shrink.  For example, if you have an unattached label in the same horizontal band as a shrunk text box, the section's band can not shrink.  This can be dealt with by either using VBA to make the label invisible or by changing the label to a text box with an expression like:

   =IIf(Nz([the shrunk text box],"") = "", Null, "the caption that was in the label")

For the weapon skill check box issue, you could use text box expressions like:

  txtSkill           =IIf(chkBow Or chkAxe or ..., skillfield, Null)

  txtweapons   =IIf(txtSkill Is Null, Null, "(" & (IIf(chkBow,"Bow", Null) + ", ") & (IIf(chkAxe,"Axe", Null) + ", ") & ... & ")")

Note the judicious use of + and & to concatenate (i.e. Null + "x" is Null while Null & "x" is x).

A different challenge that I did not see in your requirements is if you want side by side lists of skills.  This can be dealt with by using subreports based on queries that isolate the skills to their separate lists.  Then the two side by side subreports can grow and/or shrink independently.

Some good thoughts here. In particular, this bit, while kind of obvious once you think about it, had simply not occurred to me and fixes one part of the problem very nicely:

changing the label to a text box with an expression like:

   =IIf(Nz([the shrunk text box],"") = "", Null, "the caption that was in the label")

Regarding the txtweapons thing, how do you get rid of the last comma? In my case exporting to Word (which I'll need to do anyway) and then just using a straightforward replace would work [replace ", )" with ")", both without the quotes], but if you were working strictly within access what would you do about that?

Sorry, I meant to add that part after proof reading my reply, but I got distracted when the UPS guy came and the dogs went nuts.

Actually, I messed up and made it more complicated than it needed to be.  Try this:

   =IIf(txtSkill Is Null, Null, "(" & Mid(IIf(chkBow,", Bow", Null) & IIf(chkAxe,", Axe", Null) & ..., 3) & ")")

Note that it is easier to get rid of an extra comma at the start than at the end.

The root of your problems is, I'm sorry to say, a poor relational design.  By having multiple columns, each of which represents an attribute value you are doing what is known as 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1).  This requires that all data be stored as values at column positions in rows in tables, and in no other way.

Taking the example of Characters and Weapons, what you have here is a many-to-many relationship type between these two entity types.  The way this is modelled is by means of a table which resolves the many-to-many relationship type into two one-to-many relationship types.  So in broad outline the tables would be:

Characters
....CharacterID  (PK)
....Character

Weapons
....WeaponID  (PK)
....Weapon

and to model the relationship type:

WeaponSkills
....CharacterID  (FK)
....WeaponID  (FK)

Diagrammatically the model is:

Characters----<WeaponSkills>----Weapons

The problem of weapons not relevant to a character now disappears as there are only rows in WeaponSkills where there is relationship between Characters and Weapons.  So in a report thre are no irrelevant controls to shrink.

When it comes to a comma separated list this can be built by means of a VBA concatenation function.  You'll find one in Concat.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes a solution using the very efficient GetRows method of the ADO recordset object to concatenate values.  As the example it uses data from Northwind.

Allen Brown also provides a concatenation function at:

http://allenbrowne.com/func-concat.html

I think you've misunderstood something. There is absolutely no information about characters in the database; it consists entirely of abilities that they can learn. And the issue I'm asking about has absolutely nothing to do with "abilities not relevant to a character". There is no data in the headings, and the database is simple enough that relational design is almost entirely irrelevant.

The issue is how to format abilities so that only the information applicable to them appears. For example, there is a heading for "Duration", but an ability that just gives a static bonus that applies all the time does not have a duration.

It never fails; whenever I post here, I do usually (or at least, often enough for it to be worthwhile) get some helpful stuff, as I have here, but there's also always, without fail, at least one person who didn't read the original post, at least not carefully, who then proceeds to either go on and on about something completely irrelevant, or suggest something I've already tried and explained why it didn't work (and that's an inclusive "or").

Right, so the relationship type is with abilities, not characters.  As you've not provided any information about the logical model of the database I was second guessing, but it makes no difference to the central point that the logical model is a bad one.  You say 'There is a series of 12 related Boolean fields on the most relevant table...'  That is self evidently the encoding of data as column headings, which is fundamentally wrong in a relational database table; no ifs, no buts.

Far from not having read your original post, it is the fact that in doing so I was able immediately to identify the real problem, which is not a deficiency in the functionality of the report designer, but of the logical model.

Get the model right and the desired interface will be achievable with relative ease, get it wrong and you'll inevitably  end up facing problems.   With a bad design, if you torture the data long enough it might eventually confess, but evidence obtained through torture should always be regarded with a high degree of scepticism.

I see my role here, as I'm sure all the regular respondents do, to give the best advice I can; it goes with the territory.  If an OP doesn't like the answer I give, it's no skin off my nose.  I simply move on to the next question.  But you can rest assured that I shall continue to give what I see as the appropriate answer.  I've also considered the maxim 'when in a hole the first thing to do is stop digging' to be a good principle in relational database design as well as in life in general, but anyone who wishes to dig deeper is freely entitled to do so.  But not with any help from me.

I'm sure you'll be pleased to know that I shan't waste any more of your time or mine on this issue, and am discontinuing monitoring of this thread.

Right, so the relationship type is with abilities, not characters.  As you've not provided any information about the logical model of the database I was second guessing, but it makes no difference to the central point that the logical model is a bad one.  You say 'There is a series of 12 related Boolean fields on the most relevant table...'  That is self evidently the encoding of data as column headings, which is fundamentally wrong in a relational database table; no ifs, no buts.

Far from not having read your original post, it is the fact that in doing so I was able immediately to identify the real problem, which is not a deficiency in the functionality of the report designer, but of the logical model.

Get the model right and the desired interface will be achievable with relative ease, get it wrong and you'll inevitably  end up facing problems.   With a bad design, if you torture the data long enough it might eventually confess, but evidence obtained through torture should always be regarded with a high degree of scepticism.

I see my role here, as I'm sure all the regular respondents do, to give the best advice I can; it goes with the territory.  If an OP doesn't like the answer I give, it's no skin off my nose.  I simply move on to the next question.  But you can rest assured that I shall continue to give what I see as the appropriate answer.  I've also considered the maxim 'when in a hole the first thing to do is stop digging' to be a good principle in relational database design as well as in life in general, but anyone who wishes to dig deeper is freely entitled to do so.  But not with any help from me.

I'm sure you'll be pleased to know that I shan't waste any more of your time or mine on this issue, and am discontinuing monitoring of this thread.

I'm pretty sure we're talking past each other. You still appear to be making some incorrect assumption about what the model actually is, but it's impossible to tell what exactly. This is a very simple database, a glorified spreadsheet almost, there's nothing else that needs the weapon information besides these abilities. You clearly have a sounder understanding than I do of relational database design, but the relational concepts you're trying to apply aren't relevant here.

In any case, regardless of the data model, I'm still going to have the same issues with getting the output format the way it needs to be for publication. I don't see how moving part of the information to a different table and cross-referencing between them is going to do anything but make that more complicated.

What I ultimately need to do is print out, preferably on as few pages as possible, a report consisting of all 200-odd abilities, with the complete information on each and every one of them in a human-readable format. One of those pieces of information is which weapons the ability works with, but that, like several other fields, is applicable to some of those abilities and not others. Whether that information exists as boolean fields within the same table as the rest of the information, or a list of numbers or whatever that references a separate table, the complexities of getting that into a nice-looking, publishable format are the same if not greater. Or at least, if there's a way to simplify that with a different data model, I don't see what it is, even after going over your first post multiple times.

Seriously, does anyone understand Ken's point? I mean, I understand at least the basics of what he's saying, but I'm still scratching my head over how it's even remotely applicable to this specific situation.

Setting aside my umbrage at his snotty tone, the thing he's calling "self-evident", I still claim is not obvious, or even true. The more I think about it, the more certain I am that he seriously misunderstands both my question (at least the first half of it) and my attempt to explain the database upon which it's based. (The latter might be my fault, but then again, the other two people who've responded so far seemed to have no such difficulty.)

However, if someone does think he has a valid point, I'd appreciate their explaining it to me, preferably in as polite and low-jargon a way as possible. Note that there are two separate things that would have to be addressed here:

1. Is he correct about the data model? I seriously doubt it but I'm open to feedback to the contrary.
   
   • If you need more information to answer this, I'm happy to provide it, if you do what I strongly feel Ken should have done and ask, rather than just make assumptions and go in with guns blazing.
     
2. Even if the answer to question 1 is "yes", how exactly would fixing that contribute to solving the original record-formatting question? I'd appreciate specific, concrete steps, not vague generalities.
   

I'm somewhat open-minded about 1, but I'm dead certain the answer to 2 is "it wouldn't".

I don't think it's as difficult as mbizup makes it appear and I suspect it can be done with no VBA code.  Depending on your attitude, it may seem to be complicated, tedious or tricky though.

CanShrink is the key to squeezing out blank vertical space.  The important thing to remember is that a text box will shrink if it is invisible (using a little VBA) OR if the control's value is either Null or a ZLS (Zero Length String).

Note that there are two aspects to shrinking, first the text box must shrink and second the section needs to shrink.  When a text box shrinks, it will appear as if nothing happened if something else in that horizontal band of the section does not or can not shrink.  For example, if you have an unattached label in the same horizontal band as a shrunk text box, the section's band can not shrink.  This can be dealt with by either using VBA to make the label invisible or by changing the label to a text box with an expression like:

   =IIf(Nz([the shrunk text box],"") = "", Null, "the caption that was in the label")

For the weapon skill check box issue, you could use text box expressions like:

  txtSkill           =IIf(chkBow Or chkAxe or ..., skillfield, Null)

  txtweapons   =IIf(txtSkill Is Null, Null, "(" & (IIf(chkBow,"Bow", Null) + ", ") & (IIf(chkAxe,"Axe", Null) + ", ") & ... & ")")

Note the judicious use of + and & to concatenate (i.e. Null + "x" is Null while Null & "x" is x).

A different challenge that I did not see in your requirements is if you want side by side lists of skills.  This can be dealt with by using subreports based on queries that isolate the skills to their separate lists.  Then the two side by side subreports can grow and/or shrink independently.

Marshall, just thought I'd let you know I tried it with your suggestions and it's worked quite well. With your help (and some judicious search-and-replace work after exporting the end result), I've wrestled a report that was formerly 84 pages down to 40. Thank you very, very much.

Still mystified as to what Ken was on about (understand it in general, but drawing a complete blank as to how it's applicable to this project).