many-to-many relationships and user-friendly data entry forms.

[ryansupak]

hi, i'm attempting to create an extremely user-friendly data entry form for a data processing department with minimal 'computer knowledge'. (also worth noting is that i'm pretty new to Access.)

ive created a form that displays records from a corresponding table. this works fine for the 95% of fields that are guaranteed to have only one piece of data in them. i plan to have the data processing people use Tools|Data Entry to put the data in. however, there are a few fields that may have one piece of data per record, or they may have 20 pieces of data per record.

i figured out how to create a many-to-many relationship via a junction table. so, as far as my data structure is concerned, it's no problem to have an arbitrary number of child records linked to one parent record (or for that matter, the vice-versa of that). the trouble is finding a good way to make this entry process user-friendly.

i asked the data processing people if they thought they could handle going into datasheet view, entering the child data in to the child table, then going back to the parent table and establishing the link by typing in the child data once again. they said they strongly doubt that they could handle that without making a lot more errors than it's worth.

so what id like to do seems simple enough in concept: to have the data entry field accept multiple pieces of data for the fields that need it. im assuming this is fairly frequently done, so i'd like to avoid reinventing the wheel if possible. any advice on how to do this would be greatly appreciated.

thanks in advance for helping a newbie,
rs

 

[JPeters]

RS,
I don't know if I followed your e-mail incorrectly, but if not ...
So you've got your DB setup ... you've got 2 tables, 1 for each individual record, and 1 table for all those 'sub-records'. The sub-table is going to end up being much larger because it will contain many records, per 1 record of the main-table (hence, one-to-many). Now you've got the one-to-many relationship setup, right? You've got the database to function properly when entering data in this one-to-many sytle, and you've tested it to make sure that the data is consitent and that it is all being saved.
What I gathered is that you are looking for an easy way for Users of your database to enter data. Now if you refer to the first line of my post, I'll continue - I'm trying not to give you advice that you already know.
Whenever I have a one-to-many relationship in a database and I want easy data entry, I use Sub-forms. You can create a form based off of your main-form and then use the Sub-form wizard in the Form-Design toolbar to make a subform that will have the child/parent fields setup already (so the data remains consitent -am I spelling consistent right?). Basically the subform will sit in the main form and you can scroll through "many" records on the subform per single or "one" record on the main form, thus making the data entry simple as 1, 2, 3.
If you need more advice, or help - just keep posting... or if you feel adventurous (I always try doin this before posting) go up to the advanced search feature and take some time to search some keywords in forums. You'll be surprised how many people have had your exact problem before and have recieved excellent help from the Tek-Tips community. Goodluck!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[LittleSmudge]

You describe a many to many relationship and a linking table - which is all good and fine - but nowhere do you justify why on earth you NEED a many to many relationship


If your main table has lots of records which will all need ONE entry per record - then fine, they belong in the main table.

