Referential Integrity and Data Entry

[muriel]

I'm sure this is very obvious to most of you. I have a database which will start out as purely data entry - there will not be any data to start with. I want to enforce referential integrity, but in the testing process, the users are trying to enter data, and they keep getting error messages that they can't enter records in one table without entering records in another table. How would they do both at once?

Thanks.

 

[RiverGuy]

It depends on how you want to implement it. Pretend you have Customers and Orders. You want to enter an order, but the customer does not exist yet. At the top of the form, you might have a combo box displaying the customers, or maybe you just want the Customer ID to be keyed in. Well, what I would do, is put a button next to the customer display that says "new customer." I would have it open up another formed that is used to create customer accounts. Upon closing the form, the first form is now ready to accept an entry on the newly created customer.

 

[muriel]

Thanks - that makes sense to me.

Followup question - how do I insure that the entries being made on separate forms (sending the data to separate, normalized tables) are being stored as one record? In other words, when I want to query or search a record, how does Access keep the entries as separate records? The record number seems to change with different forms linked to different tables.

Thanks.

 

[RiverGuy]

I'm really not sure what you mean. Can you elaborate a little bit? And also, are you basing your forms off of the tables themselves, or a query?

 

[muriel]

I'm trying to decide which would be better to do - base the forms off of queries or the tables themselves. I have a few many to many relationships, with join tables to make them one to many relationships.

To clarify my earlier question, I'll use an example from my database. I have bibliographic information that users will be entering via data entry forms. I'm trying to make the data entry process as simple as possible, but also trying to keep the tables separate and normalized. I have subforms, combo boxes, etc. in the data entry process. So for one record (containing author name, org name, title of article, publisher, city, etc.) the data is stored in many separate tables. When I go to query the data and I want to look at the bibliographic data, how does access keep the record straight, so that the data that needs to go together, stays together? In other words, so I'm not getting an author from one record mixed up with a city from another record. Does that make sense?

 

[jsteph]

muriel,
When you set up the Referential Integrity, you specify the linking fields, ie say AuthorID for the authors table, the Articles or Books table would also contain the Author ID of that article or book's author. Then if you were really normalized, say, and had a City table, then the CityID from the Authors table (which wouldn't hold the actual text name of the city) would link to the CityID in the City table, etc. etc.

As far as basing forms off queries or tables, I always use queries, since most likely you will end up wanting to filter or sort it in some way and it's better to do that via the underlying query than using the form's filter/sort functionality--those I only use for on-the-spot quick & dirty sorting and filtering. Additionally, you often wont need all of the fields from the table on the form, so a query can just select the needed fields--it's good practice to not throw 'the kitchen sink' at the form, especially if the data is on network--you'd be schlepping unnecessary data over the pipe.
--jsteph

 

[muriel]

Thanks for the responses - much appreciated.