Running to append inquiries and one update query

[Hdata]

As I was previously typing my question it appears that my post went away. Maybe else timed out will try again.

Appending data from a single table into two tables in a one to many relationship. All is well appending to these tables, my problem occurs when I try to run an update query, in order to update the record number into the foreign key field of the second table.
Seems the update query deletes what I previously appended to that second table. Also, I can run the second append query again and that wipes out what my update query appended to that foreign key field in the second table. Any help, thank you

 

[moke123]

what methods are you using and what is the sql of the queries?

 

[Hdata]

Using Access 2013; Method: Insert

Source Table: [TestData-AA]
tblLandSales is the primary table in a one to many relationship (Primary key field LandSalesID)
tblTransferData is secondary table (Primary key field fk_LandSalesID)

Append Query 1

INSERT INTO tblTransferData ( DeedBook, DeedPage, DeededAcres, DeedDate, Subdivision, DeedPrice )
SELECT [TestData-AA].DeedBook, [TestData-AA].DeedPage, [TestData-AA].DeededAcres, [TestData-AA].RecordingDate, [TestData-AA].Subdivision, [TestData-AA].CashEquiv
FROM [TestData-AA]
WHERE ((([TestData-AA].CheckBox2)=True));

Append Query 2

INSERT INTO tblLandSales ( SaleDate, CashEquiv )
SELECT [TestData-AA].SaleDate, [TestData-AA].CashEquiv
FROM [TestData-AA]
WHERE ((([TestData-AA].CheckBox2)=True));

Update Query

UPDATE qryLandDataDeed RIGHT JOIN tblLandSales ON qryLandDataDeed.LandSalesID = tblLandSales.LandSalesID SET qryLandDataDeed.fk_LandSalesID = [qryLanddataDeed]![LandSalesID];

 

[The_Doc_Man]

I see something suspicious and will try to explain my suspicions.

Your Append Query 2 creates new records in tblLandSales. Those records will contain SaleDate and CashEquiv as two fields. ANYTHING ELSE in that table will be null or will be a default value if you supplied one in the table definition.

Your descriptive text before the queries and the details of the Update Query both tell us that tlbLandSales has (at least) a third field called LandSalesID but Query 2 doesn't set any values for that. So I suspect that you must have warnings turned off when you run this or your would get key violations on the PK of a table being null when appending data.

