Hello everyone,
I have a bit of a problem running a query that may either need some SQL code or may be completed in Design mode.
I have 6 fields: Deal_id, Product, Date_VL, VL, Date_Retro_Fees, Retro_Fees.
Basically each deal has an associated product. The price of this product changes either each day or each month depending on the product and is given a date date_vl and value VL at which it is priced (imagine a share for example that is valued on a daily/monthly basis).
Now, we charge a fee on the VL (current price) as a percentage. This percentage can change and is determined on a given fixed date. What I would like to do is produce a query that shows the VL and date VL from one fixed date up to the next where the percentage fee changes.
At the moment the query shows the VL and date VL in each instance of percentage of fee. I have attached a photo of what it currently looks like and what I would like it to look like. If anyone could provide any guidance it would be much appreciated!
Obviously I can say for part of the filter that Date_VL must be greater than or equal to Date_Retro_Fees, but this would still leave all VL records that are greater than Date_Retro_Fees, but carry on over into the next Date_Retro_Fees when the % has changed.
Can anyone help me?
I have a bit of a problem running a query that may either need some SQL code or may be completed in Design mode.
I have 6 fields: Deal_id, Product, Date_VL, VL, Date_Retro_Fees, Retro_Fees.
Basically each deal has an associated product. The price of this product changes either each day or each month depending on the product and is given a date date_vl and value VL at which it is priced (imagine a share for example that is valued on a daily/monthly basis).
Now, we charge a fee on the VL (current price) as a percentage. This percentage can change and is determined on a given fixed date. What I would like to do is produce a query that shows the VL and date VL from one fixed date up to the next where the percentage fee changes.
At the moment the query shows the VL and date VL in each instance of percentage of fee. I have attached a photo of what it currently looks like and what I would like it to look like. If anyone could provide any guidance it would be much appreciated!
Obviously I can say for part of the filter that Date_VL must be greater than or equal to Date_Retro_Fees, but this would still leave all VL records that are greater than Date_Retro_Fees, but carry on over into the next Date_Retro_Fees when the % has changed.
Can anyone help me?
