Individual Record Reports

[ThebigCt]

In my database, I have a table full of student records. I need to be able to select one, create a report of only their information.

My table also has 35 data fields. So I would need to stack the information in columns in a stacked format.



Any tips?

[June7]

If you are trying to filter report to a specific student, parameter has be to provided somehow. There are several approaches to this.

1. Input parameter prompt in query
I never use this method. Cannot validate user input.

2. Input parameter by reference to controls on form.
I seldom use this method.

3. Parameters input into form control passed to form or report with WHERE CONDITION of DoCmd.OpenForm (or OpenReport). I use this most often and I use only VBA, not macros, but I think macros can also do this.
DoCmd.OpenReport "report name", , , "Employee_ID=" & Me.formcontrolname

Your data is not normalized. Will require a UNION query to rearrange the fields from horizontal to vertical. There is no designer or wizard for UNION, must type in the SQL View window of query designer. There is a limit of 50 lines in a UNION. An example:

SELECT ID, "Field1" As Category, Field1 As MyData FROM tablename
UNION SELECT ID, "Field2", Field2 FROM tablename
UNION SELECT ID, "Field3", Field2 FROM tablename
...
UNION SELECT ID, "Field35", Field35 FROM tablename;

[ThebigCt]

So this is what I have in the SQL View:I do understand I skipped field 4, I do not want this present in the Report.


SELECT ID, "Last Name", Field1 FROM Student
UNION SELECT ID, "First Name", Field2 FROM Student
UNION SELECT ID, "UCCS ID", Field3 FROM Student
UNION SELECT ID, "Mobile Phone", Field5 FROM Student
UNION SELECT ID, "Contract", Field6 FROM Student
UNION SELECT ID, "GPA", Field7 FROM Student
UNION SELECT ID, "SPTM 1000 Semester", Field8 FROM Student
UNION SELECT ID, "SPTM 1000 Year", Field9 FROM Student
UNION SELECT ID, "SPTM 2960 Semester", Field10 FROM Student
UNION SELECT ID, "SPTM 2960 Year", Field11 FROM Student
UNION SELECT ID, "MKTG 3000 Semester", Field12 FROM Student
UNION SELECT ID, "MKTG 3000 Year", Field13 FROM Student
UNION SELECT ID, "MGMT 3000 Semester", Field14 FROM Student
UNION SELECT ID, "MGMT 3000 Year", Field15 FROM Student
UNION SELECT ID, "SPTM 4250 Semester", Field16 FROM Student
UNION SELECT ID, "SPTM 4250 Year", Field17 FROM Student
UNION SELECT ID, "Graduation Semester", Field18 FROM Student
UNION SELECT ID, "Graduation Year", Field19 FROM Student
UNION SELECT ID, "Resume", Field20 FROM Student
UNION SELECT ID, "Field Experience Semester", Field21 FROM Student
UNION SELECT ID, "Field Experience Year", Field22 FROM Student
UNION SELECT ID, "Field Experience", Field23 FROM Student
UNION SELECT ID, "Internship 1 Semester", Field24 FROM Student
UNION SELECT ID, "Internship 1 Year", Field25 FROM Student
UNION SELECT ID, "Internship 1 Name", Field26 FROM Student
UNION SELECT ID, "Internship 1 Permission #", Field27 FROM Student
UNION SELECT ID, "Internship 1 Notes", Field28 FROM Student
UNION SELECT ID, "Internship 2 Semester", Field29 FROM Student
UNION SELECT ID, "Internship 2 Year", Field30 FROM Student
UNION SELECT ID, "Internship 2 Name", Field31 FROM Student
UNION SELECT ID, "Internship 2 Permission #", Field32 FROM Student
UNION SELECT ID, "Internship 2 Notes", Field33 FROM Student
UNION SELECT ID, "Alumni", Field34 FROM Student
UNION SELECT ID, "Notes", Field35 FROM Student

Now I get the error message: Enter Parameter Value: ID

What does that need to be switched to?

[June7]

ID is a generic field name I used for unique identifier field because I don't know the names of your table fields. Same for Field1, Field2, etc. Use your field names.

The first row needs alias field name. Otherwise the fieldname will be the field name from table. UCSSID, Last Name and First Name should not be on separate rows, they should be on every row. If unique ID is student ID, that's all you need in the query. Can then join this query to tables or queries to get other related data such as names and phone. The UCSSID is needed on each row so the records can be sorted/grouped by student.

SELECT Field1 As UCSSID, "SPTM 1000 Semester" As Category, Field8 As MyData FROM Student
UNION SELECT Field1, "SPTM 1000 Year", Field9 FROM Student
UNION SELECT Field1, "SPTM 2960 Semester", Field10 FROM Student


