Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Networked Entry won't accept 5 letters (A2k)

    To my favorite resource for tough questions,

    I have built a database that is networked, backend in Network, front end at unique sites. 4 of the tables have one-to-one relationships in the ID field (set as primary key) This field combines the last three letters of the last name and the first two letters of the first name. So...Joe Smith would have the ID code of SMIJO.

    I have form that adds a student name and ID to all 4 tables. I am not getting the same results at home when my tables are not on network. At home I can enter 4 or 5 or 6 ID letters and it will accept it. It school, on the networked backend tables it will NOT ACCEPT the 5 letter code...we have gone to a 6 letter code and it goes through. (The 5 letter code is unique and new, I checked)

    I am new to networking. Most, but not all the relationships are on the back end.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    It appears that you have changed the fieldsize in the backend table at home for the ID field to 6 characters, but you have not made the same change to the backend database on the network.

    On another note, why are you putting the Student Name and ID into each of 4 tables? You should have a Student table, and merely put the ID number into each of the other tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    Thank you. I will check that on Monday. But I don't think that will be the field size. I'm also going to check my relationships again, which is what I tinkered with the most when I got to the network.

    On the other note...I see your point, but often they look at the table alone and you can't tell the name of the student from the code. So I have duplicated the information.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    >>On the other note...I see your point, but often they look at the table alone and you can't tell the name of the student from the code. So I have duplicated the information.<<

    They really shouldn't be looking at the data thru the table. They should be looking at it via a form (or a query). What if someone changes the name in one of these tables? In the long run, you will find that this supposed "convenience" will probably cause someone alot of inconvenience; which will fall back on you! The rules of normalization were created to preserve data integrity and to protect the developer's backside!<g>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    Mark's points are appropriate, but is it possible this is related to the Access 2002/2000 situation? I haven't seen any data issues in working between the two, but experience in other areas has made me gun shy. In any event, networking shouldn't cause problems of this sort so I would look at other possibilities.

    As far as the ID code is concerned, it seems to me that 5 or 6 letters based on last name and first name are likely not to be unique. There are too many Smiths in the world for that to work long term. In any event, unless you use cascading updates (which I avoid if at all possible), if a student ID has to be change for some reason, it would need to be changed in all of the records. I would look at using an autonumber field as the primary key and put the Alpha Student ID only in the master table.

    Which brings me to my second question. Why do you have 4 separate table to describe a student? If entries in a table exist only for some students, that's a good reason. On the other hand, if there is always a record in all 4 tables for every student, then why not put them all into one table? For one thing you will only have one primary key to deal with instead of 4. Finally, you do want all of the relationships in the back end with the data. Hope this helps.
    Wendell

  6. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    Thanks Wendell and Mark

    I am learning a lot.

    I am going to shut off their access to tables. I have created an Add Student form and a Delete Student form, so they should not need access to the tables. I work with all aspects of the database when I work in the cattle data here at home and I am learning what and how to restrict different users out in another program. Relationships in the back...got it.

    I have set my relationships to have a cascading update. If this is not a good idea, tell me why I should not...

    The four table question...I have a table with general data. It contains 85 fields. The address table has 8 fields. The GPA table has 80 fields. The Testing table has 42 fields. I usually split the data when it gets too many fields.

    I wish I could start completely over with all this new knowledge! Thanks.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Networked Entry won't accept 5 letters (A2k)

    I still learn a lot every day - remembering it tomorrow is another matter! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> (It's opera time again.)
    Cascading updates is an issue of personal taste to a large degree. In my case, I guess I don't entirely trust the back-end to do that or something. And it always make me nervous when I change something that is the primary key for a table. I want it to be a constant for the time that the data exists. We have a client who has been using a similar key for 5 years or more, and I don't know of any serious problems they've encountered.

    Seeing your table design, it seems pretty reasonable. You have some data that isn't always needed, so you put it into a separate table and join to it when you need it. Besides, if it was all in one table, you would be getting fairly close to the upper limit for the number of fields in a table, and could go over the maximum record size in bytes. You could get cute with addresses, as you often have two or more students with the same address, but that adds another degree of complexity. I guess you might be able to do some normalizing with the Testing and GPA tables if you used a one to many approach, but again that would add some new twists, so I think this rambling is mostly food for thought.

    Best I get with my day - hope yours goes well.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •