Solved SQL UPDATE Statement Help Needed

[EzGoingKev]

I have two tables.

Table1 is updated once a month. I have added a date field named "last_pull". I am attempting to populated that field with data from Table2.

Table2 has two fields - an autonumbered "ID" field and a "pulled" field. The pulled field is a record of all Table1's update dates. The most current date will have the highest ID value. Example of the data in the table:

ID Pulled
1 05/13/2021
2 06/18/2021
3 07/15/2021

In the example above I want to populate Table1's "last_pull" field with 06/18/2021 date that is tied to ID number 2.

I have tried a few iterations of this:

UPDATE Table1 SET last_pull = (SELECT pulled from Table2 WHERE ID = DMax("ID","Table")-1)

And I keep getting the "Operation must use an updateable query" error message.

 

[plog]

I don't understand. A lot of things.

1. If every record in Table1 is to have the same value for some field--then you don't need that field in that table. Your last pull data can exist somewhere else and just reference it instead of putting it in every record.

In the example above I want to populate Table1's "last_pull" field with 06/18/2021 date that is tied to ID number 2.

2. But 6/18/2021 isn't the latest date. What logic are you using to determine that 6/18/2021 is the value you want?

ID = DMax("ID","Table")-1)

3. That isn't guaranteed to be an ID value. The DMax will exist because its querying the table of actual values for the highest. But there's no certainty if you subtract 1 from it that that value is in your table. Someone screws up entering data for ID=7, deletes it then puts in ID=8; when you query and get ID=8 as the Max value and subtract 1 you've chosen a value that doesn't exist. Further, someone might put 6/1/2021 in after they put in 5/29/2021 so that your ID values don't sort the same as your Date values.


I suggest you post a better example with both tables to demonstrate exactly what you want--before and after.

 

[theDBguy]

Hi. First off, why are you trying to store a calculated value, which is typically not necessary? You can just create a query to show the data you need.

 

[EzGoingKev]

Hi. First off, why are you trying to store a calculated value, which is typically not necessary? You can just create a query to show the data you need.

The dates are stored as records in another table. How is it a calculated value?

 

[theDBguy]

The dates are stored as records in another table. How is it a calculated value?

I would consider it a calculated value, because you are looking it up from another table. In other words, if I understand it correctly, you are calculating the "last" pulled date based on the "largest" ID value.

By the way, are you sue the largest ID value will have the latest pull date? If so, are you saying that even if a smaller ID value has a later date value, you would still use the earlier date because it has the largest ID value?

 

[EzGoingKev]

I would consider it a calculated value, because you are looking it up from another table. In other words, if I understand it correctly, you are calculating the "last" pulled date based on the "largest" ID value.

By the way, are you sue the largest ID value will have the latest pull date? If so, are you saying that even if a smaller ID value has a later date value, you would still use the earlier date because it has the largest ID value?

When I hear the words calculated field I think of something else but as I am mostly self taught IDK if I have been thinking incorrectly all this time.

The date values in Table2 are added to the table by the code that refreshes the data in Table1. The ID field auto-populates. The latest date will always have the highest ID. The second highest ID will be last month's update date.

If there is a better way to do it then please let me know. I am not married to any part of this process. I just want a field that is populated with the last month's pull date.

 

[theDBguy]

When I hear the words calculated field I think of something else but as I am mostly self taught IDK if I have been thinking incorrectly all this time.

The date values in Table2 are added to the table by the code that refreshes the data in Table1. The ID field auto-populates. The latest date will always have the highest ID. The second highest ID will be last month's update date.

If there is a better way to do it then please let me know. I am not married to any part of this process. I just want a field that is populated with the last month's pull date.

Hi. If you're using an Autonumber field, it's not guaranteed to be sequential. Each time you add a new record, you might be getting a higher ID value. However, it's possible to get a lower or even a negative ID value at some point.

Depending on how you're adding the records to Table2, would it be possible to have an earlier date with a higher ID value? If not, then you can just find the latest date, instead of the highest ID.

 

[EzGoingKev]

Hi. If you're using an Autonumber field, it's not guaranteed to be sequential. Each time you add a new record, you might be getting a higher ID value. However, it's possible to get a lower or even a negative ID value at some point.

Depending on how you're adding the records to Table2, would it be possible to have an earlier date with a higher ID value? If not, then you can just find the latest date, instead of the highest ID.

You have more experience than I do but I have never seen an autonumber field not kick out the next sequential number from the last number it generated.

