Multiple Table Add/Update Records

[w8n]

Hi all!

I am having difficulty adding/modifying entries for a Helpers database that consists of the following structure:

Main Table_Helpers is contact info for all people in all areas:
HelperID Autonum pri key
HelperName Text
HelperAddress Text
…

Each AreaMembers tbl (Programs, Facility, Service, Recovery, more to add later) has a HelperID in a 1-to-1 w/ HelperID in Helpers ([Helpers.HelperID] <<< 1-to-1 >>> [XMember.HelperID]) and chkbxs and txtbxs for the various Services (Mowing, Auto Repair, Cleaning, around 12 per Area), available in that Area.

Table_FacilityMembers
HelperID number pri key
FacPaint chkbx
FacMow chkbx
…

Table_ServiceMembers
HelperID number pri key
SvcElec chkbx
SvcAuto chkbx
…

The AreaMembers tbls have a record related by HelperID to a Helper that is included in some Service(s) from that Area.
Not all Helpers have a member in each Area, (thus the LEFT JOINs below), but being able to update that later is necessary.

Form_Helpers includes controls for all Helper fields, as well as all Service fields from all AreaMember tbls, allowing quick checking of where a Helper is involved, and allowing quick entry of a new Helper in any Area/Service.

Helpers_Query is the Form_Helpers Record Source, attempting to connect all of these tables together for new record entry and dynamic updating, and includes the following SQL:

SELECT [Helpers].[HelperID] AS Helpers_HelperID,  Helpers.*,
 [ProgramMembers].[HelperID] AS ProgramMembers_HelperID, ProgramMembers.*,
 [FacilityMembers].[HelperID] AS FacilityMembers_HelperID, FacilityMembers.*,
 [ServiceMembers].[HelperID] AS ServiceMembers_HelperID, ServiceMembers.*,
 [RecoveryMembers].[HelperID] AS RecoveryMembers_HelperID, RecoveryMembers.*
FROM (((Helpers LEFT JOIN ProgramMembers ON [Helpers].[HelperID]=[ProgramMembers].[HelperID])
 LEFT JOIN FacilityMembers ON [Helpers].[HelperID]=[FacilityMembers].[HelperID])
 LEFT JOIN ServiceMembers ON [Helpers].[HelperID]=[ServiceMembers].[HelperID])
 LEFT JOIN RecoveryMembers ON [Helpers].[HelperID]=[RecoveryMembers].[HelperID]
ORDER BY ([Helpers].[HelperID]);

I can add a new Helper and AreaMember info using one LEFT JOIN Area. If I have more than one LEFT JOIN, new info can be placed in Form_Helpers, but using a “DoCmd.Close , , acSaveYes” from a command button won’t save new records in the tables.

If this isn’t the correct forum, please let me know so I can move it.

Are there some rules that I am not obeying to allow a multiple-table update?

Can anyone recommend a GOOD book for handling this type of relationship/SQL/Access/VB code manipulation?

If you need any other info, please let me know – I will give you whatever I can!


All help is appreciated in advance!

Bob

 

[KenReay]

Hi

I do not fully understand your datastructure, but alarm bells sound because:

Why do you have 1 to 1 relationships?, they are seldom needed

The table FacilityMembers looks suspiciously like it has repeating data, I would tend toward having a code for each service and this table would then have n reows per helper, with primae key of HelperId;ServiveId


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JeremyNYC]

One more alarm bell rang for me: you discuss having multiple AreaMembers tables. these should all be put into one table, with a field to indicate which records are associate with each ?area?.

My thought is that you'd get a lot out of doing some reading on normalization. I've got an article on my website, by Paul Litwin, called Fundamentals of Relational Database Design. There are tons of other great sources out there, too.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.