In essence, your problem is that your Query 2 doesn't appear to correctly identify the records it wants to update so they don't get updated correctly (or perhaps they don't get updated at all.)

 

[Hdata]

Thanks for the reply Doc Man

Something else I can add is the way I launch the append queries and the update query with DoCmd.OpenQuery

(Append 1)
DoCmd.OpenQuery "qryAppendBuncoToLandSales", acViewNormal, acEdit

(Append 2)
DoCmd.OpenQuery "qryAppendBuncoDataToDeedRecords", acViewNormal, acEdit

(Update)
DoCmd.OpenQuery "Update fk_LandSalesID-LandSalesID", acViewNormal, acEdit

I do not know how to turn warnings on or off I don't believe I use that feature.

No additional records added. The primary key in the second table cannot be edited, however foreign key can be edited and whenever I run the update query after the second append query, that action erases the append to data, and then inserts the correct foreign key number. If I run the second query again, that erases the foreign key number and then inserts the append 2 data.

 

[The_Doc_Man]

OK, what I am saying is that if you use LandSalesID as the PK, the INSERT queries and the UPDATE queries have to refer to it in some way or else you have an uncontrolled query that will affect EVERY record (for the update). For you to say that something gets updated incorrectly? I agree and would predict all sorts of inconsistencies occurring, based on what little I can see. The method of launching your queries almost doesn't matter. (Almost.) The text of the SQL is what is going to make or break this process.

Neither append query 1 nor append query 2 references LandSalesID. Is it perhaps an autonumber field in the tblLandSales? Or does it come from a third table?

You say tblTransferData is related to tblLandSales. Is there a formal relationship?

I ask that because if you have a formal relationship and then run Append Query 1 before you run Append Query 2, you should be getting warnings (that you need a record in the parent table before you can add a record in the child table.)

If you are not getting errors on the append queries, then I have to assume that you have no formal relationships between the tables AND that the keys in the dependent table are not properly set up to allow you to use Access Relational Integrity as a way to protect you from what you are apparently doing right now. Given that there are no references to LandSalesID in EITHER query, one of those two queries MUST be wrong.

How else can I say it? Something is missing from your queries - a reference to LandSalesID (whatever name you actually use for it).

That is your prime key in tblLandSales and a foreign key in tblTransferData. Then in the UPDATE query we learn that there is ANOTHER query using LandSalesID as the basis for the JOIN. But you don't specify the LandSalesID in either of the append queries. If you don't use the LandSalesID in the append to the dependent table, in essence you are not telling it on what record it should depend. You leave it dangling.

I also have to think that you aren't telling us the process completely, since your description tells me you must be erasing something manually. Otherwise, you would ALSO be complaining about duplicate records.

Step away from the curb and tell us IN ENGLISH what you are trying to do. AVOID USE OF SQL/ACCESS ACTIONS! Tell us the problem in terms of your business model.

 

[Hdata]

I want to add whatever I think might help?

OK, I have a formal relationship set up in the relationships window whereas tblLandSales has a 1 to many relationship with the child table tblTransferData.

Query 1 inserts (Saledate, Cashequiv) into tblLandSales from a third table called TestData-AA, creating the initial record in the parent table including auto number field LandSalesID which is a primary key.

Query 2 inserts (DeedBook, DeedPage, etc) into tblTransferData creating a second record (1st record n tblTransferData) with primary Key DeedDataID, and a fk_LandSalesID which references the primary key from the parent table tblLandSales.

Query 2 does not know what the primary key from tblLandSales is, which is why I use the update query. Maybe this is my problem.

I'm not saying I'm correct, obviously I'm not getting the results I'm looking for. I Might add that the update query includes a join of tblLandSales and qryLandDataDeed which includes all records from tblLandSales and only those records from qryLandDataDeed. Maybe this is redundant and identified in the SQL statement.

 

[Hdata]

Thanks again, during the past several weeks I've tried multiple things and what I'm showing you is the closest I've gotten to what I'm looking for. At this time, my best solution is to eliminate the update query, look at the tblLandSales in order to see what the auto number field shows, and manually type that into the fk_key field in the second table, and that gives me what I want. However, it sure would be nice if I get right.

 

[Hdata]

In layman terms, if I could get the update query to update only the foreign key field without deleting the other info in that record provided by query 2, that would be it. Life would be great again.

 

[The_Doc_Man]

Good - query 1 uses an autonumber in tblTransferData, which is perfectly fine.

Good - query 2 has an autonumber for its own primary key in tblLandSales, which is acceptable and perhaps even useful (though I can't actually answer the "useful" question without knowing more).

Open to question - The presence of the FK in tblTransferData.

Query 2 does not know what the primary key from tblLandSales is, which is why I use the update query. Maybe this is my problem.

Almost certainly this is your problem. This is just my way of thinking, but if you don't know the correct FK to use for query 2 then you are not ready to use query 2 at all. You have this third query that participates in a JOIN on the LandSalesID field - but if you COULD do the join on that field in the first place, there is no need to do a SET. That is the part that is confusing. If you actually CAN do the JOIN on that ID field, you are saying (to yourself if no one else) that you already KNOW the ID in both tables so that the JOIN can work.

Am I making myself clear here? It is a complicated concept and is somewhat obfuscated by the multiple tables and queries that are involved.

Here is a test for you as food for thought:

Take that UPDATE query you showed us earlier that does the SET. Make the SELECT equivalent of that JOIN to see what it actually shows you. I'm not fully convinced, given your description, that ANYTHING is being updated. The way to test that is to see if anything would have been SELECTed.

 

[The_Doc_Man]

In layman terms, if I could get the update query to update only the foreign key field without deleting the other info in that record provided by query 2, that would be it. Life would be great again.

As the followup - this muddies the waters considerably since it SEEMS from the description that you shouldn't be changing ANY records after the fact. You might create more records (because you said it is one-to-many), but none of them should change. If you change the FK, how do you know to what you should change it?

 

[Hdata]

You raise a very good issue. I did not realize that the foreign key should not be changeable. I should look at other examples of 1 to many field relations and see how they work. Thanks, that might be the answer. A field that can not be accessed.

 

[Hdata]

WELL DOC MAN, I BELIEVE YOU JUST SOLVED MY ISSUE & THANK YOU VERY MUCH! I will need to look into this foreign key issue. When I was creating the system, I would create a table and add a field and make it a number format. Name it fk_LandSales and in the relations window drag the primary key field from the parent table to the foreign key field in the child table. I just made a quick look in one of my access text books, and it mentions a lookup field.

I believe I will need to go back to the drawing board and redesign my tables. I cannot thank you enough, this is a good time to do that. WOW

You can tell your grand kids that you solved a problem for me on fathers' day that I did not even know I had! Hope you and your family have a great day!

 

[The_Doc_Man]

Beware of lookup fields. They can cause incredible headaches down the road. They have their uses, but as you become more and more accustomed to queries and relationships, you find that their presence has oddball side effects.

Search this form for "LOOKUP FIELDS" to see what others have reported. I never use them myself.

On the other hand, going back to the drawing board to redesign the tables is not wrong, even if you find that you will keep most of what you did and maybe only change a few things slightly. It would help you gain/retain confidence that you are on the right track.

 

[Hdata]

Doc Man, you have raised a question for me. If you don't use lookup tables what do you use. I guess you do it in some form of SQL?
I am the only user of my database so that helps.
This would be the right time for me to look into an alternative.

and Thanks again.

 

[The_Doc_Man]

A lookup table implies a relationship between the table having a lookup field and the table holding the data being "looked up." I just use foreign keys.

I don't want to confuse you about FKs. They are NOT immutable. Their permanence depends on your design.

Simplest case: The field contains some type of code to represent a status. If the situation changes, you change the status. A one-to-many relationship exists between that field and the table holding the translation of the status. Thus, you can print out a word for the status despite it actually being stored as a number.

Other end of the spectrum: The field contains an FK that links a transaction to a particular property. You have a many-to-one relationship between the transactions and the property. You ONLY change the FK if you realize you incorrectly linked it to the wrong property.

PKs never change. FKs change depending on the design, but even the strictest case allows FKs to change when needed to correct an error.

When you are ready to insert a record into a child table and the record contains an FK, you must make it a point to know the corresponding PK from the parent table. It is OK to have to do a couple of lookup operations first, perhaps with DLookup, perhaps using a .FindFirst or .Seek if a recordset is involved. But when doing an INSERT into a table, you should be ready to write every field that the table NEEDS.

If you have optional fields for supplemental data, it is OK to have no immediate values for such things. However, when the field is structural (like an FK) rather than descriptive (like, say, a number of square feet), you need to know that field's value before you store it in the table.

We have not actually discussed whether you are comfortable with the topic of database normalization. If you are still a little "iffy" on that topic, NOW is the time (before you do that redesign) to study the subject. There are many resources in this forum and on the web in general. However, for general web searches, remember to look for DATABASE NORMALIZATION. If you leave off the word "database" then you also find out about mathematical normalization and diplomatic normalization. Searching this forum, you won't find too much on those extraneous topics.

One final question: You are going back to the drawing board for a redesign. But where did the inventor of drawing boards go before he got it right?

 

[Hdata]

Well Mr. Doc Man you certainly have a great knowledge of this topic and I sincerely respect your efforts. I will do a considerable amount of research of what you have just told me. My primary occupation has been commercial real estate valuations, originally coast-to-coast industrial properties with American Appraisal Company out of Milwaukee. However today I stay local as possible. This database thing has really attracted my interest with computer technology, generally focused with Microsoft suites. I use the database specifically for assisting my report writing. In this last step I've taken with regards to append queries is to download information online from government agencies and commercial entities rather than have to type it. That makes my work palatable thank you.

So here's to you and maybe we'll talk again in the future.

 

[The_Doc_Man]

We'll all be here. Come back any time for directions, suggestions, and other help such as we can offer. I took up most of your thread, but the others here are perfectly good sources of help too.