BTW, should avoid spaces, special characters, punctuation (underscore is exception) in field names. Better would be: Internship2PermissionNum

[ThebigCt]

I am pretty confused on your explanation. Could you take a look at it for me?

I need the report to look like SPTM Student Report.
I have the union there but it looks like that is wrong.

I just need to select a student by their last name and then their information populates the rest of the report.

[ThebigCt]

I made it this far:

SELECT Last_Name As LastName, "Last_Name" As Field, Last_Name As Data FROM Student
UNION SELECT Last_Name, "First_Name", First_Name FROM Student
UNION SELECT Last_Name, "UCCS_ID", UCCS_ID FROM Student
UNION SELECT Last_Name, "Mobile_Phone", Mobile_Phone FROM Student
UNION SELECT Last_Name, "Contract", Contract FROM Student
UNION SELECT Last_Name, "GPA", GPA FROM Student
UNION SELECT Last_Name, "SPTM_1000_Semester", SPTM_1000_Semester FROM Student
UNION SELECT Last_Name, "SPTM_1000_Year", SPTM_1000_Year FROM Student
UNION SELECT Last_Name, "SPTM_2960_Semester", SPTM_2960_Semester FROM Student
UNION SELECT Last_Name, "SPTM_2960_Year", SPTM_2960_Year FROM Student
UNION SELECT Last_Name, "MKTG_3000_Semester", MKTG_3000_Semester FROM Student
UNION SELECT Last_Name, "MKTG_3000_Year", MKTG_3000_Year FROM Student
UNION SELECT Last_Name, "MGMT_3300_Semester", MGMT_3300_Semester FROM Student
UNION SELECT Last_Name, "MGMT_3300_Year", MGMT_3300_Year FROM Student
UNION SELECT Last_Name, "SPTM_4250_Semester", SPTM_4250_Semester FROM Student
UNION SELECT Last_Name, "SPTM_4250_Year", SPTM_4250_Year FROM Student
UNION SELECT Last_Name, "Graduation_Semester", Graduation_Semester FROM Student
UNION SELECT Last_Name, "Graduation_Year", Graduation_Year FROM Student
UNION SELECT Last_Name, "Resume", Resume FROM Student
UNION SELECT Last_Name, "Field_Experience_Semester", Field_Experience_Semester FROM Student
UNION SELECT Last_Name, "Field_Experience_Year", Field_Experience_Year FROM Student
UNION SELECT Last_Name, "Field_Experience", Field_Experience FROM Student
UNION SELECT Last_Name, "Internship_1_Semester", Internship_1_Semester FROM Student
UNION SELECT Last_Name, "Internship_1_Year", Internship_1_Year FROM Student
UNION SELECT Last_Name, "Internship_1_Name", Internship_1_Name FROM Student
UNION SELECT Last_Name, "Internship_1_Permission_Num", Internship_1_Permission_Num FROM Student
UNION SELECT Last_Name, "Internship_1_Notes", Internship_1_Notes FROM Student
UNION SELECT Last_Name, "Internship_2_Semester", Internship_2_Semester FROM Student
UNION SELECT Last_Name, "Internship_2_Year", Internship_2_Year FROM Student
UNION SELECT Last_Name, "Internship_2_Name", Internship_2_Name FROM Student
UNION SELECT Last_Name, "Internship_2_Permission_Num", Internship_2_Permission_Num FROM Student
UNION SELECT Last_Name, "Internship_2_Notes", Internship_2_Notes FROM Student
UNION SELECT Last_Name, "Alumni", Alumni FROM Student
UNION SELECT Last_Name, "Notes", Notes FROM Student;


This brings up the information I need, but for every student. I need to be able to select only one student record and create a report for that.

[June7]

Structure still wrong. Need the UCCS_ID value on each line, not Name (more than one student can have same name, need the unique ID on each line to be able to filter for single student).

SELECT UCCS_ID, "LastName" As Category, Last_Name As [Data] FROM Student
UNION SELECT UCCS_ID, "Mobile_Phone", Mobile_Phone FROM Student
UNION SELECT UCCS_ID, "Contract", Contract FROM Student
UNION SELECT UCCS_ID, "GPA", GPA FROM Student
UNION SELECT UCCS_ID, "SPTM_1000_Semester", SPTM_1000_Semester FROM Student
UNION SELECT UCCS_ID, "SPTM_1000_Year", SPTM_1000_Year FROM Student
...

Then use this query as the data source to filter for a single student ID.

On review, don't think UNION needed at all. Your description of 'stacked format' had me jump to UNION. All you really have to do is arrange the textboxes on report vertically. If you wanted to do some kind of graphing or statistical aggregate calculations then UNION can often be necessary with non-normalized data. Only some of the fields in your table are not normalized data.