Update query based on dates

Texas Longhorn

Active Member
Joined
Sep 30, 2003
Messages
493
Hi all,

I'm new to Access and I am trying to do an update query that i could use some help with. I have two tables: [HLT Data] and [CStat Data]. In [HLT Data], I am trying to update the field Debt to be the [CStat Data].[DebtMM] value where two conditions are met: (1) The unique company identifier in each table matches, and (2) [CStat Data].DataDate is the largest date that is less than or equal to [HLT Data].NewDate.

What I've done is started an Update Query where I have linked the unique company identifier between the two tables. However, I'm not sure how to deal with the date criterion. The [HLT Data] table has monthly fields, whereas [CStat Data] is quarterly. I'm trying to associate the correct quarterly datapoint from [CStat Data] with each monthly date in [HLT Data]. As an example:

Code:
[HLT Data].NewDate          [CStat Data].DataDate         [CStat Data].DebtMM
6/30/96                           4/17/96                             50
7/31/96                           7/19/96                             60
8/31/96                           7/19/96                             60
9/30/96                           7/19/96                             60
10/31/96                         10/16/96                            55
Here's what I've done, which has nothing to address the date issue:

Code:
UPDATE [HLT Data] INNER JOIN [CStat Data] ON [HLT Data].PERMNOLink = 
[CStat Data].NPERMNO SET [HLT Data].Debt = [CStat Data].DebtMM;

Any tips would be greatly appreciated.

Thanks,
Bill
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I would do this in a few steps (you can later go back and nest the code if you want to make it all one query).

1. Make a Select Query similar to what you have already, but add the following criteria under [CStat Data].DataDate:
Code:
 <=[HLT Data].NewDate

2. Create a new a new query based on the first query, that is an Aggregate Query that returns only the latest (most recent value) of [CStat Data].DataDate (Group By your other field(s)).

3. Use this query in an Update Query to update your values.
 
Upvote 0
Joe4 - Thank you for the reply. I'm having a bit of trouble implementing your solution (I've been using Access for about a month, so I'm very unfamiliar with the lingo). On step 1, I switched from Update Query to Select Query. I maintained the link between the two tables for the unique company identifier. I then dragged all of the fields from [CStat Data] down to the query builder, and placed the criteria you give under the DataDate column. This gives me a lot of records...about 45 million. I'm not exactly following what the purpose of this first query is...any help with understanding this would be great.

On the second step, I'm afraid this is beyond my Access understanding. I looked up Aggregate Query in the Help files, but I'm still unsure of how to implement this step (i.e. do I do this in the SQL window; what fields should I group by, etc?).

To give a bit more info (which may or may not be helpful), each table has about 30 fields (with some overlap). In [CStat Data], I have about 400,000 records, which consist on average of a few dozen dates (4 per year, but not exactly quarter-end dates) per unique company (of which there are several thousand). [HLT Data] has a little more than a million records which consist of monthly data points for thousands of companies.

Thanks for your help,
Bill
 
Upvote 0
You use an Aggregate Query to return SUMs, COUNTs, MINs, MAXs among other functions of a certain group of records. Most intorductory Access books will teach you how to do this. I searched the web, and I found what appears to be a free on-line tutorial on how to do this (I haven't downloaded the free tutorial myself, so I can't vouch for how good it is):
http://www.599cd.com/tips/access/aggregate-query/?key=AllExperts

The point of the first query is to exclude all the dates that do not meet your date criteria. Once you have all the possible records you need, you can perform the Aggregate Query on them (using the MAX function on the date). You only need to return all the pertinent fields in this query (fields you want to Group By and the date field you want to use).

If you are still running into problems, can you post a more detailed example? Like, post a small sample of each table with all pertinent fields, and then your expected output (you have a field fields in your SQL code that are not displayed in your small data sample, so I am not quite clear on the exact details of what you are doing).
 
Upvote 0
Joe4,

Thanks again for your help and your patience. The tutorial was helpful. I am still a bit confused, though I believe I've made progress. Before getting to my remaining questions/clarifications, here's a little better example (I hope):

First, the two tables:
[CStat Data] contains quarterly financial information (e.g. sales, debt, income) for many companies over many years. Pertinent fields would be:
PERMNO (unique company identifier), DebtMM (debt in millions of dollars), qtenddt (quarter end date - for example 6/30/97), and DataDate (the date the quarterly variable was actually publicly released, for example 8/4/97).

[HLT Data] contains monthly data (e.g. market capitalization, stock price) also for many companies over many years. The included companies between to two tables are similar, and the date range is basically 1970 through 2007. Pertinent fields would be:
PERMNO (unique company ID), NewDate (month end dates), Debt (a currently unpopulated field).

I'm basically trying to populate the Debt field in [HLT Data] with the date-appropriate DebtMM value from [CStat Data] (by date-appropriate, I mean the most recent DataDate that is less than or equal to NewDate). Here is an attempt at an example:

Current [CStat Data]
Code:
PERMNO              qtenddt                   DataDate                  DebtMM
001                    3/31/97                   4/20/97                    50
002                    3/31/97                   5/15/97                    1000
001                    6/30/97                   7/22/97                    70
002                    6/30/97                   8/14/97                    1100
001                    9/30/97                   10/20/97                   60
002                    9/30/97                   11/18/97                   1150

Current [HLT Data]
Code:
PERMNO             NewDate                  Price                        Debt
001                   4/30/97                   10
001                   5/31/97                   12
001                   6/30/97                   11
001                   7/31/97                   12
001                   8/31/97                   12
001                   9/30/97                   13
001                   10/31/97                 14
001                   11/30/97                 13
002                   4/30/97                   40
002                   5/31/97                   42
002                   6/30/97                   41
002                   7/31/97                   42
002                   8/31/97                   42
002                   9/30/97                   43
002                   10/31/97                 44
002                   11/30/97                 43

Desire end-result (in [HLT Data]):
Code:
PERMNO             NewDate                  Price                    Debt
001                   4/30/97                   10                        50
001                   5/31/97                   12                        50
001                   6/30/97                   11                        50
001                   7/31/97                   12                        70
001                   8/31/97                   12                        70
001                   9/30/97                   13                        70
001                   10/31/97                 14                         60
001                   11/30/97                 13                         60
002                   4/30/97                   40                         
002                   5/31/97                   42                        1000
002                   6/30/97                   41                        1000
002                   7/31/97                   42                        1000
002                   8/31/97                   42                        1100
002                   9/30/97                   43                        1100
002                   10/31/97                 44                         1100
002                   11/30/97                 43                         1150

I've definitely made some progress thanks to Joe4, but I have the following remaining problems/clarifications:

In step one, to clarify, my Select query should have fields from both tables, correct? That is, in query design view, I have both tables. They are linked only by PERMNO. I have three fields in the design pane: [CStat Data].PERMNO, [CStat Data].DataDate, and [HLT Data].NewDate. I don't need the debt fields here, correct? As instructed, in the criteria field under [CStat Data].DataDate I have <=[HLT Data].NewDate. I have saved this query as AggregateStep1.

In step two, I use only AggregateStep1 to build my query...is this correct? From AggregateStep1, I have dragged down all three fields to the design window. PERMNO and NewDate have "Group By" in the Total field, and DataDate has "Max" in the Total field. I have saved this query as AggregateStep2. It appears to correctly associate the "date-appropriate" DataDate with each NewDate. My output here is simply PERMNO, DataDate, and NewDate.

I next have a step three (I'm diverging from Joe4's post here, as I ran into some problems). Here I do another select query. In the design view, I am using AggregateStep2 and [CStat Data]. I have these two linked by PERMNO and DataDate/MaxOfDataDate. I have dragged down these four fields: [AggregateStep2].PERMNO, [AggregateStep2].MaxOfDataDate, [AggregateStep2].NewDate, and [CStat Data].DebtMM. This query basically appends the correct DebtMM to AggregateStep2. I have saved this query as AggregateStep3.

Finally, I have an update query that I'm trying to do in design view. I use [AggregateStep3] and [HLT Data], linking these on PERMNO and NewDate. The field to update is [HLT Data].Debt, which I try to update to [AggregateStep3].DebtMM. Unfortunately, when I try to run this query, I get the error "Operation must use an updateable query." I have googled this error and not found any solution that I understand.

So...in addition to the handful of questions/clarifications I've just posed above, I would like to know two things: (1) Does my above four-step process make sense/should I alter it? and (2) How do I overcome this error I'm receiving in Step Four?

Thanks for your patience and help,
Bill
 
Upvote 0
OK, I am a little squuezed for time, so I am not able to go into as quite as much detail as I would like, but I have the code you need to do what you want.

First, it might not be necessary to actually update the values back to the original table, since you can return what you would like in a query. I can do this in a two-step process:

Query1:
Code:
SELECT [CStat Data].PERMNO, [HLT Data].NewDate, Max([CStat Data].DataDate) AS MaxOfDataDate
FROM [CStat Data] INNER JOIN [HLT Data] ON [CStat Data].PERMNO = [HLT Data].PERMNO
WHERE ((([CStat Data].DataDate)<=[HLT Data]![NewDate]))
GROUP BY [CStat Data].PERMNO, [HLT Data].NewDate
ORDER BY [CStat Data].PERMNO, [HLT Data].NewDate;

Query2:
Code:
SELECT Query1.PERMNO, Query1.NewDate, [HLT Data].Price, [CStat Data].DebtMM
FROM (Query1 INNER JOIN [HLT Data] ON (Query1.NewDate = [HLT Data].NewDate) AND (Query1.PERMNO = [HLT Data].PERMNO)) INNER JOIN [CStat Data] ON (Query1.MaxOfDataDate = [CStat Data].DataDate) AND (Query1.PERMNO = [CStat Data].PERMNO)
ORDER BY Query1.PERMNO, Query1.NewDate;
This should return what you want, except for the 4/30/97 value for 002, as that has no corresponding value. Many times, if you can get what you want in a query, it is not necessary to actually physically write the value back to the original table.

If, however, you do want to write it back to the original table, I was able to do this through the use of a Temporary Table (Update Queries do not like to use Aggregate Queries). We would still use Query1 above, then here is the code for the query to create the Temporary Table:

Query3:
Code:
SELECT Query1.PERMNO, Query1.NewDate, [HLT Data].Price, [CStat Data].DebtMM INTO TempTable1
FROM (Query1 INNER JOIN [HLT Data] ON (Query1.PERMNO = [HLT Data].PERMNO) AND (Query1.NewDate = [HLT Data].NewDate)) 
INNER JOIN [CStat Data] ON (Query1.PERMNO = [CStat Data].PERMNO) AND (Query1.MaxOfDataDate = [CStat Data].DataDate)
ORDER BY Query1.PERMNO, Query1.NewDate;

Lastly, we can use this Temporary Table to update our original "HTL Data" table:

Query4:
Code:
UPDATE [HLT Data] 
INNER JOIN TempTable1 ON ([HLT Data].PERMNO = TempTable1.PERMNO) AND ([HLT Data].NewDate = TempTable1.NewDate) AND ([HLT Data].Price = TempTable1.Price) 
SET [HLT Data].Debt = [TempTable1]![DebtMM];

This should do what you want (I verified the results match what you posted).

If you paste the SQL code into the SQL window of the query, you can then switch to Design View if that helps to see how these were built.

Hope this helps. There may be a more efficient way of doing this, but this is what I came up in my limited window of time.
 
Upvote 0
Great! Glad we got it to work!
 
Upvote 0
Joe4 - Just a quick follow up (every thing is still working wonderfully...this is just a curiosity). In the WHERE line of Query1, why do you use an exclamation point after [HLT Data] instead of a period?

Thanks again,
Bill
 
Upvote 0
You know, I never noticed that before. I used Query Builder to write it, so I didn't write the SQL straight up (if I did, I would have used a period also). I tried changing it to a period, and it still worked fine. So both seem to work.

Odd, maybe it is one of those Access quirks...
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top