Report confusion

Question

I'm trying to build a report using the query below. When I try to run the report, it gives me a parameter window asking for "student." If I just say OK without putting anything in the parameter window, it runs the report with all the requested information in the query, except that it shows that information as listed under only one student. Can anyone tell from this, whether the problem is with my query, or with the report design? The design was copied from another report that does run correctly. It contains the "name" field under a group header, and the rest of the information under the detail. The is no field called "student" so I'm mystified as to why it's asking for that.

SELECT [Stu_first] & " " & [Stu_MI] & " " & [Stu_last] AS Name,
[Par_Gurd_first] & " " & [Par_Gurd_MI] & " " & [Par_Gurd_last] AS ["Parent/Guardian"],
 Address1, Address2, Town, State, Zip, Phone1, Phone2, Phone3
FROM Students;

Answer 1

Check your sorting and grouping - there might be something there.  Also look at each one of your controls.  Maybe you have a control on the report that has student as its data source.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 2

Thanks, SuzyQ

I checked the controls— no "student" as data source.

---

—nick

Answer 3

There is no Order in the query. I can't find any properties in the report design for sorting or grouping, so I'm not sure what you referred to there.

---

—nick

Answer 4

sorting and grouping on the report.  You can right click on the details group header and then select view sorting and grouping

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 5

Thanks, SuzyQ

I can't see anything wrong there. The report design window looks like this ("student, below, is just a label)". . .

And the sort/group windo looks like this:

Can you see any problems from what you can see here?

---

—nick

Answer 6

I looks ok.  I suggest that you open a new blank report and copy these items into that new report to see if that solves your problem.  If it does, then somewhere in the past you had something indicated as "student", but the report somehow held onto the reference.  I've see this happen before and bringing it into a new report should solve the issue.  After you test the new report you can delete (or rename) the old one and rename the new report so that it is the name you want (the old report's name).  Make sure you also copy over the record source for the report.

EDITED - I just saw something... look at your =[Student] header - you'll want to change that to name.  click on "Group on expression" and change that to the name field.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 7

Also check the Filter and Order By properties of the report (in the Data tab of the Property Sheet): anything there?

---

Regards, Hans Vogelaar

Answer 8

O wait - you do have a =[Student] header section - it's the expression in Group on expression in the Sorting & Grouping window!

---

Regards, Hans Vogelaar

Answer 9

WOw!  thanks!  And to think— I used the existing report as a template because I thought it would be easy and save lay-out time!

Thanks again, to you both

---

—nick

Answer 10

There is no need for a group header at all here as the report is based on a single table, so there will only be one instance of each student.  I assume the grouping is simply to order the report by student name.  Normally this would be alphabetically by last name then first name, so the first thing to do is amend the query to:

SELECT [Stu_last], [Stu_first], [Stu_first] & " " & [Stu_MI] & " " & [Stu_last] AS Name,
[Par_Gurd_first] & " " & [Par_Gurd_MI] & " " & [Par_Gurd_last] AS ["Parent/Guardian"],
 Address1, Address2, Town, State, Zip, Phone1, Phone2, Phone3
FROM Students;

Remove the existing Student group level completely from the report and put the Name control in the detail section above the Parent/Guardian etc controls, which you'll have to move down to make room.

Then order the report firstly on [Stu_last] and secondly on [Stu_first].

Incidentally, your table is badly non-normalized. Having multiple columns for the phone numbers means that it is not in First Normal Form, which requires each row in a table to have only one value of each attribute.  The phone numbers should be in a separate related table, with a foreign key column referencing the key of Students and a single PhoneNumber column.  The table will which will have as few or as many rows as required per student.  In the report you can (a) return the phone numbers in a subreport, (b) Join the Students table to the PhoneNumbers table in the report's query and group the report, or (c) by means of a function concatenate the phone numbers into a single control with each separated by a carriage return/line feed; for examples of this technique see the Concat.zip demo file in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Having Town and State columns in the table also introduces redundancy as State is determined by Town, so the former is not solely determined by the whole of the table's primary key.  The table should have a TownID column referencing the distinct numeric key of a Towns table (the town name is not a suitable key as town names can legitimately be duplicated).  The Towns table will in turn have a foreign key referencing the primary key of a States table.  The reports query would then join the tables to return the town and state.  As it stands the table is open to the risk of update anomalies as there is nothing to stop the same town being entered in two or more different states in separate rows in the table.

