Design Help Please

[ceatana]

Good afternoon.

I am trying to redesign our database at work and I'm a little stumped. The previous database had 1 huge table with everything in it.

I have an idea of what I want the database to do, but I'm not 100% sure how to do it.

A little background:
We are a union. We get employee information from the employer (ex. First and last name, start date, location, etc.)
Once an employee signs their union card, we get personal information (home address, email, phone, dob, etc)
Employees pay dues on a monthly basis (remitted to us by the employer), that we track in our database.


Employees can also take part in the union or health and safety committees. I'd like to start tracking that information (just really start and end dates and position).
Employees can also opt to get additional life insurance through the union, which they pay the premiums to the union for. That information is currently stored in a second, linked, database that I would like to incorporate into 1 database for everything.

I am not certain how to organize my tables, though, to best link them. Initially, I had the following tables (not actual names, just description for ease of communication):
Member Work Information
Member Personal Information
Buildings (this consists of the various locations of the employees with building address and name information)
Categories (the various categories and sub-categories for our members)
Paylists (the employer department groups, which includes paylist number and name in English and French)
Terms (The start date and end date of a work term)
Insurance Rates (the premium rates for the insurance
Insurance (The main insurance table with relevant information (start date, beneficiary, etc)).
COSH (start and end dates and committee name)
Union (start and end dates and union position)
EX/LWOP (start and end dates for employees who go into and out of excluded positions or leave without pay)
Dues (Year and dues paid each month for a given year. I need to have a record of dues paid in the past, so was thinking of a year field and then fields for each month).

I would like the main form for inputting information to look like the picture below.



The Personal info, Dues, and Insurance tabs will have a form view, the rest of the tabs will be table view. I want the information in the subforms to populate when I change the Lookup by Name field.

Any help on how best to organize this would be great. Am I making too many tables? Are my layout dreams beyond the realm of possibility? And help on this would be greatly appreciated.

[CJ_London]

not sure you have provided enough information but to comment

Am I making too many tables?

you need as many tables as you need, no more, no less

As a rule of thumb it is one table, one form using subforms to bring related data together and using the linkchild, linkmaster properties of the subform to represent the relationship. If you have mapped your relationships then these will populate automatically.

[ArviLaanemets]

At your picture I read, that you have all employees in your database, not only members of union (you have a check box on main form to mark the employee as member of union). So the main table to which others are linked is tblEmployees, not tblMembers.

Do you keep account of members of your union only? Or don't you have any concurrent unions at all?

When designing the tables, the easiest way to handle them is to have an autonumeric primary key in every table. When you have some fields or field combinations which also must not be empty and must have unique values/value combinations, then define according additional unique indexes (p.e. when all employees must be union members, then you can define MemberNumber field as unique index).

All tables linked to tblEmployees must have a field where according value of primary key from tblEmployees is stored (A Foreign Key). You can define this field in all tables as non-unique index too - it is good for your app performance. When the PK in tblEmployees, p.e. EmployeeID, is autonumeric, then according FK's in linked tables must be long integers.

By 'form view' and 'table view' you mean single and continuous forms?

To link 2 forms as Parent and Child, you must:
1. Insert the child form as subform into parent form, and link parent form PK with child table FK in child subform;
or
2. Have both parent and child form as subforms in another form, have an unbound control on this form where Current Event of parent form writes the value of current PK from parent form, and have this unbound control linked with child table FK in child subform.

NB! You must differ between child subform and child form - they are different objects. The child subform is object in parent form! The child form is Source object in child subform (Parent Form > Subform > Child Form).

Linking forms is made setting subform's LinkMasterFields and LinkChildFields properties. When you link parent and child forms, then you set these properties p.e. to '[EmploeeID]' for LinkMasterFields, and to '[EmploeeID]' for LinkChildFields. When you link unbound control and child form, then you set these properties p.e. to 'txtEmploeeID' for LinkMasterFields, and to '[EmploeeID]' for LinkChildFields. When you link parent form and child form, then creating the subform (dragging child form into parent form) usuallu creates links automatically. When you link unbound control and child form, then you have to set links manually (and Access will complain, that you want to do an impossible operation - don't belive him).

When all this is done properly, then:
1. Whenever you select a new record in parent form, only linked record/records in all linked child forms are displayed (or are navigable when the subform is single one and has several linked records);
2. When you add a new record into subform, it is automatically linked with parent form (parent PK is written into field linked to child form's FK). Here follows, that when you must have only 1 linked record in child form (p.e. with single type subform), then you have to disable adding records, when there exist a linked record.

All controls in all forms linked to autonumeric PK's and to FK's must be set invisible - users must not have access to them and must not dabble with them at all. (But they must exist on form.)

[ceatana]

No. The checkbox for Union is for people who volunteer in the Union (as a local representative or treasurer or one of the VPs). Everyone in the database is a member of the union.