There would not be an earlier date with a higher ID value. I do not want the latest date, I want the second to latest date in the field. Using the 3 row example I posted above 07/15/2021 is this month's pull date. 06/18/2021 is last month's pull date. I need last month's pull date.

 

[theDBguy]

You have more experience than I do but I have never seen an autonumber field not kick out the next sequential number from the last number it generated.

There would not be an earlier date with a higher ID value. I do not want the latest date, I want the second to latest date in the field. Using the 3 row example I posted above 07/15/2021 is this month's pull date. 06/18/2021 is last month's pull date. I need last month's pull date.

Check out this web article about Autonumber fields. Let's hope this problem never happens to your projects.

Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates

Identifies why Microsoft Access assigns duplicate AutoNumbers or wildly inappropriate values for AutoNumbers, and explains how to correct a database that already has these problems.

allenbrowne.com

Try the following query against Table2 and let us know what you get.

SELECT TOP 1 T1.PullDate FROM (SELECT TOP 2 T2.PullDate FROM Table2 AS T2 ORDER BY T2.PullDate DESC) AS T1 ORDER BY T1.PullDate

 

[EzGoingKev]

Check out this web article about Autonumber fields. Let's hope this problem never happens to your projects.

Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates

Identifies why Microsoft Access assigns duplicate AutoNumbers or wildly inappropriate values for AutoNumbers, and explains how to correct a database that already has these problems.

allenbrowne.com

Try the following query against Table2 and let us know what you get.

SELECT TOP 1 T1.PullDate FROM (SELECT TOP 2 T2.PullDate FROM Table2 AS T2 ORDER BY T2.PullDate DESC) AS T1 ORDER BY T1.PullDate

OK thanks. I never such problems existed.

I ran the code and got the the latest pull date.

 

[theDBguy]

I ran the code and got the the latest pull date.

Just to check, is it the correct date that you wanted?

 

[EzGoingKev]

Just to check, is it the correct date that you wanted?

No, it is not.

I do not want the most current date. I want the date just prior to the most current date.

 

[theDBguy]

No, it is not.

I do not want the most current date. I want the date just prior to the most current date.

Just curious, did you use the one I posted or the one from your email?

I made a minor change to the query after I posted it. Did you see the additional ORDER BY clause?

 

[EzGoingKev]

I used the one you initially posted. That was what was there when I clicked on the link. I updated it and am getting the result I need.

So now how do I use it in an update statement?

 

[theDBguy]

I used the one you initially posted. That was what was there when I clicked on the link. I updated it and am getting the result I need.

So now how do I use it in an update statement?

Hi. Thanks for the update. I was just demonstrating how you can create a query to get the information you wanted. I still think it's not necessary to store that information again in another table, since you can always calculate for it.

 

[Pat Hartman]

I don't understand what you are trying to do so feel free to ignore me if my reply doesn't make sense.

When I import sets of data, sometimes I want to tag each row so I can identify what the source file was. If you NEVER, EVER have to consider the possibility of two imports on the same day, you can use the current date as the tag or even an assigned date. Add an unbound textbox to your import form. Let it default to today's date or if you can calculate the date you want then calculate it. Then rather than importing the file directly, link to it instead. You would create an append query that selects the rows and columns from the linked table and adds a RefDate column that pulls the value from the unbound textbox on the form.

In reality, I always use defensive programming so I am not inclined to believe in "NEVER, EVER" So I create a log table that includes the file name as well as the date and assigns an autonumber to give it a unique ID. Then when I do the import, it is the autonumber I use as the reference field rather than the date.

 

[arnelgp]

you need to make 3 steps:

1. create a query that will return records (excluding the record with Max(ID)), call it Query1:
SELECT Table2.ID, Table2.PULLED
FROM Table2
WHERE (((Table2.ID)<>DMax("ID","TABLE2")));

2. create another query (Query2) based on Query1 to just return the Last "Pulled" date:
SELECT TOP 1 Query1.PULLED
FROM Query1
ORDER BY Query1.ID DESC;

3. finally, do an update using Table1 and Quey2:
UPDATE TABLE1, QUERY2
SET TABLE1.LAST_PULL = QUERY2.PULLED;

 

[EzGoingKev]

@arnelgp - that is pretty much what I did.

The data that I am importing has a date field denoting when the data was either added or changed. It is changed due to errors. I needed the previous date so I can see what has been added or changed since the last update.

Thanks everyone for your help.