Dont show null fields in report

[senna]

HELLO ALL,

I HAVE A TABLE AND QUERY CONTAINING MANY FIELDS.


THESE FIELDS IN SOME CASES HAVE A VALUE, AND IN OTHER CASES DO NOT. EG:


PROJECT No. REFERENCE PART1 QUANTITY1 VALUE1 DESCRIPTION1 PART2 QUANTITY2 VALUE2 DESCRIPTION2

THE CRITERIA IS BASED ON THE PROJECT NUMBER. AND THE DETAIL BASED ON THE REFERENCE NUMBER

I NEED THE REPORT TO ONLY SHOW VALUES THAT ARE NOT NULL.
SO FOR MY PROJECT NUMBER I WILL HAVE VARIOUS REFERENCE NUMBERS, WHICH CONTAIN 0 - 20 PARTS (DETAIL).
EACH OF THESE PARTS HAVE A PART NUMBER, QUANTITY, VALUE AND DESCRIPTION.
MY TABLE CONTAINS ALL THE FIELDS FOR THE PARTS THAT MAY BE RELATED TO THE REFERENCE NUMBER.
TABLE NAMES ARE AS PER TOP EXAMPLE, EXCEPT CONTINUE UP TO 20.

IS THERE ANY WAY TO DISPLAY IN THE REPORT ONLY THE VALUES THAT ARENT ZERO?

I TRIED >0 IN THE QTY FIELDS (0-20) BUT IT ONLY DISPLAYS THE 1 REFERENCE WHICH HAS 20 PARTS ALLOCATED TO IT, ALL THE REST DO NOT SHOW UP BECAUSE THEY HAVE LESS THAN 20 PARTS ALLOCATED.

I AM STUCKKKKKKK.

[pbaldy]

For starters, your caps lock key appears to be broken.

Your design is flawed. You should not have the repetitive fields in the table. The normalized design would have a related table with fields for PART, QUANTITY, VALUE, DESCRIPTION. If a project had 3 parts, there would be 3 records in this table. You should read up on normalization, here for instance:

http://r937.com/relational.html

[senna]

Thanks Paul. Caps lock is fine, cant believe people get offended by CAPS. It just happened to be on so I kept typing.
Anyway, the design is based on data i receive in excel format. The reason behind this post is to avoid the previously monotonous and time consuming task of sorting the data into the simple 4-5 columns.
The data i receive is over 30 columns wide, hence why made fields for each of these values even though they are the same TYPE of value.

I want to copy and paste the data into the table as it is...All I want to know, is if it is possible to have my desired outcome through either modifying the report or query to suit. rearanging the table would simply put me back to step 1 and having to manipulate data which can lead to errors occuring.

Appreciate the reply all the same.

[pbaldy]

I would still work with properly normalized data. You could pull the data from your Excel format into normalized format with a fairly simple UNION query, either to store it properly or at least for reporting. In any case, you might be able to use the Can Shrink/Can Grow properties of the report textboxes to get the look you're after.

[ssanfu]

Thanks Paul. Caps lock is fine, cant believe people get offended by CAPS. <snip>

Senna,

I'm sure Paul knew that your caps lock was fine. But it was a polite way of saying "Please don't shout". After all, he *did* add a wink...
Typing in all caps is akin to shouting. How would you like to have someone ask for help by SHOUTING at you??

OK, back to your problem.. I also think you would be better off in the long run normalizing your table structure. You will probably spend more time fixing forms/reports in the future than fixing the structure now. If the Union query solution doesn't work for you, you could import the data using code to read in the data (from Excel SS or a csv file) and put it in the correct fields.

(I had data coming in that was in an Excel SS: 32 columns wide; using code, I broke it up and put it in 7 related tables.)


Because all of the parts as in one record, if one part has a quantity, then you get all parts. You might use code to loop thru each record, checking each part quantity, writing it to a temp table if QTY>0, then using the temp table for the report (one record per part).

I don't know of a way just using a query.. Sorry