Joining 2 table

[ardi]

Hi,

I have problem to make query for joining 2 table,
first table (tblA), holds num data which associate with date, while
second table
have date with interval 1 day and field site

for example table wich will be joined ,
tblA TblB
date num site date2 site
1/1/1990 6 x 1/1/1990 x
1/4/1990 7 x 1/2/1990 x
1/9/1990 9 x 1/3/1990 x
1/11/1990 6 x 1/4/1990 x
1/13/1990 11 x 1/5/1990 x
1/15/1990 8 x 1/6/1990 x
etc..
would return from joining table, like :
date2 num site date
1/1/1990 6 x 1/1/1990 * date2 must be closest
(date2-date1 minimum)

1/2/1990 6 x 1/1/990 to date 1
1/3/1990 6 x 1/1/1990
1/4/1990 7 x 1/4/1990
1/5/1990 7 x 1/4/1990
1/6/1990 7 x 1/4/1990
....
1/14/1990 11 x 1/13/1990
1/15/1990 8 x 1/15/1990

note i have created query for that but it running very-very slow, do
you have any better way for
this ?

thanks
ardi

 

[Vincent Johns]

Hi,

I have problem to make query for joining 2 table,
first table (tblA), holds num data which associate with date, while
second table
have date with interval 1 day and field site

for example table wich will be joined ,
tblA TblB
date num site date2 site
1/1/1990 6 x 1/1/1990 x
1/4/1990 7 x 1/2/1990 x
1/9/1990 9 x 1/3/1990 x
1/11/1990 6 x 1/4/1990 x
1/13/1990 11 x 1/5/1990 x
1/15/1990 8 x 1/6/1990 x
etc..
would return from joining table, like :
date2 num site date
1/1/1990 6 x 1/1/1990 * date2 must be closest
(date2-date1 minimum)

1/2/1990 6 x 1/1/990 to date 1
1/3/1990 6 x 1/1/1990
1/4/1990 7 x 1/4/1990
1/5/1990 7 x 1/4/1990
1/6/1990 7 x 1/4/1990
...
1/14/1990 11 x 1/13/1990
1/15/1990 8 x 1/15/1990

note i have created query for that but it running very-very slow, do
you have any better way for
this ?

thanks
ardi

I don't know why your Query is running slowly, but you didn't list the
SQL of your Query in your message, so it's difficult to determine.

Here's one way to set up your Query (perhaps not the best, but maybe
someone else can suggest an improvement).

I assume your [tblA] and [tblB] contain the following records:

[tblA] Table Datasheet View:

date num site
--------- --- ----
1/1/1990 6 x
1/4/1990 7 x
1/9/1990 9 x
1/11/1990 6 x
1/13/1990 11 x
1/15/1990 8 x

[tblB] Table Datasheet View:

date2 site
--------- ----
1/1/1990 x
1/2/1990 x
...
1/15/1990 x

Incidentally, I hope that these names are only for your illustration --
the actual Table names should probably reflect what they contain.

I actually defined two Queries. The first one identifies the correct
dates from [TblA].

[Q_1Max] SQL:

SELECT tblB.date2, tblB.site, Max(tblA.date) AS MaxDate
FROM tblB INNER JOIN tblA ON tblB.site = tblA.site
WHERE (((tblA.date)<=[tblB]![date2]))
GROUP BY tblB.date2, tblB.site
ORDER BY tblB.date2;

[Q_1Max] Query Datasheet View:

date2 site MaxDate
--------- ---- ----------
1/1/1990 x 1/1/1990
1/2/1990 x 1/1/1990
1/3/1990 x 1/1/1990
1/4/1990 x 1/4/1990
...
1/14/1990 x 1/13/1990
1/15/1990 x 1/15/1990

The other Query ascertains the value of [num] for the selected date in
[tblA] and displays it. If there are duplicate date values in [tblA],
then additional (probably unwanted) records would be displayed, so you
might want to constrain [tblA] to have unique values in the
[tblA].[date] field.

[Q_2Links] SQL:

SELECT Q_1Max.date2, tblA.num, Q_1Max.site,
Q_1Max.MaxDate AS [date]
FROM Q_1Max INNER JOIN tblA
ON Q_1Max.MaxDate = tblA.date
ORDER BY Q_1Max.date2;

.... and the results look like this:

[Q_2Links] Query Datasheet View:

date2 num site date
--------- --- ---- ----------
1/1/1990 6 x 1/1/1990
1/2/1990 6 x 1/1/1990
1/3/1990 6 x 1/1/1990
1/4/1990 7 x 1/4/1990
1/5/1990 7 x 1/4/1990
1/6/1990 7 x 1/4/1990
1/7/1990 7 x 1/4/1990
1/8/1990 7 x 1/4/1990
1/9/1990 9 x 1/9/1990
1/10/1990 9 x 1/9/1990
1/11/1990 6 x 1/11/1990
1/12/1990 6 x 1/11/1990
1/13/1990 11 x 1/13/1990
1/14/1990 11 x 1/13/1990
1/15/1990 8 x 1/15/1990

Incidentally, field names like [date] are not very informative; I
suggest changing it to something more suggestive of what it means.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[ardi]

Thanks for your suggest, i have try that query but when i running the
query, it's still slow
because my table have large row number more than 90000 row. Can u have
any idea ?

 

[Vincent Johns]

Thanks for your suggest, i have try that query but when i running the
query, it's still slow
because my table have large row number more than 90000 row. Can u have
any idea ?

Perhaps you need to add an index on any field that you use for looking
up values.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.