Turning data into field heading, similar to a pivot table

[MShek]

I'm using Access 2003. In one table is the personal info of the residents,
and one of those field is DORM (there are nine different ones). In another
table I have these residents enrolled in various programs using their ID#. I
would like the nine diff dorms to become field headings in a report and their
name appear under their dorm within the program they are enrolled. Then, if
their dorm changes then they would change "fields" automatically.

They way I have it now: in the enrollment form, each program has nine
different feilds, you enroll them into the program by putting them in the
field wich has their dorm name. Very ugly.

 

[John Spencer]

I don't understand you data.

Is the dorm an attribute of the resident - that is the resident "lives" in
a specific dorm? or
Is the dorm an attribute of the program - that is a program "is conducted"
in a specific dorm?

From what I read in your posting I believe the dorm is an attribute of the
resident and therefore should be stored in the Residents table.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Larry Daugherty]

Your data needs to be reorganized. There should be a separate table;
tblDorm that lists the dorm names. That's as you've given the
information. I'm involved in something for the homeless and the more
significant assignments are by bed names/numbers...

Anyway, in the table on which your form is based, there should be a
single field for the Dorm.

In the Report's "Sorting and Grouping" you can now sort and group on
the dorm's name and list everyone in that dorm separately from all
other dorms.

HTH

 

[MShek]

Dorm is an attribute of the resident. The report that prints my data prints
a single days activities per report. The report is grouped on each program
and it is this details section where I need the enrollment to appear. There
must be 9 columns of info, side by side, with the heading: Johnston, Lincoln,
.... and so on for all nine dorms.

What I would like to happen is that the program looks to the personal info
of each person enrolled in a particular program and them places them in the
correct column. That way if the move dorms then once it is changed in their
personal info then the report will move them to the proper column
automatically.

The way I have it now is there is a main enrollment form that has a
perticular programs info and then there is a subform where you actually
enroll them. The subform has common field that links the two forms (program
#), and it has an auto line field for a primary key, then it has 9 other
field, one for each dorm. When you click in one of these 9 fields a combo
box drops down and gives you their personal info, including what dorm they
live in, which is handy so you know you are enrolling them in the proper
column. The problem is that when dorm changes happen and are changed in
their personal info it does not move them in the enrollment for which means
they don't move in my report.

 

[MShek]

Larry thank you for your response, but I can't have the info organized like
you suggested since my boss does not want it that way. Read my reply below
to another individual and see if it makes more sence. Thanks!

Dorm is an attribute of the resident. The report that prints my data prints
a single days activities per report. The report is grouped on each program
and it is this details section where I need the enrollment to appear. There
must be 9 columns of info, side by side, with the heading: Johnston, Lincoln,
.... and so on for all nine dorms.

What I would like to happen is that the program looks to the personal info
of each person enrolled in a particular program and them places them in the
correct column. That way if the move dorms then once it is changed in their
personal info then the report will move them to the proper column
automatically.

The way I have it now is there is a main enrollment form that has a
perticular programs info and then there is a subform where you actually
enroll them. The subform has common field that links the two forms (program
#), and it has an auto line field for a primary key, then it has 9 other
field, one for each dorm. When you click in one of these 9 fields a combo
box drops down and gives you their personal info, including what dorm they
live in, which is handy so you know you are enrolling them in the proper
column. The problem is that when dorm changes happen and are changed in
their personal info it does not move them in the enrollment for which means
they don't move in my report.

 

[Larry Daugherty]

I don't follow your explanation. It gets ambiguous.

How data is organized in tables does not dictate how you must display
it in Forms. Unless you have gone out of your way to design a
linkage, the way data is displayed in Reports has nothing to do with
how it is displayed in Forms. Bear in mind that the data is always in
the Tables. The Form is simply a lens that passes over the recordset
to display one record at a time.

In Reports you design the organization of the data for display. It
should never be necessary to refer to data presented in a Form in
order to communicate the design of a Report.

Although I don't understand your explanation it sounds like you'd
benefit from a study of crosstab queries and display.

If you still have questions, please post back with a complete and
detailed explanation of what you are trying to accomplish.

HTH

 

[MShek]

I'm sorry for my poor explinations. For me it is much easier to do than to
explain since I am mainly self taught. I'll bend your ear one lat time and
hopefully I can be a little (lot) more clear.

What I need is a report that prints nine columns side-by-side. These nine
colums are the name of each of the nine dorms and they would appear in the
details section of a particular program that the data is grouped under. The
data would be drawn from two tables: the first being the resident personal
info which does contain what dorm they live in and the other would be the
program enrollment table.

I hope I haven't confused you more. Thanks for your time.

 

[Larry Daugherty]

If crosstab won''t help you then I don't think that Access Reports are
the tool for the job.

HTH