Null Values in reports

[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?

 

[Duane Hookom]

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.

 

[Grimwadec]

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

 

[Duane Hookom]

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.

 

[Grimwadec]

tried to send you a longer reply but system crashed, suffice to say your
reply meets my needs and thank you
--
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.
--
Duane Hookom
Microsoft Access MVP

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