Changing the display possible?

[ayupchap]

I have created a relational database where there is a student table and a strands table. The strands table lists the results for each strand a student gets (D,E or S). It so far looks like this:

First Name	Second Name	Number- Counting and number value	Number- Add, sub, mult, div and est	Shape, Space and Measure
Student FN1	Student LN1	E	D	E
Student FN2	Student LN2	E	D	E
Student FN3	Student LN3	D	S	D
Student FN4	Student LN4	D	S	D
Student FN5	Student LN5	S	S	D
Student FN6	Student LN6	D	D	S
Student FN7	Student LN7	E	D	S
Student FN8	Student LN8	E	E	S
Student FN9	Student LN9	E	D	E

Now is there anyway in a report to make the strands show up on the left ans the names across the top? even if its just the first name? This would be amazing if I could (FN means first name and LN means last name). I have no idea if this is possible or how to do this.

[Micron]

In report design, create a group by student; place the name controls in the group. Place the strands in the detail section of the report.
You can play with the position of labels, putting them in the report header or group header, but I would think not in the detail section.

[ayupchap]

Oh wow. So that would make it look like this:

	Student FN1	Student FN2	Student FN3	Student FN4	Student FN5	Student FN6	Student FN7	Student FN8	Student FN9
Number- Counting and number value	E	E	D	D	S	D	E	E	E
Number- Add, sub, mult, div and est	D	D	S	S	S	D	D	E	D
Shape, Space and Measure	E	E	D	D	D	S	S	S

I'm having trouble making it look like this which is what I want. I created a student group but what do you mean by control? Sorry not sure about this type of customization. Thanks so much for your help and expertise on thsi

[ayupchap]

I just can't seem to get it to work. here is what I got, I must be missing something right? I can not for the life of me work out how to get it to look like my last post.

[Micron]

No, it would not. I took your layout to mean that the data was stored in the proper row/field configuration as befits a database and your request to mean the report should more or less fit the norm. The second row I took to be how data is stored (the field names are abbreviated to save space).

First Name	Second Name	Number- Count&value	Number- ASMDE	Shape, S&M
Student FN1	Student LN1	E	D	E

You didn't say you wanted the rows to be transposed to columns.


In the image, AssetID and SystemID are textbox controls. AssetID is placed in the detail section; SystemID in the SystemID header. The value in the header would show once for each SystemID for which there are AssetID detail records. I was saying that in your case, names are analogous to SystemID and 'strands' are analogous to AssetID, one control for each of your strand headings, side by side (like AssetID, Asset and whatever comes to the right that is not shown).

With all due respect, if you don't know what a control is, I think you are severely disadvantaged for solving this issue. Report design would not be first night in Access Level 1, if you get my drift. I'm wondering if you need to step back and review some tutorials on report design (and maybe query too, since that is the foundation of a good report). If Orange sees this post, perhaps he can weigh in - he seems to have a plethora of links for good tutorials at the ready. If I take your sample info literally, you are already exhibiting poor naming practices (using dashes, spaces, commas, reserved words...). This would definitely indicate a need to brush up on design, normalization and naming conventions.

[Micron]

I just can't seem to get it to work. here is what I got, I must be missing something right? I can not for the life of me work out how to get it to look like my last post.

This appeared after my reply. You should be able to figure out that I'm saying this is not how it should be laid out. If your data looks like this too, it means you have to add a table field each time there is a new student added, or worse, remove a field when they're gone. This is just wrong, wrong, wrong. Nor should you want your report to resemble that sort of layout, mostly for the same reasons. Someone might help you achieve what you want, but sorry, it can't be me.

[ayupchap]

OK Thanks so much for all your help here. I am going to try this out. Really appreciate the time you have put into this.

[ayupchap]

I hear what you are saying. I have made databases in the past but in a different system, access is something i am using to try and help out a friend. Although there are great similarities in all database systems there are ways things work and names of its parts which are different so it is throwing me a little. I should add I would never normally make labels like this at all. The end users are teachers and these are the strand titles they wanted to identify with so they could enter grades. I couldn't think of another way unless I gave them a unique code which they would then have to reference, its what they wanted and a cheap fix to be honest, not sure how else to display the title in an access form otherwise although I could in a report. The term control I just researched and see what it means now, its a term I have not used before when making something, I have used primarily salesforce. You are right however with regards to doing some homework on access report creation and query making. I have 4 related tables so far and I did normalize but I'm not so good at that and I was slightly restricted by the import files I have to use which is how the system is to be updated for the student table details.

Again I appreciate your help and time. Any good known resources would be much appreciated.

[ssanfu]

I have to agree with Micron.
Your table design is like an Excel spreadsheet, which is not the design of an relational database.


I would expect the tables to look more like:

tblStudents
StudentID_PK AutoNumber
FirstName Text
LastName Text


tblStrandDesc
StrandDescID_PK AutoNumber
StrandDesc Text - desc of strand (ex: "Number- Counting and number value")


tblStrandResults
StrandResultID_PK AutoNumber
StudentID_FK Number - link to tblStudents
StrandID_FK Number - link to tblStrand
Strand Text - value of Strand (D,E or S)






but....... I know nothing about your project - you might have other tables and requirements.........

[Micron]

The design principles wrt naming or data structure (normalization) of which I speak transcend all formats. If the inputs don't jive, it's incumbent on you to either convince others to adapt or you have to bring over the data in a way that befits the need, or make that transition once you have it as data that's laid out in spreadsheet fashion. One way is to link workbooks as tables, then rearrange data in new tables so that it suits the database norm. Regarding label names, that's not so much an issue. My comment was about field names mostly. The caption (proper term) of a label can be anything - even like what you've shown. Under no circumstances should the name of the control (a label is a control too) resemble what you have.

If you want help with your relationships or other factors not related to this post, I'm sure that we'd be happy to help. It's common to post a picture of your relationships window. Just start a new thread that defines the need or issue. However, be clear and concise when describing what you have, need, and if applicable, have tried. A good narrative on the business that the db is intended to support is often a big help. In the meantime, you might want to review these topics:

autonumbers:
http://access.mvps.org/access/general/gen0025.htm
http://www.utteraccess.com/wiki/index.php/Autonumbers
primary keys (and other good topics listed on left nav pane): http://www.fmsinc.com/free/newtips/primarykey.asp
naming:
http://www.access-programmers.co.uk/...d.php?t=225837
http://access.mvps.org/access/general/gen0012.htm
http://allenbrowne.com/AppIssueBadWord.html

[ayupchap]

Wonderful, Thanks so much Micron and also ssanfu for all your help. I'm going to look into all of this now. I absolutely see where you are coming from. No doubt you will hear from me again at some point in the near future.

[Micron]

PS ayupchap; if you don't already know, what ssanfu did to suggest a schema takes some time. More than this lazy guy would usually do.
Nice to have that sort of dedication in this forum.