auto adding fields in datasheet view?

[techexpressinc]

I have a access db with 2 tables. Student School
When adding rows to the Student table for the field School the selection is a drop-down list. I want the school address to automaticly populate the Student table for the field School Address on the Student table.
Is this possible and how?
The access db is a small file attached and at
http://www.box.net/shared/7ncxb1u1dg
Thanks Russ @ techexpressinc.com

 

[Scooterbug]

For starters....I think you should read up on Normalization. I'm assuming that the primary function of the database is to record something that has to do with students. You shouldn't need to store the school information twice. You should store the schoolID along with the Student record.

Also, you have several fields that have a table level lookup. Do a search on the forums here for the Evils of Table Level Lookups. They will cause you nothing but problems down the road.

 

[techexpressinc]

Scooterbug - Thanks for your input. I agree multiple storage of same data arross tables is bad. The schoolID is being stored with on the Student record, field "Child School" in the form of the School name. For all the reports they DB owners should join the tables together. The users like to just dump/copy tables into Excel and work with the data. The users are not comfortable writing queries or reports.

Can I some populate the STUDENT table’s fields: school address, Location, zip code; once they select the school from the drop-down. I think it would be populated with the pointer not the actual data, like the school name is populated with the pointer.

I am very limit part-time help for the DB owners

 

[Scooterbug]

What you would do is store the ID of the school in the student table. This way, you can have a relationship between the student and the school with the ID being the key to the relationship.

As for the end user not being comfortable with writing queries and reports...they shouldn't have to. The first step to database design should be finding out exactly what the database is going to be used for. This way, you can plan all of the database components (tables, forms, reports, queries)

 

[techexpressinc]

The ID of the school is stored the student table. field "Child School" it is a number which is the id number on the SCHOOLS table the field
definition is this drop-down:
SELECT [Schools].MailingListID, [Schools].School FROM Schools ORDER BY [School];

So, once the user selects the right school can i auto-fill the other fields address ones or does it did to be external query ran to update the fields on the student table once in a while?

Thanks for look at this problem for me.

Russ

 

[Scooterbug]

I have attached a copy of your database with the Student and School tables fixed. There is a form, frmStudentInformation that will illustrate how to use a combo box to store the ID number of the school in the Student record.

As for storing the address and other information for the school on the student record, you dont need to do so. Storing the information like the way you want violates the rules of Normalization. What happens if the child changes schools? You then have to update not only the school, but the address as well. Or what happpens if the school's address changes for some reason? You would have to go through all the students records with that school and change the address for each student. By tying the school by an ID number, you only have to change it once.

Think of the relation between records like using legos to build something. The lego that has the student information on it has a number printed on it. By looking in the bin with the legos with school information on it, you can find the school who's schoolID number matches the number printed on the student information.

 

[techexpressinc]

the form does not allow me to change the school on a student.

if the address field on the student is just a query look-up behind the field to the SCHOOL table. the data is not being stored twice. There could be a problem if the entry was ever deleted from the SCHOOL table, thou.

I was thinking the just like the child-school is a number that relates to the SCHOOL table for the school name.
There would be a number for the address that relates to the SCHOOL table.?

 

[techexpressinc]

This group of people are not into forms. They like, insist on just view the tables in layout view, updating them in that view.

 

[Scooterbug]

the form does not allow me to change the school on a student.

Hmmm....I had no problem changing the school for a student

if the address field on the student is just a query look-up behind the field to the SCHOOL table. the data is not being stored twice. There could be a problem if the entry was ever deleted from the SCHOOL table, thou.

Are you referring to the school address, or the student address? If you are referring to displaying the school information when the school is chosen, they you can put a subform with the school data on the main form, linking it via the SchoolID on the subform and the bound column on the combo box.

I was thinking the just like the child-school is a number that relates to the SCHOOL table for the school name.
There would be a number for the address that relates to the SCHOOL table.?

I really suggest that you read up on Normalization. When designing a table, you lump together data that is similar. In this case, you want data associated with the school. So ALL school related data should be stored as one record, with the Primary key (the number that is used to identify a record as Unique) being what you use to look up the data. If you wanted to store the color of the bricks of the school, if the driveway is paved or not, the number of windows, etc they all go under the same record. This includes the address (Unless, for some odd and strange reason it has more than one address)

This group of people are not into forms. They like, insist on just view the tables in layout view, updating them in that view.

Letting the end user have access to the table for data entry is a very bad, bad, bad idea. You can setup a form to LOOK like a table (Properties, Default View, Datasheet). If someone gets froggy and decides to mess with the datatype of a field, for example and changes it...it could muck up the whole database.

 

[boblarson]

This group of people are not into forms. They like, insist on just view the tables in layout view, updating them in that view.

That is like letting people insist on cutting their own Japanese Pufferfish (Fugu). It is highly dangerous and could seriously affect the health of your database and data integrity. As ScooterBug has said, give them a Datasheet form which LOOKS like the table, but you still retain control using events, etc. which allow you to validate data and keep data and database integrity. Giving the users access to tables and queries directly is a big no, no when it comes to good practice, regardless of what they THINK they should have.

 

[techexpressinc]

That group of users been doing the table layout updates for years.

I am not an regular employee to them, just called on when they want something, then for next to nothing allocated in time.

They asked for when they select the school from the drop-down selection that the address be popluated. I did not think it would be that hard but it appears it is??

 

[Scooterbug]

If they insist on storing the data in two places....it can be done. Most of us here on the board will give the correct way to do things...mostly because it's the way we do things.

The easiest way would be through the use of a form. You could include the address for the row source for the schools...then use the .column(x) property to fill in the address field.

As far as doing it straight through the table....I honestly dont have a clue. I've never entered critical data straight through the table. Since there are no events that you can use I dont know of a way to have the field auto populated.

 

[boblarson]

As far as doing it straight through the table....I honestly dont have a clue. I've never entered critical data straight through the table. Since there are no events that you can use I dont know of a way to have the field auto populated.

Yep, REQUIRES A FORM to do that. (at least until Data Macros arrive in Access 2010)