March 3, 2008 at 11:37 am
I am attempting to pull data from several tables, but I want that data that I collect to be based upon the values of other data. For example, I'm pulling 8 fields from Table A, and if the 8th field is not null, then I want to pull 5 fields from Table B. This isn't something that I've managed to do correctly yet.
What I have now is a series of Join statements to collect the data for table A, and there is a LEFT Join with Table B. However, this leaves me with NULL values for the table B fields that I collect, which does not work well with the reports that I am generating (Active Reports leaves blank space for the fields). I need to be able to not return these cells at all, if possible. I haven't been able to make the report ignore them when they are NULL (this is probably because there could be multiple lines within the section, and it can't hide individual lines).
Have I explained this well enough, or have I left everyone confused?
March 3, 2008 at 11:43 am
Does your report display the data in columns, several records at a time, or is each report page a single record?
If it was easy, everybody would be doing it!;)
March 3, 2008 at 11:45 am
I'm pretty sure there isn't a way to do that in a single recordset.
You might need to set up some conditional formatting in the reporting application. I know I used to be able to do that in Crystal Reports and Access (been a while for either of them).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 3, 2008 at 11:46 am
The report displays several records at a time, depending on how many are returned. The actual report is formatted like the following:
OrderNumber
Detail1
Detail2
SubDetail1
Detail3
SubDetail1
SubDetail2
....
March 3, 2008 at 11:48 am
That's what I was afraid of. I'll keep working on it, but I'm afraid that you might be right. If there is a way, it's beyond my abilities for the moment.
March 3, 2008 at 12:11 pm
Mark Harley (3/3/2008)
The report displays several records at a time, depending on how many are returned. The actual report is formatted like the following:OrderNumber
Detail1
Detail2
SubDetail1
Detail3
SubDetail1
SubDetail2
....
So in essence, you are displaying one record at a time as opposed to rows of data going across the page, correct? Then you get the next record, with all of its details (kinda like a block format). If I'm following correctly, in some cases, for instance, Detail3 may be non-existant, and you don't want those fields to show up with no data.
Most reporting tools will allow you to suppress a field conditionally, such as when it is null (or any other value of your choosing), or you can convert it (when null, make it blank). If the 'extra' data is in a different section, some tools allow you to suppress the entire section. By doing this, your query with the LEFT JOIN will suffice and you control the display at the report.
If it was easy, everybody would be doing it!;)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply