 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2008Turning data into field heading, similar to a pivot table
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. 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.  SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > I'm using Access 2003. In one table is the personal info of the > residents, [quoted text clipped - 11 lines] > different feilds, you enroll them into the program by putting them in the > field wich has their dorm name. Very ugly. 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. > I don't understand you data. > [quoted text clipped - 21 lines] > > different feilds, you enroll them into the program by putting them in the > > field wich has their dorm name. Very ugly. 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 Signature-Larry- -- > 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 [quoted text clipped - 6 lines] > different feilds, you enroll them into the program by putting them in the > field wich has their dorm name. Very ugly. 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. > 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 [quoted text clipped - 26 lines] > in the > > field wich has their dorm name. Very ugly. 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 Signature-Larry- -- > 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 [quoted text clipped - 53 lines] > > in the > > > field wich has their dorm name. Very ugly. 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. > I don't follow your explanation. It gets ambiguous. > [quoted text clipped - 99 lines] > > > in the > > > > field wich has their dorm name. Very ugly. If crosstab won''t help you then I don't think that Access Reports are the tool for the job. HTH Signature-Larry- -- > 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 [quoted text clipped - 112 lines] > > > > in the > > > > > field wich has their dorm name. Very ugly. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.