2 Questions

[bpaquette]

Guten tag,

I have 2 questions about tables:

first is primary key. i have a table featuring all the personnel in my squadron, and i've set the social security # as primary key for obvious reasons. the problem is i have a form that lists everyones names and you can select it to load their data into the form. simple enough, except it's listing the names in order of primary key. is there anyway to override this and list them alphabetically?


second question is one of design etiquette. i have about fifteen fields other than name and social. not all fields have data in all records, but it all applies to the majority of records. anyway my question is this: all my data manipulation is done via forms and sometimes queries. what would be the down side (if any) of throwing all these fields into one table de grande?


regards,

 

[Mile-O]

Answer 1: Use a query and sort on the specific field(s) to order your data on your form.

It shouldn't matter how data is ordered in your table.


Answer 2: Each table should contain information relevant to one thing i.e. Employees, but Department (which you would expect to find as a field in the Employees table) would have a separate table as it is not dependant upon the Employee. The department’s primary key would then relate to the Employee in a one-to-many relationship. i.e. one department can have many employees.

What are the fields in your table?

 

[bpaquette]

a lot of it is jargonish so let me sum it up



Office (dept)
Rank
deros (when they're leaving us)
address (where they physically live)
mailing address (this will always be a po box)
home phone
duty phone
cell phone
status (whether they're not here yet, are here, on leave, hospitalized, etc)
DOB


there's a few more but they're not really uch different than the onesl isted.

 

[namliam]

I agree with Mile, as ussual. Do "Standard" normalization

Department for instance is not a thing you want typed in you record, but use a combo to fill it with a PK from the department table
If you allow text typing of department (or fields simular) you might get differances like:
Finance dep
Fin. department
Finance department
Fin. dep.

Stuff like that, which is why you should use the combo [like] sollution....

The same might go for rank and or other fields in the table...

Besides the above (as allready meantioned by mile) theree is no real problem with having a record containing hundreds (if needed) fields. As long as they all are DIRECTLY related (dependant) upon your employee/Social#.

Regards

The Mailman

 

[bpaquette]

That's perfect, that's exactly how i have it. anything that has a standardized format is a menu box linked to another table (or just hardcoded in if its like 5-6 options).


thanks guys!

 

[namliam]

You should only hard code anything if its likely to stay the same for ages to come (> 5 years, like Male/Female) I mostly use tables myself. For some relatively simple listboxes i use 1 table, tblLists.

ID ShowingText Listbox

Instead of having endless one time one perpose tables...

Regards