Report Question

[DLN]

I’m trying to write a Report.
I’ve a query that selects 1 record from the table.
The Table has 20 or 30 fields all Yes\No.

I want the report to only have the fields that are Yes so it would look something like.


Report Name
Product “WATER”
----------------------
Oxygen
Hydrogen

End of report

[cori25]

I am a little confused as to what you are asking. But if you want to determine how to only bring in the field that says "Yes" in a query, all you need to do is in the criteria for the field with yes/no, write yes.

[Scott Price]

If I picture the table correctly, you have 20 to 30 columns with the names similar that what you listed for Water?

If so, this is a typical spreadsheet design that doesn't lend itself well to use in a relational database.

Using this as an assumption, you will likely have a query with Where criteria set to Yes for every field. This isn't going to work very well for you for several reasons, first of which is that it will return zero records, or very few records, since there are very few elements that will have ALL yes's!

One way to visualize a much better design for a relational database is to think of 'wide' and 'shallow' versus 'narrow' and 'deep'. A spreadsheet is typically wide, yet fairly shallow. In other words the number of columns is more comparable to the number of records. A well designed database table usually has far fewer columns, but far more records.

Here is a link to a good article on Database Normalisation and Table Structures that would be well worth reading.

Regards,
Scott

[NeoPa]

I am a little confused as to what you are asking. But if you want to determine how to only bring in the field that says "Yes" in a query, all you need to do is in the criteria for the field with yes/no, write yes.

This isn't quite right (though all honest attempts to help are appreciated).

A Scott says, you really need a normalised database to handle this easily.

Normalised     ==> Easy
Non-normalised ==> Almost impossible

[DLN]

I am a little confused as to what you are asking. But if you want to determine how to only bring in the field that says "Yes" in a query, all you need to do is in the criteria for the field with yes/no, write yes.

The table looks a little like this

Name       | Oxygen | Lead | Hydrogen | 
WATER    |Yes       | No    |Yes          |
SUGER    |No         |No     |No            |

So when I run a report I want only Oxygen and Hydrogen on the report for Water and I don’t want a blank spot for Lead.

[NeoPa]

I assume from your response that you haven't considered the normalisation issue (at least not yet). Let us know if you would like to progress on that path. I'm afraid your table design is just too wrong to work with otherwise.

[DLN]

I assume from your response that you haven't considered the normalisation issue (at least not yet). Let us know if you would like to progress on that path. I'm afraid your table design is just too wrong to work with otherwise.

This is what I did

Set rst1 = db.OpenRecordset(“tblCompounds”, dbOpenDynaset)
Set rst2 = db.OpenRecordset(“tblMyHold”, dbOpenDynaset)
 
rst1.FindFirst “CompoundName = Water”
With rst1
With rst2
Counter = 0
Do Until Counter = rst1.fields.count
    If rst1.fields(Counter).Value = True then
        rst2.AddNew
        !ElementName = rst.Fields(Counter).Name
        rst2.Update
    End if
Counter = Counter + 1
Loop
End With
End With

Then I used tblMyHold to populate the report.


Most of the tables in the db are related to tblCompounds and I'm using the word "Water" instead of the Key Number to make it easier to understand.

Thanks.

[NeoPa]

Are you saying that this is your solution and that you're happy now?

[DLN]

Are you saying that this is your solution and that you're happy now?

What do you suggest?


I don't have time to rewrite the whole db. I'm not going to break out a table that has 60+ Fields per record to 60 tables.

But I got the problem down to 15 lines of code that works.

[NeoPa]

I would suggest normalisation. However, if you were happy already then there would be little point. I wasn't sure from your earlier post whether or not you were done or still after some help.

However, normalisation would NOT involve 60+ tables, but maybe a single extra table to store the various attributes for each named item (Name in your example). They can be GROUPed to gether to produce the table you're currently working with, but they can also be processed individually, as required by your question.

It must of course, be your choice. I won't be in your office if anything goes belly-up in the process. I hope I can be available on here should you need, but you will be alone when your boss asks why things have gone horribly wrong if they do. It's how I'd do it though :)

[DLN]

I would suggest normalisation. However, if you were happy already then there would be little point. I wasn't sure from your earlier post whether or not you were done or still after some help.

However, normalisation would NOT involve 60+ tables, but maybe a single extra table to store the various attributes for each named item (Name in your example). They can be GROUPed to gether to produce the table you're currently working with, but they can also be processed individually, as required by your question.

It must of course, be your choice. I won't be in your office if anything goes belly-up in the process. I hope I can be available on here should you need, but you will be alone when your boss asks why things have gone horribly wrong if they do. It's how I'd do it though :)

Thank-you for your help.

I don't think you understand what I'm asking and that is my fault, I'm sorry if I didn't communicate it correctly.

[NeoPa]

Misunderstandings are hard to avoid sometimes when communicating via a web site. It sounds like you have a solution you're perfectly happy with anyway, so I'm happy to leave it here.

[MMcCarthy]

There is an easier solution to this question. Have a look at the table and Report in the attached file.

[MMcCarthy]

There is an easier solution to this question. Have a look at the table and Report in the attached file.

As NeoPa just pointed out to me I should explain what I did in the solution.

I used and IIf statement in an unbound textbox to check the values in the controls which are set to invisible and print the required value only if set to yes. I then set the "Can Shrink" property on the text boxes to yes which will check for any null values in the unbound textboxes. This should avoid any spacing issues.

[NeoPa]

The detail section is also set to Can Shrink = Yes.
Having gone through the question again and looked at this solution, I feel it's a good potential solution to your requirement. Let us know if this helps.

[DLN]

As NeoPa just pointed out to me I should explain what I did in the solution.

I used and IIf statement in an unbound textbox to check the values in the controls which are set to invisible and print the required value only if set to yes. I then set the "Can Shrink" property on the text boxes to yes which will check for any null values in the unbound textboxes. This should avoid any spacing issues.

Thank you, this is very helpful.

[DLN]

The detail section is also set to Can Shrink = Yes.
Having gone through the question again and looked at this solution, I feel it's a good potential solution to your requirement. Let us know if this helps.

Yes it does help.

I would like to thank all of you for you help.

[MMcCarthy]

Thank you, this is very helpful.

You're welcome.