forms and subforms

[phineas629]

I have the following tables.

• person
• client
• counselor
• service

A person can be either a client or counselor. There are service sessions where clients and counselors meet. How should I set up my SERVICE form in order to display client's name and counselor's name? It's easier to use names rather than the number filing system I've used in their respective tables. I'm trying my best to normalize my database as it will grow. I just don't want to cut out the person table and have personal info in a client table as well as a counselor table.

Thanks in advance

[TheShabz]

Is there any extra info that is held in the client and counselor tables or are you merely using them to differentiate between the two? If the former, we would need more info as to what exactly you want done on your Service form. If the latter, then you can simply create 2 extra fields, IsClient, and IsCounselor, and use them as indicator fields, making your form pretty straightforward.

[phineas629]

Thanks for replying,

My database is pretty basic so far. Eventually I will have more tables for our Nurses, Management, Janitors, etc.

In regards to my current tables, the extra info in both tables are numbering systems unique to clients(i.e. 03266666) and counselors (i.e. AAA6456660). These numbers have real-world significance in our place of work.

The problem is that I will only see the unique numbers and not the people's names once I design the forms. You can see how troublesome it is to remember names vs numbers. Ha ha.

Thanks again

[TheShabz]

If that's the only problem then you need only create a query that shows both ID and the name (from the main table) then bind the form to that query, instead of the table. Now you have both to choose from.

[phineas629]

THANKS TheShabz