Finally Name should not be used as a column name in the query as it is the name of a built in property, and as a 'reserved word' should be avoided for object names.  Use something like FullName.

---

Ken Sheridan, Stafford, England

Answer 11

Ken, I apologize for not having responded before. The immediate problem had been solved, and so I somehow missed this comment.

Thanks for your pointers on normalization. This is an area that has continued to confuse me. Your thoughts about the phone numbers helps clarify the concept a little bit. Using this as an example, though, I have a few questions:

1. Since phone numbers are unique (there is zero chance that the same number might be associated with more than one person) I'm not clear why putting them in the same table with the person is a violation of norms.
2. Assuming it is, some pratical considerations: The situation occurs in two places: the Students table, , and the Users table. In each case. the table has a form associated with it which is used to manage data related to that table. If I move phone numbers to a separate table, this will mean that each form will be pulling information from multiple tables. Is that likely to create unexpected glitches?
3. Given that the system is due to go online at the the end of the month, and that we are only talking about one class of information: Will I see real improvement in performance by splitting this information out into sperate tables? I'm wondering, basically, is this something I definitely should do, or is it more in the class of information I should file in my brain so I do it better next time?
4. The comment about towns and states is more confusing to me. I don't understand the reasoning for having two separate tables, one containing a list of towns and nothing else, the other states and and nothing else, which I think is what you're suggesting. How will this make the system more efficient?
5. One clarifying point that might affect your view: at this point, by law, the program serves students from only one state. State does not "need" to be included at all for internal reasons (it's a given), but is required for mailings.
6. Thanks for the tip on "Name." I thought that as an alias, it was okay, but I see your point now and will change it.

---

—nick

Answer 12

1.  It's having two columns for phone numbers which is the problem.  The definition of First normal Form is:

First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).  The same attribute, phone number, is being stored twice in your table.   Note that by attribute is meant attribute type, not attribute value.

You are limited to storing two numbers per student.  This may not be a problem in reality, as students are unlikely to have more than that.  When I was an undergraduate I didn't have a phone number at all!  It'd a matter of good or bad design per se.  In other contexts, particularly in a business scenario, it could be a real problem.  By having the numbers as rows in a related table there is no limit.  The table would probably also have a separate column PhoneType to distinguish between landlines and mobiles (cellphones).

2.  Not a problem.  The phone numbers go in a subform.  The following is an example of a real life form which does this:



Note however that having separate subforms for landlines an cell phones does not mean there are two tables.  Each subform is based on a query restricted on the PhoneType column.

3.  It is definitely something you should do.  It's not a question of performance, which will be unhindered provided that the keys which relate the tables are appropriately indexed (unique on the primary key, non-unique on the foreign keys).  It's a question of protecting the integrity of the data.  Allowing redundancy by the lack of normalization leaves a table open to update anomalies (see below in relation to towns and states)

4.  Normalization demands this.  By having a Towns table with one row per town, uniquely identified by a numeric TownID primary key, and enforcing referential integrity in the relationship on TownID between this and the Students table there is no redundancy and the integrity of the data is protected because the town name is recorded only once, and the enforced relationship restricts rows in the students table to those towns in the Towns table.  It's what John Vinson has called the 'Grandma's cupboard principle' - a place for everything and everything  in its place.

5.  Nevertheless you should have a one row States table referenced by the Towns table as this enables referential integrity to be enforced, preventing an invalid state name being inserted in Students.  This can easily happen - in one database I found three versions of my own name as author of technical articles in my own field of work!  An acceptable alternative in your case would be a validation rule on the State column in the Cities table.  An unacceptable alternative would be to use the interface to enforce the integrity.  I know that Northwind does this, but I'm sorry to say that in many ways Northwind is an example of how not to do things.

---

Ken Sheridan, Stafford, England

Answer 13

Thank you, Ken

(sigh) You are right, of course. I'm reluctant to make structural changes so late in the game, but I fully get the import of what you're saying— so I suppose I'll have to bite the bullet.

