How to populate gaps in my table

  • Thread starter Thread starter fdsa
  • Start date Start date
F

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
 
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
 
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


Stephen Rasey said:
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


fdsa said:
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
Click to expand...
Click to expand...
 
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


Stephen Rasey said:
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


Stephen Rasey said:
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


fdsa said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top