Many to Many Relationship... I think?

[Yecats131]

Hello!

I've been reading books and searching the net for an answer to my question.... and I've only been confusing myself more.

I'm trying to design a part of my database and it breaks down like this:

Tables:

Transaction: Conversion
* Contains a lookup field that pulls from the table "Missing Documents"
Transaction: External
* Contains a lookup field that pulls from the table "Missing Documents"
Missing Documents

Now, for the Lookup field, I need the user to be able to pick multiple items. I know that while you are setting it up you can pick the "allow multiple selection" option (or something close to that). However, I've read that if you allow that then you cannot convert the database over to SQL because it isn't built to allow it.

So, since the multiple selection seems to act like a junction table in a many to many relationship I figure that is my best option. However, I just don't get it! LOL!

I would appreciate any help I can get in having it explained to me... Basically:

1. How do I set it up?
2. Can I get it to update the Junction table automatically up date based off of what the user selects while in the Conversion or External table? (In this case it would be a table that had the EmployeeID & MissingPaperworkID fields)
3. When creating a form for my conversion and external tables, how should I set it up so that when the transaction is viewed later you can see what the list is for the missing paperwork?

Please let me know if you need anymore clarification in what I am asking.

Thanks!

 

[neileg]

More info is needed!

If you have Transaction: Conversion and Transaction: External as two tables, couldn't they be normalised into one?

 

[doco]

Is the dropdown on a form?
Is your intention to allow the user to select several items from a list for the purpose of returning records based on that selection/those selections?

If the purpose is to create a criteria list then put that selection/those selections into an IN( first, ..., last). But as Neileg said "More info is needed!"

 

[Yecats131]

More info is needed!

If you have Transaction: Conversion and Transaction: External as two tables, couldn't they be normalised into one?

No - they're two different types of transactions that require differen't information. It's best if they are seperated. I only showed you that those to tables exsisted to illustrate that once this is solved, I'll have to apply it to another table. :0)

Is the dropdown on a form?
Is your intention to allow the user to select several items from a list for the purpose of returning records based on that selection/those selections?

If the purpose is to create a criteria list then put that selection/those selections into an IN( first, ..., last). But as Neileg said "More info is needed!"

My intention is to have the user select multiple if needed.. so that when a report is run later that shows all New Hires with missing paperwork, it can display WHAT it is that they are missing.

PLease let me know if you need more clerification... I really want to get this going today. :0)

 

[neileg]

Hires? You didn't mention hires in the first post!

Table structures please, relationships and an idea of the business process.

 

[Yecats131]

Hehe sorry!

Okay, so I think my tables may be setup incorrectly... I've been reading about database theory. This is how I have it setup now though:

The table has three main sections:

1. Tracking Transactions
2. Tracking Requisitions
3. Employee File

These are then broken down into types
1. Tracking Transactions(Currently, Each type has a seperate table because they require different information):

A. Conversion Hire
B. External Hire
C. Promotion
D. Rate Increase
E. Bonus
F. and so on...

2. Tracking Requisitions (One Table):

A. just has the one 'type' which is where the user changes the status, adds notes, and adds misc. information about the req.

3. Employee File (Haven't made it this far yet):
Broken up into 3 sections:
A. Applicant
***Moves to Current Employee once the Conversion or External transaction has been complete and the position has been accepted. Stays an applicant if they decline the position.

B. Current Employee
***Stays as such until they are termed.
***Will contain a history of the employee's transactions based off of the "Tracking Transactions" portion.

C. Termed Employee
***May be reactivated to become a current employee.
---------------------------------------

So that is a quick over view of what my database will look like. I'll need it to be able to do other things - which is where my question comes into play.

For the External and Conversion Transactions there needs to be a spot for the new hire paperwork. The paperwork has about 15 or so things that they have to return to HR. I would like the user to be able to select which paperwork is still missing as sometimes the new hire turns in 8 out of 15 or 14 out of 15.

Now, because this database might need to be turned into SQL some day, I do not want to use the option of Allowing Multiple Fields or something along those lines. I've read that SQL doesn't support it. So, I'm trying to learn how to do it the old fashion way which is the Junction Table.

Where I'm at:
I've created a Lookup table that has two columns.
1. The primary Key
2. The Name of the paperwork

This is used on my External and Conversion Tables under the "Missing Paperwork" column.

So back to my questions:

1. How do I properly setup a junction table?

2. Can I get it to update the Junction table automatically up date based off of what the user selects while in the Conversion or External table (Which will of course be via a form)?

3. When creating a form for my conversion and external tables, how should I set it up so that when the transaction is viewed later you can see what the list is for the missing paperwork as well as select which paperwork is missing? (I'm thinking check boxes?)

 

[Yecats131]

I'm going to bump this - I'm still stuck.

 

[neileg]

You are still only describing the database. Without some understanding of the business process, it is impossible to advise.

 

[georgedwilkinson]

So back to my questions:

1. How do I properly setup a junction table?

2. Can I get it to update the Junction table automatically up date based off of what the user selects while in the Conversion or External table (Which will of course be via a form)?

3. When creating a form for my conversion and external tables, how should I set it up so that when the transaction is viewed later you can see what the list is for the missing paperwork as well as select which paperwork is missing? (I'm thinking check boxes?)

1. Create a table with fields for the FK from the two (or more) tables in question. Go into relationships and drag the PK from those 2 tables to the appropriate fields in the junction table.

2. Does not compute. The correct way is to manipulate the junction table directly. Though if you were a very experienced VB programmer you might do it programmatically...I don't though I am--I'm just too lazy.

3. Tricky. There are a number of methods. I sometimes make a separate sub-form for each item I am tracking. There are just so many different ways to do this that it's hard to answer directly. Checkboxes can be tricky when used in this way, but that shouldn't stop you.