Matching Update Query

[james28]

Hello!

I have an update query that works great if I specify the criteria manually; however, I would like for it to be a generic catch all, if possible.

Basically, what I want to happen is when the query runs, I want it to find all matching records.
For my example, we will say that there are 10 fields:
- Key (auto number)
- Parent Key (optional number field)
- Breed (text)


- Family info (text)
- 6 other fields are not related to this question.

I want the query to:
1) Find where Key = Parent Key.
2) Copy the Breed and Family info from the record where the key is and then duplicate it in the record where the Parent key matches.
3) The other 6 fields would not be touched at all.

Is there some way to do this? I realize that it would be better to split into a new table for the Parent, to normalize the data, but there is currently a limit and I can only use 1 table.



Thank you for any guidance that you can provide!

[June7]

What do you mean by 'limit'? Why must you use 1 table?

This might require the table to do self-join in the query.

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

[james28]

June,

Thank you for the reply. The issue is that the database is connected to infopath. Unfortunately, infopath can only communicate with 1 table at a time. To clarify, more than one table can be used in the database, but ultimately all of the data will need to be in 1 table.


Is there any way to do what I described by any chance, even if it is not the optimal way to do it? If not, would there be any other work arounds with the above mentioned constraint?

Thank you!

[June7]

Possible approach suggested in my previous post.

How is the data transferred to InfoPath? InfoPath has link to Access table?

Another approach is to have normalized tables for data entry/edit then use queries and/or code to create/update records in the table InfoPath links to.

Either way is the same result but the second might be simpler.

[james28]

June,

Sorry about that. For some reason, posting from my phone causes me to skip over text. It is not intentional!



As for your question, infopath does have a link to the access table. Users interact with the infopath forms and never touch the access database directly.


The database itself has a couple of tables, so there is no limit there. In this case, each infopath form interacts with a different table. I then have a few forms / reports in the access database which bring everything together, as needed.





Per your previous post, I have attached a sample copy of the database. To simplify things, I have deleted the 4 extra columns, so that there are only 2 extra rows of data.

Also, I created an extra table called "AfterQueryRunsMainTable", which shows how the data should look, once the query runs.


In the example:

"Key" is an autonumber.
"ParentKey" is an optional number.
"Breed" and "FamilyInfo" are text that I would like to have copied.
"Likes" and "Dislikes" are text that should remain exactly as they are. They should not be deleted or replaced.

Basically, any row that has a parentkey = to a key, should have the "breed" and "familyinfo" updated to what is in "key".


DatabaseUpdateQuery.accdb


Thank you very much for the help. It is very appreciated.

[June7]

The example tables seems contradictory to what you describe.

The After table shows empty fields. Whereas the main table shows those fields already populated. If you want a query to populate the After table fields:

Try:

UPDATE AfterQueryRunsMainTable INNER JOIN (SELECT Key, Breed, FamilyInfo FROM AfterQueryRunsMainTable) AS Query1 ON AfterQueryRunsMainTable.ParentKey = Query1.Key SET AfterQueryRunsMainTable.Breed = [Query1]![Breed], AfterQueryRunsMainTable.FamilyInfo = [Query1]![FamilyInfo];

[james28]

June,


Thank you for the reply. You are 100% correct. I seem to have failed in my naming of the tables in my sample database.


That works a treat!! Would there be any way to make it so that it does not keep updating all of the previous ones each time?


I tried this. I added a new field called "Updated", which is set to "Yes" when ever an update happens. This part works just fine.

I then tried to make it so that it would only work if "Updated" was <>"Yes". Unfortunately, that does not seem to work at all.


Any ideas? This is the sql view.

UPDATE AfterQueryRunsMainTable INNER JOIN (SELECT Key, Breed, FamilyInfo FROM AfterQueryRunsMainTable) AS Query1 ON AfterQueryRunsMainTable.ParentKey = Query1.Key SET AfterQueryRunsMainTable.Breed = [Query1]![Breed], AfterQueryRunsMainTable.FamilyInfo = [Query1]![FamilyInfo], AfterQueryRunsMainTable.Updated = "Yes"
WHERE (((AfterQueryRunsMainTable.Updated)<>"Yes"));

[June7]

Is Updated a Yes/No type field? If so, then don't include Yes within quote marks. If that doesn't work, then use True - not within quote marks.

Regardless of display formatting, the value of a Yes/No field is -1/0, not "Yes" or "No". True/False are Access/VBA constants. Do not put constants within quote marks.

Instead of having the Yes/No field, criteria could be:

Breed Is Null OR FamilyInfo Is Null

[james28]

June,

Thank you for the reply. I can see why that example would be confusing. "Updated" is a text field, not a yes/no. I just happened to pick yes as the text.


Your example of is null worked. Thank you! I also found that is null for the criteria on updated also works. I am going to do some testing to see which method gives the desired results in real life use.


Thank you again!