help with my first database.

[DWR]

I have a database which is going to be used to record quality alerts here as well as the employees who signed off on them.

I have one table containing the Alert info (Alert#, Date and Description) Another containing Employee info (Clock# & Name)

What I want to do is have a form in which I may enter the info for the alert and then use a lookup type field where I can select multiple employees (listbox?).

After the info has been entered on the form it I want to be able to retrieve the info about the alert as well as the employees who have been entered as signed off.

What is the easiest/best way to accomplish this? Do I need another table???

Thanks

 

[RichMorrison]

<<
What is the easiest/best way to accomplish this? Do I need another table???
>>

Yes, you need another "junction" table with Alert# and Clock#.

When you assign one or more Employees to an Alert, you insert a record for each Alert#/Clock# combination.

So if Employees 1, 2, and 3 are assigned to Alert 1, you will have 3 records:
1, 1
1, 2
1, 3

RichM

 

[DWR]

Now here is my current situation. I have the two tables with the first being employee having the following two fields Clock# & Name. This table is already fully popualted with employee names and clock#'s

The second table is Alert it has the fields Alert# (which is a letter with a letter to distinguish the area of origin.), Date and description.

What I understand is I should create a table containing alert# and clock# and join the tables with relationships. Should I use a lookup value for clock# or how should I go about this?

Once this is done how do I then create the new records bonding the alert# and it's info with the employees who have been entered as signed off?

Thanks for your help sorry I am so slow on this.

DWR

 

[Pat Hartman]

Take a look at this many-to-many sample database:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

Also, you're batting 1.000 with your column names. They are all bad Clock# and Alert# contain special characters, Date is a function, Name is a property, and although Description is allowed by Jet, most other RDBMS' would reject it because it is also a property. In summary, do not use embedded spaces or special characters and avoid the use of reserved words especially function names and properties.


Reserved words