Could you clarify what you meant about  a validation rule on the State column in the Cities table?

I already have a look-up table used for assigning correct ZIP codes to towns (or vice versa). I'm wondering at this point if I should simply add a State column to that table, and then change the Town textbox to a combo box based on that table. State and ZIP could then autofill based on the selection.

---

—nick

PS: A whole lot of back-data from the last two years of services has already been entered. Do you have an suggestions of good shortcut method to migrate the data to the new tables?

Answer 14

As regards the ValidationRule for the State column, as it can only be one state, just set it to the name of the state in question.  You can also set the column's DefaultValue property to the name of the state, along with the DefaultValue property of any control bound to it in a form.  The state name will then be automatically inserted into a new record, however it's inserted.  You wouldn't need a States table at all as the value is being enforced at table level.  Also set the column's required property to True to disallow Nulls.

---

Ken Sheridan, Stafford, England

Answer 15

Thanks, Ken

I seem to be having a brain cramp regarding your suggestion about a towns table.

If only one town is listed per student record, why is this a violation of norms?

---

—nick

Answer 16

Hello Nick,

What Ken means here is that if you have 10 students and five are from the same town and your Tables aren't normalized to having a Town, then you are having reduntdent information because you are entering the name of the Town for each student therefore the Town exists multiple times.

---

Chris Ward

Answer 17

okay. . .

but why would that be different than, say, if two students had the same name?

(not quibbling with you or Ken— just trying to understand)

thanks!

---

—nick

Answer 18

Without a Towns table with one row per town (John's Grandma's cupboard principle) you cannot control the integrity of the Town column in Students.  The same town could be entered differently for two or more students from the town, just as in one scientific database I was K W Sheridan, K Sheridan and K V Sheridan as the author of 3 papers in a table of technical references.  An authors table with just K W Sheridan  and an autonumber primary key AuthorID would have prevented this.  Towns should also have a TownID primary key as, like personal names, town names can legitimately be duplicated (there are four Staffords in the USA to my knowledge, as well as the original one where I am - we are twinned with at least two of them).  Now you may not have any duplicate town names in your state, but that's not really the point, it's theoretical possibilities you should cater for in a well designed database.  Also, surrogate numeric keys by and large have advantages over 'natural' keys regardless of whether the latter are possible or not.

---

Ken Sheridan, Stafford, England

Answer 19

OKay. Thanks. That I start to understand.

So if I went back to my idea above, of using my ZIP code look-up table (but add a primary key) then it should accomplish what you're suggesting? And if I do it as a cbo, I will prevent misspellings and the rest.

---

—nick

Answer 20

Hello Nick,

What Ken means here is that if you have 10 students and five are from the same town and your Tables aren't normalized to having a Town, then you are having reduntdent information because you are entering the name of the Town for each student therefore the Town exists multiple times.

---

Chris Ward

Actually Chris, I'm not really saying that.  There is no redundancy in entering the town name 10 times, just as there is no redundancy in entering a numeric TownID value 10 times.  This issue here is one of integrity enforcement.  If the town names were guaranteed to be distinct the use of the town name as a natural key is quite legitimate in the relational model.  But a separate Towns table is necessary to enforce the integrity.

What does introduce redundancy is to have separate Town and State columns in the Students table as in the rows for the 10 students we would be told that town X is in state Y 10 times,  We should be told it just once in the Towns table.  Update anomalies are then prevented.

Of course, if we were to take normalization to its limits with regard to address data we would not even have Town in the Students table, but only Street as Street functionally determines Town.  But one has to be realistic.  In the UK, though, we do regularly normalize address data to this level because our postcode system maps to very small areas.  Mine maps to 36 houses on one side of my road, so my address is regularly entered into databases solely by house number and postcode.  I don't know enough about the US zip code system to know if that's possible the other side of the pond.

---

Ken Sheridan, Stafford, England

Answer 21

okay. . .

but why would that be different than, say, if two students had the same name?

(not quibbling with you or Ken— just trying to understand)

thanks!

---

—nick

With student names the name is not the determinant, the distinct StudentID is.  The same is true of towns, where TownID would be the determinant, but in a Towns table, not in a Students table.

