How to populate gaps in my table

[fdsa]

Hello,

I have a problem which is baffling me. I have found very
tedious ways of resolving it, but was wondering if there
was an eaiser way.

I have a table with Julian dates and exchange rates, but
there are gaps in the dates. I am trying to populate the
gaps with the rates from the previous day. Any ideas?

eg.
table
Date (julian) Rate Currency
104005 1.35 CAD
104010 1.37 CAD

I would like to poulate the dates 104006-104009 with the
rate from 104005?
how do I do this

 

[Stephen Rasey]

Create a query A1qryYesterday
SELECT [julian]-1 AS JulianY, Exchange.Rate, Exchange.Currency
FROM Exchange;

the Append query
INSERT INTO Exchange ( Julian, Rate, [Currency] )
SELECT [E.Julian]+1 AS Julian1, E.Rate, E.Currency
FROM Exchange AS E LEFT JOIN A2qryYesterday AS Y ON E.Julian = Y.JulianY
WHERE (((Y.JulianY) Is Null));

Each run of the append query will fill one day in every gap.
Repeat the append query until you have filled the gaps.

Stephen Rasery
Houston
http://wiserways.com
http://excelsig.org

 

[Stephen Rasey]

One more thing. The table should haave a boolean field "GapFill" and the
Append query should check it Yes on the appended records. It is the only
way you can go back and find which records were guessed at.

These queries do not check the currency. You might have to do one currency
at a time.

Stephen Rasey

Create a query A1qryYesterday
SELECT [julian]-1 AS JulianY, Exchange.Rate, Exchange.Currency
FROM Exchange;

the Append query
INSERT INTO Exchange ( Julian, Rate, [Currency] )
SELECT [E.Julian]+1 AS Julian1, E.Rate, E.Currency
FROM Exchange AS E LEFT JOIN A2qryYesterday AS Y ON E.Julian = Y.JulianY
WHERE (((Y.JulianY) Is Null));

Each run of the append query will fill one day in every gap.
Repeat the append query until you have filled the gaps.

Stephen Rasery
Houston
http://wiserways.com
http://excelsig.org

Hello,

I have a problem which is baffling me. I have found very
tedious ways of resolving it, but was wondering if there
was an eaiser way.

I have a table with Julian dates and exchange rates, but
there are gaps in the dates. I am trying to populate the
gaps with the rates from the previous day. Any ideas?

eg.
table
Date (julian) Rate Currency
104005 1.35 CAD
104010 1.37 CAD

I would like to poulate the dates 104006-104009 with the
rate from 104005?
how do I do this

 

[Stephen Rasey]

This query will do multiple currencies at a time.

INSERT INTO Exchange ( Julian, Rate, [Currency] )
SELECT [E.Julian]+1 AS Julian1, E.Rate, E.Currency
FROM Exchange AS E LEFT JOIN A2qryYesterday AS Y ON (E.Julian = Y.JulianY
and E.Currency = Y.currency)
WHERE (((Y.JulianY) Is Null));

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org

One more thing. The table should haave a boolean field "GapFill" and the
Append query should check it Yes on the appended records. It is the only
way you can go back and find which records were guessed at.

These queries do not check the currency. You might have to do one currency
at a time.

Stephen Rasey

Create a query A1qryYesterday
SELECT [julian]-1 AS JulianY, Exchange.Rate, Exchange.Currency
FROM Exchange;

the Append query
INSERT INTO Exchange ( Julian, Rate, [Currency] )
SELECT [E.Julian]+1 AS Julian1, E.Rate, E.Currency
FROM Exchange AS E LEFT JOIN A2qryYesterday AS Y ON E.Julian = Y.JulianY
WHERE (((Y.JulianY) Is Null));

Each run of the append query will fill one day in every gap.
Repeat the append query until you have filled the gaps.

Stephen Rasery
Houston
http://wiserways.com
http://excelsig.org

Hello,

I have a problem which is baffling me. I have found very
tedious ways of resolving it, but was wondering if there
was an eaiser way.

I have a table with Julian dates and exchange rates, but
there are gaps in the dates. I am trying to populate the
gaps with the rates from the previous day. Any ideas?

eg.
table
Date (julian) Rate Currency
104005 1.35 CAD
104010 1.37 CAD

I would like to poulate the dates 104006-104009 with the
rate from 104005?
how do I do this