Re: A subquery? Not sure if this is doable?

Hi,


That depends on how many records you have to deal with, and if there are
indexes.


If you are sure there is only one record per month in the lessFrequent
table, you can try:

SELECT a.date, a.title, a.data, b.data
FROM moreFrequent As a LEFT JOIN lessFrequent AS b
ON a.title=b.title AND a.date >= b.date AND Year(a.date)=Year(b.date)
AND Month(a.date) = Month(b.Date)


Hoping it may help,
Vanderghast, Access MVP


"kohai" <kohai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CFF299F-4C4F-4CDB-9915-416EB22DB8D2@xxxxxxxxxxxxxxxx
Michel,

Thanks for the speedy reply. I tried to use the SQL example you wrote and
my computer hangs and hangs and hangs.

This example should be returning each weeks data point for each company
and
the previous month-ends data point in the other table such that the
monthly
point will repeat for each week until a new month starts?


"Michel Walsh" wrote:

Hi,

SELECT a.date, a.title, LAST(a.data), LAST(b.data)
FROM (moreFrequent As a INNER JOIN lessFrequent as b
ON a.title=b.title AND a.date >= b.date) INNER JOIN lessFrequent
As
c
ON a.title=c.title AND a.date >= c.date
GROUP BY a.date, a.title, b.date
HAVING b.date =MAX(c.date)



should do. I hope you have indexes on title and date fields.

For a given a.date, a.title, b values are about all records with a date
less or equal to a.date.
For a given group, a.date, a.title, b.date, c values are about all
records
with a date less of equal to b.date. MAX(c.date) gives us the closest
one,
<= a.date. so, keeping b.date=MAX(c.date) limit the records from the
JOINs
to those that correspond to the closest b.date <=a.date


Hoping it may help,
Vanderghast, Access MVP


"kohai" <kohai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C8E57ED-61CE-43A4-A1F2-5706AAAC866D@xxxxxxxxxxxxxxxx
Hi,

Any help out there that the masters can shed some light on would be
greatly
appreciated.

I have tables where data is stored based on different
frequencies(Daily,
Weekly, Monthly). The primary keys are the Date and the Company ID.

What I am trying to figure out is whether there is any way to pull data
from
a table of greater date frequency and have the data from the table with
less
frequency such that the table with less frequency will repeat its value
until
its next point in time.

I know that joins would bring back only where the values are equal and
I
don't want to have to store the data of shorter frequency on a more
frequent
basis since it will be repeating and violate cardinal rule of db
design.

For Example:

More frequent:
1/5/05 ABC 5.25
1/5/05 DEF 2.41
1/5/05 XYZ 9.82
1/12/05 ABC 4.22
1/12/05 DEF 3.12
1/12/05 XYZ 8.88
....
2/1/05 ABC 4.25
2/1/05 DEF 6.41
2/1/05 XYZ 10.82
2/8/05 ABC 7.22
2/8/05 DEF 1.12
2/8/05 XYZ 9.99

Less Frequent:
12/31/04 ABC 52.5
12/31/04 DEF 31.9
12/31/04 XYZ 44.4
1/31/05 ABC 50.5
1/31/05 DEF 30.9
1/31/05 XYZ 49.4

My goal would be to have the weekly data point divided by the most
recent
month end point for each week.

1/5 05 ABC 5.25 / 52.5
1/5/05 DEF 2.41 / 31.9
1/12/05 ABC 4.25 / 52.5
1/12/05 DEF 3.12 / 31.9
2/1/05 ABC 4.25 / 50.5
2/1/05 DEF 6.41 / 30.9
2/8/05 ABC 7.22 / 50.5
etc.........

Is something like this feasable with some type of subquery? Do I need
a
table where these repeating dates are set up?

Your help is greatly appreciated.

Thank you.

kohai





.

Relevant Pages

  • Re: A subquery? Not sure if this is doable?
    ... the weekly series and the repeating monthly figure. ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: A subquery? Not sure if this is doable?
    ... my computer hangs and hangs and hangs. ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: A subquery? Not sure if this is doable?
    ... FROM (moreFrequent As a INNER JOIN lessFrequent as b ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: A subquery? Not sure if this is doable?
    ... Weekly data +1 mil rows ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: Classes as units of reuse
    ... Component ABC { ... The thing that is not clear in your pseudo code is the nature of the relationship between ABC and DEF. ... IMO, the second approach would be the best where one abstracts some set of objects with the proper functionality and then decouples their details from the rest of the application via a Facade interface to which the clients all talk. ... In the OO paradigm one basically has three levels of logical indivisibility: subsystem, object, and responsibility. ...
    (comp.object)