Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10

    Wink Unanswered: Multiple entries on same field

    Hey guys! First timer here and need some help. I'm creating a small system that will allow my users to enter employee data. Part of the data could be up to 5 position fields (some part time employees could hold more than 1 position). If I want to eventually do a search on someone who has 'position1' as a position, I would like to search through all their positions and if I find it, I can put it in a report (I'll worry about the report later).

    I created 2 tables. One with the employee record (by SSN) and a Positions table (SSN and Position field). What I would eventually like to do is to enter an entry by SSN for every position my users enter in. From there I would create a query for the information (from both tables) for any employee who's position matches my search.

    Any good recommendations?

    Sample mdb attached. Change extension to .mdb.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2003
    Posts
    268
    You are on the right track.

    Create a table called EmployeePosition (you could actually rename your Positions table.

    Create a table calles Positions with only the position as an attribute.

    Then join the two tables together via 1:M and Viola you have an exref table.

    Modified your txt to show these changes.
    and a sample query too, you can change the position criteria to find out who is what.

    HTH
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10
    I believe I'm closer to where I'm trying to get. My table is a bit more complicated than what I sent and took me a while to figure out the relationships. The file you sent me was asking for SSN before opening the form. If I go to the form (by entering an existing SSN), I see multiple records for that SSN. The position is duplicate for all the positions. Doesn't seem to be working quite right. What's supposed to happen is that if there's more than one job, it should pull it up on the main record just list it in a different position text box. Hope this makes sense.

    Any further help would be appreciated.

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Subform

    What you are trying to achieve will require a subform. I have modified your employee form to include a subform called sfrmPositions. For each employee it will find the respective positions assigned to that employee. You can add more positions to an employee by just typing them in on the subform. All changes will be automatically updated. However, on caveat with this though. You can only add positions that are defined in the positions table. Manager, Pee-on, Inter, Plebian. That is it. If you need to use more positions, just add them to the positions table.

    HTH. If you have any more questions just post.

  5. #5
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10

    Cool Re: Subform

    I couldn't see the subform. When opening your form, it would give me a 'query-like' window and ask for 'position.ssn'. It wouldn't let me in unless I knew a current SSN. I'm using Access 2000 by the way.

    Is there any way to allow users to type in whatever they want on the position field? I know that it would guarantee correct data but we have a LOT of positions and would be more simple to allow them to enter them as they come. I guess I could create a 'simple' form to populate the positions table but still. If you can resend the file so I can see the subform, I'd appreciate it.

  6. #6
    Join Date
    Mar 2004
    Location
    Atlanta
    Posts
    10

    Re: Subform

    I'm almost there. I'm attaching a new database (rename to .mdb). Closer to the real thing. I'm still getting problems attaching the SSN to the positions. I have two subforms since I have 2 sets of positions that I could get for each SSN, current position and position placed (or new position). Looks right to me as I'm comparing it to my small-scale test case (posted previously).

    I appreciate any help you guys can give me!
    Attached Files Attached Files
    Last edited by breban; 03-10-04 at 17:37.

Posting Permissions

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