How to change Column Headings?

[RoMi]

Sorry, I couldn't find anything similar in the previous questions.

I need a table that is very similar to a crosstab query: in the first
column named Titles there are listed hundreds of the books related to the
different categories like electronics, medicine, philosophy etc. From the
second to the 999th column there are ColumnHeadings like the student names.
In the table fields one must enter Yes or No - was the book read or not.

In fact, I need a printed Report based on this table. To reduce the size of
the printouts I have to filter the books and the students - assuming that
the students of electronics have to read some books related to electronics
only.

And - how to enter and/or replace the Column Headings by a data from a table
with thousands of names?
One table tblBooks keeps Titles and Category. The other table tblStudents
have StudentName and a Category which can be related to the
tblBooks.Category.

Thank you with a hope that this could be useful to other members of this
group.

 

[Jeff Boyce]

I may not be understanding your description (in fact, I'm sure I'm not).
Access tables can only have 255 (?256) columns, but rarely are more than 20
or 30 seen in well-normalized designs.
First, I don't understand what you're describing by "second to the 999th
column".

And your description sounds like the column headings contain names ("like
the student names."). Again, with a well-normalized data structure, you
would not use repeating groups, embedding data like PersonName or
MonthOfYear in ColumnNames.

From your description, you already have a table for Persons and a table for
Books. A design that would allow you to record a person, a book, and the
fact that the person had read the book, could take as few as two columns in
a "relation" or "junction" table. One column would hold the PersonID (taken
from a table of Persons), and one column holds the BookID (taken from a
table of Books). If it was important to know when the book was read by the
person, you could add a third field for DateRead.

With a design like this, you can look up all the persons reading "Crime and
Punishment", or all the books read by "A. Chekov", or all the books read
during the month of November, 2003.

Good luck

Jeff Boyce
<Access MVP>

 

[Duane Hookom]

You might want to look at the solution for crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. You can create your
report for as many columns as fit your page. The crosstab will handle
virtually any number of columns.

 

[RoMi]

And your description sounds like the column headings contain names ("like
the student names."). Again, with a well-normalized data structure, you
would not use repeating groups, embedding data like PersonName or
MonthOfYear in ColumnNames.

Jeff Boyce
<Access MVP>

I was afraid of misunderstandings because of my poor English and next,
because of an unusual task.



Let's imagine a big sheet of paper on a wall in some department. In the
leftmost column there are listed the titles of some documents. On the top
there are written employees names (horizontally). This is a scheduler named
Training list. A secretary just has to put a cross by a hand - which
employee has to pass adequate training. Departments have up to 50-60
employees (totally up to 500) and the number of the training items varies
from 20 to 400. Some people come, some people go away. It must be easy to
validate the data and to print new "Reports". This is what does want a user.





What I should like to offer is the simple data tracking and printing.





A crosstab query is very near to my wishes but I don't know how to avoid a
"value" (count). At lest all zeros in the fields could satisfy me. Next I
should like to filter the Titles and the EmployeeNames depending on
Department rules.



If I can get just a simple table with a possibility to automatically replace
EmployeeName as a Column Heading, it would be great.



Where am I "limited" in looking for a solution? Is there any simple
solution?



Thank you.

 

[RoMi]

You might want to look at the solution for crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. You can create your
report for as many columns as fit your page. The crosstab will handle
virtually any number of columns.

Thank you on a given link but my problem is still opened.

Is it possible to fool the data field in a crosstab and get empty fields
(Null)? How can I use a code to filter leftmost column and the
ColumnHeadings? The most important thing is printing some kind of report
based on this table/query. The number of columns will exceed the A4 or a
letter format but the papers may be put together on a desk or a wall.

Is there more elegant way to do such job?

Sorry for more questions but it seems to me, it is all about the same
problem.


Thank you.

 

[Duane Hookom]

If you have up to 999 columns then I have never seen a better solution than
the one provided in the sample download.

I have no idea what you mean by "fool the data field in a crosstab and get
empty fields (Null)".

 

[RoMi]

As for now I use a crosstab query exclusively as a template for printing to
get something like a scheduler: vertically - DocumentTitle, horizontally -
EmployeeName (ColumnHeadings). The field area is fulfilled by a hand on a
paper. One must put 'x' what does mean 'yes' (an employee must study
corresponding documents). Because of crosstab query which gives values in
the field area I didn't know how to remove them not to be seen on the report
(printed paper). In the meantime I found out that I can do it if use an
Expression like 'Expr1:" " ' (an empty space or just o dot to simulate a
coordinate system). For now it works great: I can filter DocumentTitle and
EmployeeName depending of the department's type. In this case I can reduce
the number of columns (I need up to 80 columns only and the number of rows
is not critical, too).



The problem: a need for a frequent and accurate changes on a list because
people come and go away. It is very unpleasant job to adjust the changes on
a report. This job can not be left to a user himself. For example, if one
new employee is added it is necessary to adjust his place (data box) on a
report what takes time and ask for patience. Therefore, I think that a
normal table would be much more convenient but only if I can put the
EmployeeName instead of the ColumnHeadings. In that case I could use the
fields for direct data entering (like Yes/No) so I could keep tracking in a
computer without a need for additional handwriting on a wallpaper. I could
still filter the DocumentTitle and what I am asking all the time for is that
I don't know how to use a code to enter the EmployeeName from another table
into ColumnHeading's place. For now I could do it in a design mode to define
field name and its data type but this is not practical if you need to do it
frequently.



So many words ... so little code.



Is there more elegant way to do such job?

Thank you.

 

[Jeff Boyce]

RoMi

I'll re-enter this thread to offer the following...

I may still not understand your situation. What I believe is that you have
been describing "how" you wish to do something, more than "what" you wish to
accomplish. I may have this impression because of the words you've chosen.
For example, "crosstab query" has a specific meaning in Access, but I'm not
certain if you are using that same meaning.

In your most recent post, you describe "a need for a frequent and accurate
changes on a list because people come and go away. It is very unpleasant job
to adjust the changes on a report." In a well-designed Access application,
you should have NO need to be adjusting/editing/modifying your report.

You also mentioned "would be much more convenient but only if I can put the
EmployeeName instead of the ColumnHeadings." Again, you should not need to
be editing the table structure once you've set it, if your design is
well-normalized. I can see that you understand this when you say "For now I
could do it in a design mode to define field name and its data type but this
is not practical if you need to do it frequently." This IS my point -- you
should not have to do it at all, after setting the structure.

And you mention "In that case I could use the fields for direct data
entering (like Yes/No) so I could keep tracking in a computer without a need
for additional handwriting on a wallpaper." This sounds like you are NOT
able to track this information in your Access application.

Is there more elegant way to do such job?

I will try to reiterate the structure I mentioned earlier in this thread.
Please accept my apologies if I do not clearly understand your situation...

tlkpSubjectArea (the "categories" you need to categorize your Titles)
SubjectAreaID (Autonumber, primary key)
SubjectArea (descriptive -- e.g., "electronics")

tlkpDepartment (how you categorize the course of studies taken by a student)
DepartmentID (Autonumber, primary key)
Department (descriptive -- e.g., "Education")

tblTitle ("hundreds of the books related to the different categories ...")
TitleID (The primary key, a unique identifier, either ISBN# or
Autonumber)
Title (the book's title)
SubjectAreaID (the "category"; treated as a foreign key, from
tlkpSubjectArea)

tblStudent
StudentID (Primary key, Autonumber or ...?)
StudentName
DepartmentID (which department is the student in -- treated as a foreign
key)

trelTitleRead
TitleReadID (Primary key, Autonumber)
StudentID (treated as a foreign key; comes from tblStudent)
TitleID (treated as a foreign key; comes from tblTitle)
?DateRead (if you need to track when a student read a title)

With your data organized in this manner, and by keeping only one fact per
field, you should be able to answer questions similar to the following, and
to create reports for each:

Which books has a given student read? (and what where the books' SubjectAreas?)
Which books have been read by students in the "Engineering" department?
Which books have NOT been read?

Please note that it might be impractical to list 100's of book titles and
use checkboxes to indicate whether/not each has been read -- and it would be
VERY difficult to be able to comprehend. Unless I've totally misunderstood,
I suspect it is much more likely that a given student would read only a
percentage of the total number of books listed. If true, it seems like it
would be confusing to show ALL Titles, with many of them not checked for a
given student.

There are other report layouts/designs that can make it easier to
comprehend. For example,

Department1
Student1
SubjectArea1
Title1
Title2
Title3
SubjectArea2
Title4

Student2
SubjectArea3
Title5
Title6

Good luck! (and my apologies if this does not help)

Jeff Boyce
<Access MVP>