Null Values in reports

  • Thread starter Thread starter Grimwadec
  • Start date Start date
G

Grimwadec

I have a table with 10 fields named sizes 1 through 10. I don't want my
report based on the table to display the field label (or leave a space in the
report for that matter) if the data for a particular size is NUll. Is it
possible?
 
IMO, your table structure is wrong. It looks to me like you have created a
spreadsheet, not a relational database. The size should be stored in a field
as a value. Each size should create a unique record in your table.

If you can't do this, I would create a normalizing union query and base your
report on this query. It would help to know the primary key field and name of
your table.
 
Thanks
Oops, was trying to keep it simple but in fact conveyed a totally incorrect
scenario...In fact I have tblJobs with a Primary Key field "ID" that has
i.a., 17 fields "Size4" through "Size5XL". Typically only say 6 of the fields
will have a value in any given record and when I produce a report for an
individual record I want to achieve my originally stated objective...and what
do you mean by a "normalizing' ? union query

Grimwadec
 
You conveyed your table structure quite well. It confirms my opinion that
your tables are not normalized. There are tons of web resources regarding
"normalizing databases".

You can create a normalizing union query with SQL like:
SELECT ID, "4" as Size, [Size4] as Qty
FROM tblJobs
WHERE [Size4] Is Not Null
UNION ALL
SELECT ID, "5", [Size5]
FROM tblJobs
WHERE [Size5] Is Not Null
--- etc ----
UNION ALL
SELECT ID, "5XL", [Size5XL]
FROM tblJobs
WHERE [Size5XL] Is Not Null;

You can then create a report based on the union query. The quantities will
not be displayed where there are not records.
 
tried to send you a longer reply but system crashed, suffice to say your
reply meets my needs and thank you
--
Grimwadec


Duane Hookom said:
You conveyed your table structure quite well. It confirms my opinion that
your tables are not normalized. There are tons of web resources regarding
"normalizing databases".

You can create a normalizing union query with SQL like:
SELECT ID, "4" as Size, [Size4] as Qty
FROM tblJobs
WHERE [Size4] Is Not Null
UNION ALL
SELECT ID, "5", [Size5]
FROM tblJobs
WHERE [Size5] Is Not Null
--- etc ----
UNION ALL
SELECT ID, "5XL", [Size5XL]
FROM tblJobs
WHERE [Size5XL] Is Not Null;

You can then create a report based on the union query. The quantities will
not be displayed where there are not records.
--
Duane Hookom
Microsoft Access MVP


Grimwadec said:
Thanks
Oops, was trying to keep it simple but in fact conveyed a totally incorrect
scenario...In fact I have tblJobs with a Primary Key field "ID" that has
i.a., 17 fields "Size4" through "Size5XL". Typically only say 6 of the fields
will have a value in any given record and when I produce a report for an
individual record I want to achieve my originally stated objective...and what
do you mean by a "normalizing' ? union query

Grimwadec
Click to expand...
Click to expand...
 
Back
Top