Results 1 to 4 of 4
  1. #1
    Thornjr81 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    2

    Taking data from one table putting in another based on an input

    So I am having a couple problems and I will start by explaining what I'm trying to do.



    I maintain a database of lockers and I have to assign a group of about 130 lockers every 2 months. There is a seperate set of staff lockers and student lockers, and I have both book and clothing lockers that need to get assigned.

    When a new class shows up there is another access database from which I import the new class data, and then take that data and put it into a master personnel file.

    So the tables I have are Personnel info (this is the master file), with fields: SSN, LAST_NAME, FIRST_NAME, CLASS#, Gender (Gender is for assigning female/ male clothing lockers and there are other columns but they are not relevant to what I want to do). Then I have locker assignments by SSN, with fields SSN, Physical Condition (Works or Broken), Purpose (Staff, Student, Staff/ Student Overflow), Locker#, and Gender for the clothing lockers.

    What I would like to do is have access ask me for a class# input and assign this class lockers that are empty based on the criteria Purpose ="Student" or "Staff/Student Overflow" and Physical Condition = "Works" (and Gender in the case of the clothing lockers).

    Any help would be greatly appreciated. I have been using access programs for a long time but this is the first one that I have actually took on to make the program. I am not opposed to using visual basic with some guidance.

    Thanks in advance!

    Josh

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    When working with a relational database, you would only use 1 field to link the various tables rather than copying data from several fields between tables. First, I would recommend that you join your personnel and locker assignments via a primary key field rather than the SSN. Using a numeric field is more efficient for a relational database compared to a text field. So your personnel table might look something like this

    tblPersonnel
    -pkPersonnelID primary key, autonumber
    -SSN
    -LAST_NAME
    -FIRST_NAME
    -CLASSNO
    -Gender

    Also it is best not to use spaces or special characters in your table and field names (I changed the # in class# to CLASSNO)

    Now you need a table to hold information about the lockers, but I'm a little unclear as to some of the locker info you presented. You have lockers that can be for staff or students. If a locker is currently considered a staff locker, can it ever become a student locker? Can a student locker ever become a staff locker?

    Also, you have book lockers and clothing lockers and the clothing lockers are either male or female subtypes.

    Can a staff member have both a book locker and a clothing locker?
    Can a student have both a book locker and a clothing locker?

    Can either a staff member or a student have more than 1 clothing locker? more than 1 book locker?

    You will need a table to make the assignments of lockers to people, but I'm just not sure how the locker table needs to be structured.

  3. #3
    Thornjr81 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    2
    Thanks for taking the time to answer my questions.

    For the book lockers 1-250 are for staff only and lockers 251-400 can be staff or student (in case there are not enough student lockers for the number of students we have). Lockers 400-834 are solely student lockers. It is prefered that students get student only lockers and staff get staff only lockers due to their location. If all the student only lockers are full, only then do I want to assign them a staff or student locker. Also, sometimes the lockers break and I don't want to assing a known broken locker to a new student so there is a field PhysicalCondition with the values of either "Works" or "Broken". Yes staff and students can have a book and clothing locker. For the most part, each person can only have one of each.

    For the clothing lockers, there is no staff or student requirement but there are some male lockers and some female lockers as well as the Physical Condition.

    The tables I currently have are

    Personnel
    Book Locker info (combos, manufactured data etc)
    Clothing Locker info
    Book Locker Assignment
    Clothing Locker Assignment

    I think that is all of them, but I'm not 100% because this database is at work and I can't bring it home because the book locker combinations are Confidential.

    I have tried using an Update query, and it just doesn't do anything when I run it.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    All lockers, no matter what type or who they are for, should be in 1 table (rule: like data in 1 table)

    tblLockers
    -pkLockerID primary key, autonumber
    -LockerNo
    -fkLockerOwnerTypeID foreign key to tblLockerOwnerTypes
    -fkLockerContentTypeID foreign key to tblLockerContentTypes

    The following tables just hold descriptors used to describe the locker:

    tblLockerOwnerTypes (3 records: student, staff, either)
    -pkLockerOwnerTypeID primary key, autonumber
    -txtLockerOwnerTypeName

    tblLockerContentTypes (2 records: book, clothing)
    -pkLockerContentTypeID primary key, autonumber
    -txtLockerContentTypeName


    Now the table to hold the assignments
    tblLockerAssignments
    -pkLockerAssignmentsID primary key, autonumber
    -fkPersonnelID foreign key to your personnel table
    -fkLockerID foreign key to tblLockers
    -dteEffective (date locker is assigned)

    As to the locker condition, I assume that a broken locker can be fixed and put back into service, so it would be best to have a table that tracks the condition of each locker (a locker can have many related condition records as it goes from in service to broken and then back into service over time). I would probably start with 1 record for each locker in the following table so you have an initial condition for each.

    tblLockerCondition
    -pkLockerConditionID primary key, autonumber
    -fkLockerID foreign key to tblLockers
    -fkConditionID foreign key to tblCondition
    -dteEffective date that the locker is in the given condition


    tblCondition (contains records such as: in service, broken, out of service(i.e. completely/no longer usable) etc.)
    -pkConditionID primary key, autonumber
    -txtCondition

    With this structure, you can use (select & aggregate) queries to find those lockers that are available at any give time. There would be no need for an update query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-18-2011, 06:38 AM
  2. Replies: 0
    Last Post: 07-14-2010, 04:01 PM
  3. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  4. Replies: 1
    Last Post: 06-14-2010, 02:31 AM
  5. Replies: 1
    Last Post: 05-25-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums