Muliple Table data entry

[Accesstrep]

As a novice access user I am trying to develop a database for waterpoints, communities and associated data. There are a large number of linked tables with referentially integral joins. This situation must be a very common one. What is the best way to enter data into such tables?
Attached is a sample query which is an attempt to provide the basis of a form for data entry but it does not work.
I would be very grateful if someone could indicate where I should start and what is the logical way to proceed>
Many thanks

 

[Rabbie]

It would be helpful if you could post a copy of your Db rather than just a picture of the relationships.

 

[Accesstrep]

Thanks, Rabbie - your point is valid. A naive question - Is there a simple way to post a copy of the db without publishing the confidential data included in it?

 

[Accesstrep]

Answering my own question, here is a data-reduced version of the db in .mdb format. Grateful for your comments on solving the data entry problem with muliple linked tables joined with referential integrity.

 

[Alisa]

Which query is it and which tables are you trying to enter data in? Usually when entering data into more than one table, you end up with a main form and then a subform where you can enter data for the related table . . .

 

[Accesstrep]

Re: Multiple Table data entry

So far I have managed to enter data into Organizations and People tables with a people subform.
However a more complicated group for example is Community to Dowsing linked to Community Visit and Waterpoint and Team. Even making an effective entry form for Team and Team leader linked to People and using a combo box is so far beyond my reach.

 

[Alisa]

Form design is no simple matter, and it really takes a lot of trial and error to learn what works and what doesn't work. If you are really stuck, I would use the access forms wizard - it will at least get you started. Then, for the lookup fields, you can create a combo where the row source of the combo is based on the lookup table, and the control source of the combo is the field in your table that you are trying to fill in. Let me know if you have a more specific question and I will try to help.

 

[Accesstrep]

It seems unbelievable to me that a key part of relational database design - the many-to-many relationship of two tables through an intermediate junction table seems to become an arcane art for a beginner making a simple form to enter data in either main table!

As an example I have Communites, CommunityLivelihoods and Livelihoods as follows: CommID, CommName
and LivelihoodID, LivelihoodName
and the junction table: CommLivelihoodID,CommID(FK), LivelihoodID(FK)

There are about 20 livelihoods which I would like to use a combobox to select. One Community could have several Livelihoods.
I have made a query joining the junction table with the Livelihood table to create a subform listing the livelihoods.

Having struggled with this for some days, it would be great if someone could show me how this is done!

 

[Alisa]

If you are doing many to many data entry, you are right, it can be a little tricky. There are lots of ways to do it, and it really depends on how you want the process to go. One way is to have two list boxes (or two combo boxes) representing the data in the two tables that are related to the junction table. In your case, you would have one list for Communities and one list for Livelihoods. Then, you can highlight items from each list, and use a command button event to insert the appropriate record into the junction table based on what is selected in the two list boxes. If you search on the forum, there are some really good example databases using this technique that have been posted. Another way to do it is with drag and drop events, but that can be a little more complicated to implement.
HTH

 

[Accesstrep]

Could you point me to some good examples? The one that I have seen in the sample databases is clear on basic design but the detail of the transition from query to form is where I am having problems.

 

[Alisa]

http://www.access-programmers.co.uk/forums/showthread.php?t=153458

Disclaimer: I haven't actually worked with this particular example before, but it sounds like it might be relevant . .

 

[gemma-the-husky]

It seems unbelievable to me that a key part of relational database design - the many-to-many relationship of two tables through an intermediate junction table seems to become an arcane art for a beginner making a simple form to enter data in either main table!

when you say "either main table", this is probably where its catching you,
You cant really think of it in that way

The easist way is to think either in terms of the Communities, or in terms of the LIVELIHOODS, but not both at the same time.

so have a form for communities, and another form for livelihoods - pick a community, so one is active, and then display all the livelihoods, and pick the ones you want to match to the community - then pick another community, etc

or do it the other way from the point of view of the livelihoods

------
It is doable to manage both at the same time, but not as straightforward


Its not that a RDBS is hard per se, its just that you have to be rigorous with the data.