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:
Here's what I've done, which has nothing to address the date issue:
Any tips would be greatly appreciated.
Thanks,
Bill
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
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