Table/Form design (2000SR1)

Topic

New Reply

I am setting up an input form for a new segment of an existing database and can

Reply | Quote

Replies

Hi Peter,
After chewing on this for a while, I would create a separate table where you listed the Category and the Specialties for each person. It would look something like
intClientFK Number The pointer to a Clients table record
intCategFK Number The pointer to a Category table record
intSpeclFK Number The pointer to a Specialties table record
with a primary key based on all three fields so you prevent duplicate entries. Each of the fields would be a long integer. This structure would allow you to create a simple subform that the data could be entered with, and the existing list of Clients would be available to start the process. The creation of the paper directory may be a bit more of a challenge, but you would only pick people who had one or more entries in the table. You might however need to resort to creating a temporary table to combine information, depending on the format of the report.

Reply | Quote

For the part of the database you refer to I actually have a table and a sub-table With category the main table and specialties the detail table rather like the typical Orders/Order Details a la Northwinds.

I wasn’t completely clear on my dilemma, though. This has to do with unique info at theClient level that will only be recorded for the directory. For example, Organizations give Founding dates, contact names, charitable status, etc and Individuals can record degrees and diplomas and Both types have the opportunity for Additional Information that will be recorded in a memo field – up to 40 words for free and up to an additional 100 words for a fee. All of the above are only recorded in the directory and are not part of the regular record keeping of the Arts Council.

With that additional information, then, should I just dump this stuff into the main clients table or is there a reasonable case for a sub table that has a one-to-one relationship.

In addition, if I use the main table, what is the best way to filter these directory listings? Just use a check box called “directory”? Certainly both safe and explicit as opposed to doing a query on one of the other fields that uses “not null” as a parameter.

If you think I should go the one-to-one route, how do I get it to hook in easily to the main table, which I have always found a bit of a hassle when adding this type of table to existing records.

Peter

Reply | Quote

Did you get this issue resolved? Somehow it slipped under my “unanswered questions” radar.

Reply | Quote

I decided to put the unique information in the client Main table. It was simpler than dealing with the one-to-one situation. The database isn’t so large that all the null fields are a problem. For data entry, I made a form that has the relevant client information, a subform that selects categories and a sub-subform where the details can be recorded. It seems to be working fine, so i am letting sleeping dogs lie as this is a volunteer activity!

Thanks for the inquiry.
Peter

Reply | Quote