---

Ken Sheridan, Stafford, England

Answer 22

OKay. Thanks. That I start to understand.

So if I went back to my idea above, of using my ZIP code look-up table (but add a primary key) then it should accomplish what you're suggesting? And if I do it as a cbo, I will prevent misspellings and the rest.

---

—nick

If the zip code is a determinant of Town then it would be like the use of postcodes here, which are a determinant of street, so you could store just the zip code in Students and map to the town in the zip codes table.  That would then map to the Towns table.

---

Ken Sheridan, Stafford, England

Answer 23

Good. Thank you, Kenn

---

—nick

Answer 24

If the zip code is a determinant of Town then it would be like the use of postcodes here, which are a determinant of street, so you could store just the zip code in Students and map to the town in the zip codes table.  That would then map to the Towns table.

---

Ken Sheridan, Stafford, England

Thanks Ken,

How does this workwhen in some cases we have multiple towns to a zip and in other cases we have multiple zips to a town. I ask because this is a real scenario in the US.

---

Chris Ward

Answer 25

Chris, the way I'm addressing this is to add an autonumber as primary key, rather than letting the Zip code serve that function.

Incidentally, Zip code identifies the actual post office, so when a person lives in West Clarkston and their Zip code pulls up Clarkston, Clarkston is technically their correct mailing address. I can say that with some authority, having worked for the post office for several years :-)

Conversely, if you wanted to, you could get data from the Post office that would allow you to identify Zip by street address or PO box number, when there are multiple Zip codes in a city. In my case. Vermont is not big enough and my DB is not big enough to need that level of sophistication.

---

—nick

Answer 26

How does this workwhen in some cases we have multiple towns to a zip and in other cases we have multiple zips to a town. I ask because this is a real scenario in the US.

---

Chris Ward

If there are multiple zip codes to one town, then it's analogous to UK postcodes, but for a greater area.  In my case there are two postcodes for my street, one for each side, so postcode functionally determines street.  However, if there are multiple towns in one zip code area then it differs from the UK system and zip code does not determine town, so you could not do as in the UK and have just a zip column and house/building number/name in an addresses table, but not a town column, as you would not know from the zip which of the multiple towns is the correct one.  But if as Nick says, the address is a postal one with the 'town' being the post office location determined by the zip code then you could do the same as we do, but because our postcodes map to a much smaller area you could not omit a street column as we can.

The practice of defining an address by postcode and house number does tend to be limited to large scale databases here, however.  Most small work-group or desktop applications would store the house number, street, town and postcode as columns in an addresses table, but not the county, which in our case is the regional level generally used, though the 'county' used sometimes no longer actually exists as an official administrative unit.

---

Ken Sheridan, Stafford, England

Answer 27

Interesting,

I know in several cities where I lived, ie. Montgomery Alabama has 35 Zip Codes, Spokane Washington has 29 etc. but also as an example Zip Code 99016 was my Zip in Spokane Washington and is also the Zip for Liberty Lake and GreenAcres. Liberty Lake, a city about 20 miles from where I lived and Green acres about 8 miles.

So it sounds like in a larger db (not what Nick is working with) you would use as you said more information in a Table or in related tables, and streets themselves would not be held within a zip code or vise versa since some streets transcend zip codes and do not determine the side of a street.

As a side note...

Spokane has 30 postal offices some with a different zip codes but delivering to many zip codes including overlapping delivery areas with other units. Montgomery, AL recently closed several Postal Offices and now that mail has been split into other units for delivery not based so much on area but on volume of mail.

---

Chris Ward

Answer 28

Hi, Kenn

You may recall I mentioned that the normalization issue you were concerned about existed in two tables— one for users, one for students.

If I create a single Phone table for both, as I'd like to do, is there a way to have either StudentID or UserID serve as the foreign key? If not it seems to me I have to have two separate phone tables, but maybe I'm not thinking clearly (it is a hot Friday afternoon. . .)

---

—nick

Answer 29

What you can do is have one field store the student or user id, have one field that tells you whether or not it is a student or user id, have a relationship from the student and user tables to the phone table on that field, and when you run your queries, check the "is it a student id" field to determine whether or not you get give it to the student parent table or give it to the user parent table.  Whether or not this would be considered a "best practice" is another matter.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 30

