complicate query?

[hoachen]

Tab1: Item#1, Item#2
Tab2: Item#1, Title, actor, year

I used inner join to join Tab1.Item#1 and Tab2.Item#1 with a filter on Title
(like "dvd*) this return all result have dvd at the beginning of the title
(like dvd Monster House, dvd Speedy Zone). Then based on these results, I
want to look at the same table (tab2) that have the same title (maybe look up
to 50 lenght of the title) but does not have dvd in front of it (like Monster
House, Speedy Zone) and these title without dvd have it own item#, but the
actor and the year will be same as with the dvd. The final result should be
like Item#1 dvd Monster House, Item#other for Monster House, Item#1 dvd
Speedy Zone Item#other for Speedy Zone. I tried under tab2 ("ebk " &[Title]
and mid([Tab2].[Title], 4,50)) and it does not work. Will it be easy to show
tab2 two times. tab2 and tab2_1?

I been trying it for week now but can't figure out.

 

[Daryl S]

Hoachen -

I think this will do what you want, selecting all titles starting with dvd,
and all other titles starting with the same name without the dvd in front..

SELECT Tab2.[Item#1], Tab2.[Item#1], Tab2_2.[Item#1], Tab2_2.[Item#1]
FROM Tab2, Tab2 AS Tab2_2
WHERE Tab2.[Title] like "dvd*"
AND Tab2.actor = Tab2_2.actor
AND Tab2.year = Tab2_2.year
AND Tab2_2.title like Trim(Right(Tab2.title,len(Tab2.title)-4) & "*";

 

[hoachen]

Yes, it is what i need as i respected. Is there away to search for the title
with "dvd*" but some title that end of it have the (cs). When i use like
"dvd*" +
Tab2_2.title like Trim(Right(Tab2.title,len(Tab2.title)-4) & "*" the title
that with dvd Monster house (cs) will not come up (not all title only some
but i want this kind of title to display as well). I tried to put the like
"dvd*" or like "*(cs). it did not find anything when i did that. Any idea?

Thank you very much for you help.

Hoachen -

I think this will do what you want, selecting all titles starting with dvd,
and all other titles starting with the same name without the dvd in front..

SELECT Tab2.[Item#1], Tab2.[Item#1], Tab2_2.[Item#1], Tab2_2.[Item#1]
FROM Tab2, Tab2 AS Tab2_2
WHERE Tab2.[Title] like "dvd*"
AND Tab2.actor = Tab2_2.actor
AND Tab2.year = Tab2_2.year
AND Tab2_2.title like Trim(Right(Tab2.title,len(Tab2.title)-4) & "*";

--
Daryl S

Tab1: Item#1, Item#2
Tab2: Item#1, Title, actor, year

I used inner join to join Tab1.Item#1 and Tab2.Item#1 with a filter on Title
(like "dvd*) this return all result have dvd at the beginning of the title
(like dvd Monster House, dvd Speedy Zone). Then based on these results, I
want to look at the same table (tab2) that have the same title (maybe look up
to 50 lenght of the title) but does not have dvd in front of it (like Monster
House, Speedy Zone) and these title without dvd have it own item#, but the
actor and the year will be same as with the dvd. The final result should be
like Item#1 dvd Monster House, Item#other for Monster House, Item#1 dvd
Speedy Zone Item#other for Speedy Zone. I tried under tab2 ("ebk " &[Title]
and mid([Tab2].[Title], 4,50)) and it does not work. Will it be easy to show
tab2 two times. tab2 and tab2_1?

I been trying it for week now but can't figure out.

 

[Daryl S]

Hoachen -

You may want to look at your data and see if there are other characters or
spaces that could get in the way; you may need some code to get around that.
Is your data consistent (e.g. "dvd Movie House" versus "dvd Movie House"
versus "dvd Movie House " versus "dvd Movie House" each have the same
letters, but different spacing). Inconsistent data, or simple typos will
ruin text searches.

This might get you more results:

SELECT Tab2.[Item#1], Tab2.[Item#1], Tab2_2.[Item#1], Tab2_2.[Item#1]
FROM Tab2, Tab2 AS Tab2_2
WHERE Tab2.[Title] like "dvd*"
AND Tab2.actor = Tab2_2.actor
AND Tab2.year = Tab2_2.year
AND Tab2_2.title not like "dvd*"
AND Tab2_2.title like "*" & Right(Tab2.title,len(Tab2.title)-6) & "*";

If this doesn't get what you want, please include samples of the data that
you are getting and the data you are expecting to get - we might be able to
spot something there.

--
Daryl S

Yes, it is what i need as i respected. Is there away to search for the title
with "dvd*" but some title that end of it have the (cs). When i use like
"dvd*" +
Tab2_2.title like Trim(Right(Tab2.title,len(Tab2.title)-4) & "*" the title
that with dvd Monster house (cs) will not come up (not all title only some
but i want this kind of title to display as well). I tried to put the like
"dvd*" or like "*(cs). it did not find anything when i did that. Any idea?

Thank you very much for you help.

Hoachen -

I think this will do what you want, selecting all titles starting with dvd,
and all other titles starting with the same name without the dvd in front..

SELECT Tab2.[Item#1], Tab2.[Item#1], Tab2_2.[Item#1], Tab2_2.[Item#1]
FROM Tab2, Tab2 AS Tab2_2
WHERE Tab2.[Title] like "dvd*"
AND Tab2.actor = Tab2_2.actor
AND Tab2.year = Tab2_2.year
AND Tab2_2.title like Trim(Right(Tab2.title,len(Tab2.title)-4) & "*";

--
Daryl S

Tab1: Item#1, Item#2
Tab2: Item#1, Title, actor, year

I used inner join to join Tab1.Item#1 and Tab2.Item#1 with a filter on Title
(like "dvd*) this return all result have dvd at the beginning of the title
(like dvd Monster House, dvd Speedy Zone). Then based on these results, I
want to look at the same table (tab2) that have the same title (maybe look up
to 50 lenght of the title) but does not have dvd in front of it (like Monster
House, Speedy Zone) and these title without dvd have it own item#, but the
actor and the year will be same as with the dvd. The final result should be
like Item#1 dvd Monster House, Item#other for Monster House, Item#1 dvd
Speedy Zone Item#other for Speedy Zone. I tried under tab2 ("ebk " &[Title]
and mid([Tab2].[Title], 4,50)) and it does not work. Will it be easy to show
tab2 two times. tab2 and tab2_1?

I been trying it for week now but can't figure out.