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
17 1321
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.
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 32,556
Expert Mod 16PB
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
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
Are you saying that this is your solution and that you're happy now?
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 32,556
Expert Mod 16PB
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 :)
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 32,556
Expert Mod 16PB
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.
There is an easier solution to this question. Have a look at the table and Report in the attached file.
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 32,556
Expert Mod 16PB
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.
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.
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.
Thank you, this is very helpful.
You're welcome.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stephan |
last post by:
Hi,
I'm using Visual Studio 2003 (C#) with the integrated
Crystal Report software and have the following question:
How can I assign a value (string) to an unbound (string)
field in Crystal...
|
by: Danny J. Lesandrini |
last post by:
First, this is _not_ a question about how to get Crystal Reports to run
on a client machine. I've got all the merge modules added to the project
and it's working fine. The question is about...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: Tom |
last post by:
This is related to an earlier post entitled "Text in Query/Numbers in
Report"
Brief recap - report has a query as a record source. The query shows
the correct data, the report translating the...
|
by: Danny J. Lesandrini |
last post by:
Didn't get any takers on this post this morning at dotnet.General, so I'm
reposting here.
First, this is _not_ a question about how to get Crystal Reports to run
on a client machine. I've got...
|
by: Kaur |
last post by:
Hi,
I am trying to create a crosstab type of report without using crosstab
query. The report captures several survey questions and for each survey
questions there are multiple choices that...
|
by: Stinky Pete |
last post by:
Evening,
I have a form that uses 5 pages (it's an electronic copy of the paper
version) we are going to use for manufacturing non conformances and
product deviations. In case your wondering,...
|
by: itmags |
last post by:
I need to create a couple of reports, invoices, credit reports, etc...
My question is: How/what is the best way to go about this using
VB2005 / SQL Server 2000
Crystal Reports? MS Reporting?...
|
by: lorirobn |
last post by:
Hi,
I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the...
|
by: JessicaZ |
last post by:
Hi everyone! I'm new here and have a question regarding Leban's report to pdf tool. I am working on a database where we are using this code to send a report out to pdf(duh) BUT what I need to do is...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |