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.
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.
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.
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.
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).