Hide blank columns

[Robbie Doo]

I have a field called "Service Codes" from 1 to 99. Not all the codes are
being used in a month. Is it possible to show in a monthly report only the
codes that have been used?

 

[Duane Hookom]

Very possible depending on your report's record source. Do you have 99 fields
for these? I certainly hope not. If you do, you need to read up on
normalization.

 

[Robbie Doo]

No Duane, it's a record with 99 possible answers (dropdowns), which make the
columns. I'm assuming if I put a text box on the report to make a column then
with a vb code it will make columns depending on the answers.

 

[Larry Linson]

I have a field called "Service Codes" from 1 to 99. Not all the codes are
being used in a month. Is it possible to show in a monthly report only the
codes that have been used?

It would appear from what you say here that you have a record with fields
ServiceCode01, ServiceCode02, ServiceCode03 . . . ServiceCode99. That
certainly would appear to be a violation of relational database design
principles. But then, I am confused by your answer to Duane. That might
mean something else. My guess is that you are trying to use Access (a
database) as though it were Excel or Lotus 1-2-3 (a spreadsheet), but that
is only a guess from what you've said -- not a good definition of what
you're doing. I think you are going to have to give us some details about
what data you have, how you have it laid out, and what you are trying to
accomplish if any of us are to be able to help you.

Larry Linson
Microsoft Office Access MVP

 

[Duane Hookom]

I assume now that this is a single field. If so, it will create a single
control on your report and won't display values that aren't stored in the
field.

 

[Robbie Doo]

Well, these services do not apply to every department. We use the drop down
on certain orders only. This means there will be a lot of blanks in that
field. When making the query I need to show all the departments with their
service codes across something like this:

Dept Svc1 Svc 2 .... Svc 99
01 3 2
02 2
03 1 1 1

The report should show columns with data only. I hope this was clear enough
else let me know.

 

[Duane Hookom]

You shouldn't be using repeating common fields. Each service for each
department should create a new record in a related table.

 

[Robbie Doo]

Yes, it does create a new record for each department in table that is
connected to the Main table by a unique number called "ReviewNo". All those
services are connected to the main table where the deparments are by this
unique field and each has it's own record in that table.

 

[Duane Hookom]

I still think your table structure is not normalized. Can you provide your
table structure? Do you have field names with consecutive numbers?