If some of the fields could have none, one or many entries - then they need to be in a separate table. - And a JPeters has said a subform is, by far, the most user friendly solution to that problem. I'll not repeat his solution.
But I'll add that if you have two such fields in the main table and they are not linked together in a meaningful relation then DO NOT be tempted to stick them in the same sub table - You'll need as many subtables and as many subForms as you have unique / discrete data packages to store.
( Don't be daunted by this - it can still look need and it's easy enought after you've done the first one. )

Let us know the actual relationships you have and we can help further if you're not clear.


'ope-that-'elps.

G LS

 

[ryansupak]

well, the database is to the end of documenting the ownership, size, and location of land parcels. (eventually the records will be merged with a relational geographic database system called ArcInfo, so im trying to keep that in mind as i do this as well.)

the tricky part is: land parcels can be split, morphed, and merged in all sorts of bizarre ways. thus, the field that i need to be the "many" in the "one-to-many relationship", is the "parent account(s)" for a given parcel. there could be one or 1,000 parent accounts for a single parcel, and one or 1,000 parcels could come from a single parent account. thus, the parent account numbers have their own table, while all the single parcel data is in another table.

also, i have been careful to keep discrete entities in discrete tables (my instinct tells me to do that, i guess).

this all being said, do subtables still sound like the best, most robust, way to go?

thanks again guys/gals,
rs

 

[JPeters]

Yes,
Sub tables will hold all the "many" part of the 1 to many relationships. And you will steup Subforms that link to these subtables to be embedded within your Mainform(maintable). So you'll have the Main Form, and you will have some subforms within it, you can have a number of different subforms and (if you arrange it correctly) you can still have a clean looking form.

You make it clearer as you define yourself. I would probably go with SubForms/SubTables and link them by parent/child fields with one-to-many relationships. You'll get the hang of it! Stay with us, post if you need more help.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[zambezibill]

Ryan,

If you decide to stay with the previously discussed many-to-many table structure, then Josh's suggestion on the form-sub form design is where you need to go.

However I am going to toss out another suggestion for table structure, that may be easier to maintain and under the circumstances should remain normalized.

Set up one table for all land parcels. Then set up a 2nd table just to maintain the parent-child relationships. This way land parcels only have to entered once in one table. THe relationsship table can simply track any variety or depth of parent-child relations. Then you can use a recursive query to extract the needed info.

I think that a conventional many-to-many table design may end up being very difficult to maintain because of the variety and flexibility of relationships you apparently will have, and because those relationships may be several levels deep (child-parent-parent-parent).

Bear in mind that with complex data structure, if you get it wrong now, you are going to have grief for a long time to come. Happy thoughts.

Cheers, Bill

 

[LittleSmudge]

the tricky part is: land parcels can be split, morphed, and merged in all sorts of bizarre ways.

Yes - tell me about it.. .. I have two projects on the go that have exactly the same problem ( If farmers put as much effort into growing crops as they seem to put into wheeling & dealing plots of land we could feed the world from this small island alone ! )

also, i have been careful to keep discrete entities in discrete tables (my instinct tells me to do that, i guess).
AND it is a fundimental part of rlational database theory - KEEP that instinct - its a good one.

this all being said, do subtables still sound like the best, most robust, way to go?
"subTables" ??? No - SubFORMS.
AVOID subTables ( Lookup-Fields ) like the plague
SubTables are nothing to do with relational databases - they are simply included in Access because someone at MicroSoft thought it would be a good idea to add a clever looking function to give WOW factor to inexperienced users - whilst making life hell for professional programmers.

For the full horror story - follow the link to thread700-295181


thanks again guys/gals
How do you know my middle initial ?

G(a)LS

 

[JPeters]

Gals (nice),
When I use SubTables - I just make them regular designed tables and put all the gooey info in them, I keep the Main Tables short and simple... Then link them through a one-to-many (or whatever relationship) manually. I didn't even know that Microsoft had a "SubTable" Feature that you could use. I just thought that we all made our own. ;-)
Your post confused me a bit, because I don't know how I would be able to organize my database without my current structure of a main table that links to other more specified data tables (the subs). I suppose I could setup a huge string of filters, but it seems easier and more pratical to stick with my "sub tables" - if I can still call them that. Thanks guys.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[LittleSmudge]

Your structure is fine JP

The tables on the MANY end of the relationships in your description are NOT what I'm talking about.


Access has introduced A97 ( or maybe A95 ) something called 'Lookup fields' which some people refer to as subTables because the kind of act on tables in the same way as subforms act on forms. - When you look at a raw table ( from the ONE end of a relationship ) in datasheet view, you get the table that is on the many end of a relationship appearing as a combo box within the datasheet.
It looks quite flashy at first - until you spend hours trying to debug a bit of code that is working on the table - only to find that the table is lying to you and the data that you see on the screen is not actually in the table after all.

Follow the link above for a fuller understanding.

Be at peace with your design though, you're fine with what you're doing - just don't call them subTables.

G LS