Help with table design

[Lukael]

Hi,

Let's say I have a table with list of names/surnames, and 2nd table for working titles which is joined table to master form and names/surnames table.



From second table is created a continous subform which has limit of records (I allready have code for that). First record in this subform should be "worker", and second record his "boss".

So, I have 1 subform, which needs 2 records from same name/surname table and differentiation of those records by some ID

How must I design 2nd table and It's subform, anyone knows?

I hope you understand me !!

Thanks for help !

[ranman256]

you would have 1 table for people, say tNames. Every person is in it, workers, bosses.

Table 2 would be the 'boss-worker' list of indexes. These would look at the tNames table and tell the structure.
tNAMES
------
[id], [firstn], [lastn]
123,john, smith
456, sam , jones

tWORKER
----------
[boss],[worker]
456, 123
989, 456



BUT thats not how you described it, maybe you just want the sub table to be simpler:
tWORKER
-----------
[parent], [job], [ID]
123, Boss, 456

But this way seems you are doing double entry in the subform

[Lukael]

Hi ranman,

I know that is quite odd to explain

Actual problem is that I have 2 subforms linked to master form records, and both subforms have list of names from same table.

One subform is for Administrator, and second for Technical Administrator. Both subforms must have 2 records (2 persons) in master form record, of which one is actual Administrator and second one his replacement (in case of sickness etc...).

That's why I need this, just don't know how to ask question

Table 2 would be the 'boss-worker' list of indexes. These would look at the tNames table and tell the structure.
tNAMES
------
[id], [firstn], [lastn]
123,john, smith
456, sam , jones

tWORKER
----------
[boss],[worker]
456, 123
989, 456

My subforms are continous forms. Can I use this solution with It, so that I will tell a difference between entered Administrators ? It is important to know whether person is replacement Administrator or actual Administrator.

[ssanfu]

Consider:

If you must have 2 records:

tblEmployees
-----------------
EmployeeID_PK (Autonumber)
FName (Text)
LName (Text)
(other fields about employee)

tblDuties
-------------------
DutyID_PK (Autonumber)
EmployeeID_FK (Long) (Link to tblEmployees)
RoleDesc (Text) (Primary/Secondary or Admin/Deputy or ... your names for the positions)
LastUpdated (Date/time) (<<-- to be able to know when assignments are outdated - can be defaulted)

[Lukael]

thanks ssanfu & ranman,

I think I understand what I need to do now.