Double Entry

[vandehey]

I have a sort of abstract question about relationships. Ok, say I have one database containing 2 tables. Each table has 2 fields. For example, say table1 has firstname, lastname. Table 2 has lastname, IDnumber. The tables are linked by last name.

Ok, here is the question. Say I create a new record in table1. Create the last name of Smith, first name Jen. If I want to give Jen an ID number, I have to create a new record on table2, with lastname = Smith and an ID number.

Is there anyway that I can assign an IDnumber to Smith without having to manually create a new record in both tables? Say I am viewing in table mode, looking at table1 with the fields firstname, lastname, and ::IDnumber. :IDnumber being the link to the other table. Is there anyway while on table1 I could create new record of say lastname = Jones, firstname = Pat, and put a 1002 in ::IDnumber and have this trigger a new record in table2 with lastname = Jones, and IDnumber = 1002?

I hope my illustration makes sense. Thanks for any suggestions!

[Fitch]

Set the related IDnumber field to the parent IDnumber field, and make the related name fields lookup based on IDnumber.

[LaRetta]

Tom gave you an exact answer to your question but I can't help but ask why you are considering 'double entry' here.

For example, say table1 has firstname, lastname. Table 2 has lastname, IDnumber. The tables are linked by last name.

Uh, what if you have more than one Smith? And why bother having an ID if you are linking them on LastName? In fact, why have two tables at all?

Say I create a new record in table1. Create the last name of Smith, first name Jen. If I want to give Jen an ID number, I have to create a new record on table2, with lastname = Smith and an ID number.

I'm afraid I'm missing the logic of what you are trying to accomplish. The new record and ID should be assigned in the table with the unique record; in this case, Table 1. And if your tables are linked on the ID, you don't need to list the LastName in both tables because they will be available through the relationship!?

Do you plan on having multiple Smith records in Table 2 - each with a different ID? If so, they would all match to Jen if joined on the LastName!!! I will make the assumption this is a 1:1 (one-to-one) relationship, ie, each record in table 1 will create a matching single record in table 2.

Is there anyway that I can assign an IDnumber to Smith without having to manually create a new record in both tables?

Sure. But you need to join on the ID instead and the ID should be generated in the table with the unique newly created record (table 1). In your scenario consider this: Join Table 1 to Table 2 on ID. Place your Table 2 LastName on Table 1 layout. Be sure 'Allow Creation' is clicked in your relationship. Create a new record in Table 1 and auto-enter a serial (uniqueID). Enter Jen in Table 1 FirstName, enter Smith in Table 2 LastName that is placed on the same layout. A new record will be created in Table 2 containing LastName Smith (because you typed directly into that field) and the ID assigned in Table 1 will automatically be inserted into the ID in Table 2.

Theory is this ... any time you cross-place non-key fields (any field from a related table except for the key ID) and type into one, a record will be created in the related table if it doesn't already exist which matches the ID in your main table. So if you have other fields in Table 2 that you can place on table 1 layout use them instead. This would allow you to keep FirstName and LastName together in table 1 (although it certainly isn't mandatory).

Have I thorougly confused you? Can you explain what you are trying to accomplish? I just think it can be much easier than you are making it and I would love to understand your thinking and help you with it.

LaRetta

[vandehey]

Thank you very much for the help. I understand that my example is severely flawed, and last name is in no sense unique. I was just using this as an example for an easy illustration. Since the tables were already linked, the key for me was to check the 'Allow Creation' box. That is exactly what I was looking for. Thanks again for the replies!

-Jeff

[Christian Coppe]

Hi LaRetta,

Only for your information : the "double data entry" is a procedure widely used in the pharmaceutical companies and related businesses, in order to build the clinical trials databases. If the data are entered manually, once has to use the double data entry (by two different persons), to compare the data togheter and to correct (by a thirth person...) if there are differences. Hard job, time consuming, and it is the reason why the concept of "Remote Data Entry" is developed now . The master of this game is the FDA, with the rules of the Good Clinical Practice. I reconize that this is a bit strange for the peoples not involved in this business...

HTH

Christian

[LaRetta]

Hello Christian,

Actually, 35 years in Administration has brought me in contact with double entry quite frequently. In the ole days it was known as 'blind' double entry and I STILL believe in using it when accuracy is critcal.

A hard job for sure. My first office jobs way back were performing such a task, except it was using punch cards. Anyone remember those? I probably date myself.

My concerns on this post revolved around double 'computer-generated' data which should always be questioned as unnecessary and redundant. That is the basis of relational database theory.

"I reconize that this is a bit strange for the peoples not involved in this business..."

Agreed. In fact, I had used such a process when using Approach (for a social service agency) and, when first starting with FM, posted just such a question here on forum. Many didn't grok it either.

LaRetta

[Ender]

Christian & LaRetta,

Now you've got me curious about this "Double Entry" system. Is the idea to have different people enter the same data into separate tables, then compare the records? Do you use common related tables to reduce the data entry? Is the comparing an automated process?

[Fitch]

Double Entry also has a specific meaning in Accounting parlance.

But I don't think that was the intent of the original question.

[LaRetta]

Hi Tom,

Yes, this subject title can be misleading. In fact, when I first saw it, I was excited because blind double entry still interests me and so does double-entry bookkeeping (Pacioli style).

Hi Mike,

[Ender]

Thanks LaRetta,

I'm not sorry I asked. It's a process i haven't come accross before so I was curious how it worked. Good explanation. Thanks!