best way to add many employees to a table?

[wjg]

Is there a way to sort fields automatically when data is entered through a form?

This is my situation, I have three tables, Clients, Employees, and Jobs. I would like to use a form to populate the Jobs data drawing information from the Clients and Employee tables, plus user input for later visualization, reports, and calculations. Each job will have more than one employee, or any combination of employees.

Is there a a way to enter employees from the form so that the chosen employees are sorted into the table? I am hoping to see the employees per job entered alphabetically or sorted alphabetically in the fields after entering record, or should it not matter and that there is a easier way around by crafting a report? I plan to have 4 fields for each job for Emply 1, Emply 2, Emply 3, Emply 4.

Is the above method the best way to do such a thing, having extra fields for employees that may or may not be used? Each of my Jobs will have 1-4 employees that I want to keep records of, and calculate their pay based on their rate and participation.

[kabing]

I'm not sure about the sorting, issue, but rather than the Emply1, etc. fields, you are better off adding another table, named something like Employees_On_Job. It would have two fields, EmployeeID and JobID (i.e. the primary keys from the Employees and Jobs tables) and together those two fields would make up the primary key for the table. If you need to store more data about their role in a particular job, you might need more than these two fields.

Then use a table-style subform on the Jobs form, with the Employee column set for a listbox that displays the employee name but saves the EmployeeID to the table.

See the movies database attached to this thread.

kabing

[wjg]

I assume then, there would be another table, with the employee information, linked by the employee ID?

I have never created a subform, but I will experiment.

I just wish there was a good reference book for Base. There are entire books for Openoffice, but they all basically just gloss over the Base component. Such a reference is needed, but I virtually know nothing of SQL.

[kabing]

I assume then, there would be another table, with the employee information, linked by the employee ID?

I presumed that was the Employees table you mentioned in your first post. The table I suggested is in addition to the three you listed.

I just wish there was a good reference book for Base. There are entire books for Openoffice, but they all basically just gloss over the Base component. Such a reference is needed, but I virtually know nothing of SQL.

I agree. Part of the problem is that Base is the newest component. The OOo Authors group is said to be working on a more extensive guide, but it's not out yet. Solveig Haugland has a databases workbook for purchase that I've considered getting, but I haven't yet.

I did find the SQL for Mere Mortals book to be a helpful introduction to SQL.

kabing

[wjg]

I'm not sure about the sorting, issue, but rather than the Emply1, etc. fields, you are better off adding another table, named something like Employees_On_Job. It would have two fields, EmployeeID and JobID (i.e. the primary keys from the Employees and Jobs tables) and together those two fields would make up the primary key for the table. If you need to store more data about their role in a particular job, you might need more than these two fields.

How do I make two fields form a primary key?

[kabing]

The easiest way is to create the table using the table wizard. In Step 3, click on the "Define primary key as a combination of several fields" then you will be able to choose which fields to use for the combined primary key in the columns below that option. If you use the wizard, you will need to choose field names from the pre-determined list in step 1, but you can rename them and alter their properties in step 2.

Presumably there is a way to do it in either the table design window or through the SQL tool, but I don't know how to do it using either of those approaches.

This is the way it works for me in NeoOffice 2.2.3, which should be the same as OpenOffice.org 2.2; I don't think this would have changed in OpenOffice.org 2.4.

kabing

[Villeroy]

How do I make two fields form a primary key?

Open the table in edit mode (right-click>"Edit"), select the columns in question(Ctrl+Click), right-click>"Primary Key".

[wjg]

Thanks - I was trying to do a control-click - but I must have been in viewing mode, and not editing mode.