table relationships

[msprick]

I am unable to build a data entry form that will accept data. (The form is based on a query since I want to show some calculated fields.) I think my problems stem from incorect relationships but unfortunately I can't figure out a way that works any better. I'm hoping someone will spot what I can't.

I am designing a brand new database so I have NO historical data to look at. Basically a client comes in and is evaluated for eligibility for various programs. I have three main tables CLIENT (data that will rarely change), STATUS (data that is specific to this point in time and is expected to change), and INDIVIDUALS (data about people in the household). I have many lookup tables (which all funtion as they should) feeding these three main tables.

A CLIENT may have many STATUS' (1 to many).

I am uncertian what the relationship to the individual table should be. While the all of the fields are contained in the client table, the database is really about progressing a client through many different status'. The number and age of the individuals impacts on their poverty level, program eligability, and is needed for reporting to the various grantee and governmental organizations. Since the number and age of the individuals impacts status then should the relationship be 1 STATUS to many INDIVIDUALS? When I try to build a data entry from (from a query) using a subform; I can add test data to the CLIENT table but am 'unable to update recordset' when working with the STATUS table. (I thought to make INDIVIDUALS a subform of STATUS however since the STATUS subform won't work I never got that far.)

I have been asked to add 'shortcuts' to the STATUS table to make completing the INDIVIDUAL table optional rather than mandatory. As such, I can then say that a CLIENT can have many INDIVIDUALs in their household (1 to many). Unfortunately I haven't been able to get that design to work either.

Any help would be greatly appreciated.

TIA

Michael

 

[Steve Schapel]

Michael,

It seems to me that there is properly a one-to-many relationshup between
Clients and Statuses. There is also a one-to-many relationship
between Clients and Individuals. There is no discernable relationship
at all between Individuals and Statuses.

 

[msprick]

Steve,

Thank you.

With a CLIENT STATUS (1 to many join at ClientID, referential integrity enforced with cascade update and delete) design I have built a data entry form based on a query (selected fields from CLIENT and STATUS tables along with some calculated fields). I can add data to the CLIENT table fine but when I try to add data to the STATUS subform I recieve the message: 'This Recordset is not updateable.' (A change of the record source to the STATUS table rather than my query still yields the same message).

STATUS has 5 tables that I used the Lookup Wizard for. All those relationships are 1 to many with the STATUS being on the many side. My drop down choices (combo boxes?) work fine except that none of the choices are written back to the STATUS table.

From a search of this board I thought the reason I recieved my message 'This Recordset is not updateable' was poor relationships. So what am I still missing?

Michael

 

[Steve Schapel]

Michael,

The problem is probably caused by the calculated fields in the query.
Please post back with the SQL view of the query, so we can talk
specifics. I don't quite follow some parts of what you said... since
you mention a form and subform, I assume we are talking about 2 queries,
one for each form? And it sounds like the one the subform is based on
that is the problematic one? So where does the query built from both
tables come into it?

 

[MSprick]

Steve

I appologize for my slow response. I've been out of town and off the 'net

You also answered another question of mine which lead me to redefine my relationships between tables. I thought I'd have more success starting from scratch than trying to get everything correctly changed. Turns out when I did that I no longer had my problem

Thanks for you insights

Michael

 

[Steve Schapel]

Michael,

Thanks for the feedback, and I am happy to know you have got it sorted.