Make query

[ardi]

Hi All,

I have problem with query acces. I have created query from acces query
design. But I got difficult to implemented this query to vba macro.


SELECT tabel2.tgl, tabel2.num, tabel1.dates,
abs(tabel2.tgl-tabel1.dates) AS ddif
FROM tabel2 INNER JOIN tabel1 ON tabel2.site = tabel1.site;

this query i save with name query1

SELECT query1.dates, min(ddif) AS [min]
FROM Query1
GROUP BY query1.dates;

that query i save with name query2

how to implemented that query to vba macro. tabel2 should be variable.

(tabel2 must be changed to tabel1,tabel2, tabel3 etc...) ?

Thanks

Ardi

 

[Guest]

You can use a subquery in SQL like this:
SELECT dates, min(ddif) AS [min]
FROM
(SELECT tabel2.tgl, tabel2.num, tabel1.dates as dates,
abs(tabel2.tgl-tabel1.dates) AS ddif
FROM tabel2 INNER JOIN tabel1 ON tabel2.site = tabel1.site)
GROUP BY dates

And, because you don't use tgl or num in your results you could leave them
out:
SELECT dates, min(ddif) AS [min]
FROM
(SELECT tabel1.dates as dates, abs(tabel2.tgl-tabel1.dates) AS ddif
FROM tabel2 INNER JOIN tabel1 ON tabel2.site = tabel1.site)
GROUP BY dates

You can go into MSQuery (Data... Get External Data... New Database Query)
and from the MSQuery window (not the Query Wizard!) press the "SQL" toolbar
button. You should be able to manually type (or copy/paste) the SQL command
into the SQL window. Then return the data to Excel. This gives you the
basic query for tabel2.

To change the table name being used: you can set up a macro that alters the
QueryTable.CommandText. The following example will assume that the table
name you wish to use is in Cell A1 on Sheet "Sheet1":
Sub RunQuery(TableName as String)
Dim OriginalSQL as String, NewSQL as String
With Worksheets("SheetName").QueryTables(1)
OriginalSQL = .CommandText ' Store the original SQL statement with
'tabel2'
' Now modify the SQL command to use the table specified in Sheet1 cell A1:
NewSQL = Replace(OriginalSQL, "tabel2", Sheets("Sheet1").Range("A1").Text)
.CommandText = NewSQL
' Refresh the query:
.Refresh
' Restore the SQL so it again refers to 'tabel2';
' Otherwise we cannot easily find and replace the table name next time
it is run
.CommandText = OriginalSQL
End With
End Sub