Thank you, SuzyQ

It begs the question, if it's not "best practice," whether I'd be better off to leave the phone numbers in the Student and User tables as they currently are.  While I grasp the concept Ken was discussing (that doing so is not according to correct normalization practice) I'm having a hard time getting my head around what problems it could cause.

---

—nick

Answer 31

Well, what you could do is have a "people" table and a "roles" table to identify the role(s) (student and/or user) in a one (people) to role (many) relationship, then you would not have an issue with your phone table because the relationship would be with the people table and not convoluted between user and student tables.  So a person may be a student and may also be a user and wouldn't have to be in both tables and their phone numbers would not need to be duplicated in the phone table (once as a student and once as a user - for each phone number they have).

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 32

Oi!

The logic is impeccable. But something in me balks at the notion of combining the two very different groups.

. . . and from the practical side, making such a radical change so late in the game seems insurmountable.

But in any case, there is no issue with User and Student duplicating— no opportunitu for a person to be both. What Ken saw as a violation of normalization standards was my lisiting multiple phone numbers within a single table (e.g., work phone, home phone, mobile phone) (see above for his comments)

thanks

---

—nick

Answer 33

Oi!

The logic is impeccable. But something in me balks at the notion of combining the two very different groups.

. . . and from the practical side, making such a radical change so late in the game seems insurmountable.

But in any case, there is no issue with User and Student duplicating— no opportunitu for a person to be both. What Ken saw as a violation of normalization standards was my lisiting multiple phone numbers within a single table (e.g., work phone, home phone, mobile phone) (see above for his comments)

thanks

---

—nick

Yes, I did see where Ken was going with what he indicated, however your newest question was having to do with the foreign key in the phone table - could the same field be a foreign key to both the user table and the student table; my second response to that addressed that situation in addition to the possible scenario of having a dual role for people.  My first response to that question showed you how it was possible to do what you wanted without a major revision to your tables, but indicated that that is probably not the best solution.  Your response to that dictated that I give you a better solution albeit one that questions the current normalization of your tables.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 34

suzyQ's solution is a perfectly valid one for your situation.  In modelling terms it represents a Type People with two Sub-types, Users and Students.  The relationships from People to Users and  People to Students would each be one-to-one, which is how a type/sub-type is modelled.  This sort of model is characterized by each sub-type sharing all the attributes of its (super) type, but not those of other sub-types.  In this case phone numbers would be one of the shared attributes.

However, such a model is only really required if an entity (person in this case) can be a member of more than one sub-type, which you say cannot be the case here, so there is no redundancy in modelling the users and students entity types discretely.  You would then have separate StudentPhones and UserPhones tables.  In fact the database from which the form I posted comes does exactly this, having separate tables for customer phones and salesperson phones.

One thing this illustrates is that there can very often be more than one legitimate model.  Deciding which to implement is a matter of judgement.

---

Ken Sheridan, Stafford, England

Answer 35

Granted that SuzyQ's second solution is the best theoretical one— and unfortunately, this late in the game, one I just can't do— and given that I didn't really grasp at first what she was offering with her first solution. . .

I've been fiddling around with this. Please tell me (both of you) if you think this is practical and whether it solves the norms isue:

<tfoot></tfoot>

tblPhones

PhID	StudentID	UserID	PhNmb
1	00001		(802) 888- 8888

Thanks!

---

—nick

Answer 36

Granted that SuzyQ's second solution is the best theoretical one

I didn't say that, I said it was legitimate one, but not the only legitimate one.

Your solution of having two foreign keys referencing the phones table on the other hand is not a legitimate one as you cannot enforce the relationships.  An unenforced relationship is no relationship at all in any meaningful sense.  I recall doing something very similar many years ago when I was green behind the ears relationally speaking, and lived to regret it.  If you did have a single phones table you'd have to model the relationships by tables to exclude the Null foreign keys.  Far simpler to have separate phone tables for users and students.  The fact that both are sets of phone numbers doesn't preclude them being discrete entity types.

---

Ken Sheridan, Stafford, England

Answer 37

Okay, thanks!

Two tables it is, then

---

—nick