query

[igg via AccessMonster.com]

table1:
k1 k2 k3
xbcd 6 8
cfgr 8 9
xrty 9 4

table2:
k1
bcd
cfgr
xnyu

need to extract rows from table1 in which k1 is like table2 k1 using inner
join (preferably) or/and other way also. should look like:

table3:
xbcd 6 8
cfgr 8 9

 

[Dale Fye]

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 = T2.K2

 

[John Spencer]

You can only set up this type of non-equi join in SQL view
Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*"

Or you can do the same thing with a cartesian join (which is probably less
efficient)
Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
, Table2 as T2
WHERE T1.K1 Like "*" & T2.K1 & "*"

The cartesian join can be set up in the standard query design view
== Add both tables
== DO NOT JOIN the tables
== Add the fields you want to see from Table1
== Under the K1 field enter the criteria
Like "*" & T2.K1 & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[igg via AccessMonster.com]

can't try it for now, would ON T1.K1 = T2.K2 allow rows like "xbcd 6 8"
from table1 to be in output eventhough table2 k1 column is "bcd"?. "xbcd" and
"bcd" are not equal but the row "xbcd 6 8" needs to show in the
output.

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 = T2.K2

----
HTH
Dale

table1:
k1 k2 k3

[quoted text clipped - 14 lines]

xbcd 6 8
cfgr 8 9

 

[Dale Fye]

Oops! That should have been

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 = T2.K1

 

[Dale Fye]

John,

Good catch!

I just made the assumption that the OP wanted a match query, and failed to
pick up on the fact that Table1 K1 = "xbcd" and in table2, K1 = "bcd", and
that the OP stated "k1 is like table2 k1"

 

[igg via AccessMonster.com]

table1:
k1 k2 date time
xbcd 1 dt1 tm1
xbcd 3 dt2 tm2
xryf 6 dt1 tm1
yrff 8 dt1 tm1
yrff 6 dt2 tm2

table2:
k1
bcd
yrff

output should return max date and time (table1.k1 like table2.k1):
table3:
k1 k2 date time
xbcd 3 dt2 tm2
yrff 6 dt2 tm2

Oops! That should have been

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 = T2.K1

----
HTH
Dale

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1

[quoted text clipped - 23 lines]

 

[Dale Fye]

Take a look at Johns, code.

Mine did not account for the [K1] values in table2 being subsets of the
values in [K1] of table 1. Then just add the date and time fields.

BTW, you would probably be better off if you had a single date/time field
rather than one for the date and another for the time.

----
HTH
Dale

table1:
k1 k2 date time
xbcd 1 dt1 tm1
xbcd 3 dt2 tm2
xryf 6 dt1 tm1
yrff 8 dt1 tm1
yrff 6 dt2 tm2

table2:
k1
bcd
yrff

output should return max date and time (table1.k1 like table2.k1):
table3:
k1 k2 date time
xbcd 3 dt2 tm2
yrff 6 dt2 tm2

Oops! That should have been

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 = T2.K1

----
HTH
Dale

Select T1.K1, T1.K2, T1.K3
FROM Table1 as T1

[quoted text clipped - 23 lines]

xbcd 6 8
cfgr 8 9

 

[igg via AccessMonster.com]

i thought , i did a query like :
Select T1.K1, T1.K2, Max(T1.date) as maxdate, max(T1.time) as maxtime
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*"
group by T1.k1

output was like:
xbcd 1 dt1 tm1
xbcd 3 dt2 tm2
yrff 8 dt1 tm1
yrff 6 dt2 tm2

i try again.

Take a look at Johns, code.

Mine did not account for the [K1] values in table2 being subsets of the
values in [K1] of table 1. Then just add the date and time fields.

BTW, you would probably be better off if you had a single date/time field
rather than one for the date and another for the time.

----
HTH
Dale

table1:
k1 k2 date time

[quoted text clipped - 31 lines]

 

[John Spencer]

So now you want to return the record with the maximum of the Date and Time for
the match.

One method uses a correlated sub-query that would look something like the
following.

SELECT T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*"
WHERE CDate(T1.[Date] + T1.[Time)) =
(SELECT Max(CDate(Temp.[Date] + Temp.[Time)))
FROM T1 as Temp
WHERE Temp.K1 = T1.K1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[igg via AccessMonster.com]

this code works fine for small tables. With some relatively large tables it
takes time or hangs up.

So now you want to return the record with the maximum of the Date and Time for
the match.

One method uses a correlated sub-query that would look something like the
following.

SELECT T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*"
WHERE CDate(T1.[Date] + T1.[Time)) =
(SELECT Max(CDate(Temp.[Date] + Temp.[Time)))
FROM T1 as Temp
WHERE Temp.K1 = T1.K1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

table1:
k1 k2 date time

[quoted text clipped - 14 lines]

xbcd 3 dt2 tm2
yrff 6 dt2 tm2

 

[John Spencer]

Well your data is part of the problem.

First thing would be to save the Date and Time in one field. It is easy to
split out just the date or just the time. If you have both in one field you
can use an index on the field to speed up the search.

Another thing you can do is use two queries.

QueryOne: Get the latest date and time for each K1 value. Save the query as
QLatest. If you are able to limit the date range then add that to your query
with a WHERE clause and index the date field.

SELECT K1,
Max(CDate([Date] + [Time])) as LastDate
FROM Table1
GROUP BY K1

Again this would be much faster if you had the date and time in one field and
indexed. Also add an index on the K1 field. It should speed up the join
between table 1 and the query.

SELECT T1.K1, T1.K2, T1.K3
FROM (Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*")
INNER JOIN qLatest as Q
ON T1.K1 = Q.K1
AND CDate(T1.[Date] + T1.[Time]) = Q.LastDate

Beyond that I can't think of any way to make the query faster. UNLESS the
time is not significant - you don't have more than one entry for K1 on any one
date. Then you could drop the time from the query. And you would index the
date field.

Good luck

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

this code works fine for small tables. With some relatively large tables it
takes time or hangs up.

So now you want to return the record with the maximum of the Date and Time for
the match.

One method uses a correlated sub-query that would look something like the
following.

SELECT T1.K1, T1.K2, T1.K3
FROM Table1 as T1
INNER JOIN Table2 as T2
ON T1.K1 Like "*" & T2.K1 & "*"
WHERE CDate(T1.[Date] + T1.[Time)) =
(SELECT Max(CDate(Temp.[Date] + Temp.[Time)))
FROM T1 as Temp
WHERE Temp.K1 = T1.K1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

table1:
k1 k2 date time

[quoted text clipped - 14 lines]

xbcd 3 dt2 tm2
yrff 6 dt2 tm2