What am I doing wrong? (1 Viewer)

[triplell89]

Here is my sql:

SELECT Distinct [Profiles in Engine].NAME, Left([profiles in engine].[name],InStr([profiles in engine].[name]," ")) AS Left1, [Profiles in Engine_1].NAME
FROM [Profiles in Engine] AS [Profiles in Engine_1], [Profiles in Engine] INNER JOIN Sender_SRTs ON [Profiles in Engine].NAME = Sender_SRTs.SNDR_TP_NAME
WHERE ((([Profiles in Engine].NAME) Like "*[left1]*") AND ((Left([profiles in engine].[name],InStr([profiles in engine].[name]," ")))<>"") AND (([Profiles in Engine_1].NAME) Like "*[left1]*") AND ((Sender_SRTs.[SUM(MSG_CNT)])>10000));

I've played with this for a while now, and I can't get it to do what I want.

I have a column that I want to compare to itself. So Profiles in engine and profiles in engine_1 are the same table. Left1 is the first word in the column.

My output looks like this

football field-football-item1
football field-football-item2
football field-football-item3
football field-football-item4
football field-football-item5
football field-football-item6
football field-football-item7
football field-football-football field
football field-football-football shoes

in other words, it displays columns 1 and 2 right, but I want the third column to only show the bold rows. Currently its listing everything in the list.

My psuedo code would be:

If column 3 contains column 2 then
column 1 is original
column 2 is first word
column 3 is matching columns
end if

can someone please help?

 

[MSAccessRookie]

FROM [Profiles in Engine] AS [Profiles in Engine_1], [Profiles in Engine] INNER JOIN Sender_SRTs ON [Profiles in Engine].NAME = Sender_SRTs.SNDR_TP_NAME

Looking at the FROM statement ONLY, there are two things to clarify and repair if necessary. The WHERE Statement may have additional issues, but the FROM Statement needs to be modified first, and then the WHERE Statement can be modified if necessary.

1. The first Table (The Table with the Alias [Profiles in Engine_1]) is not JOINed to anything. This will create a Cartesian JOIN where all records in the Table will be compared to all records in each of the other Tables before being selected.
2. The Order of precedence for the JOINs is not clear. Adding parentheses will help to clarify the order for processing of the data.

 

[triplell89]

yeah, I wondered about that...I would like to each row to compare to eachother, and then filter the results...but I guess this is impossible.

What if I used just 1 table, created an expression with that table which would be the first word in that column. I then want to compare each row to that word, and show any "like" matches.

And could you specify what you mean by order of precedence?

 

[Fifty2One]

Write a select query in SQL for engine and then UNION then write same query with engine-1 field where you have engine. The output will have the contents of engine in the same column as engine-1. Is that what you need?

 

[MSAccessRookie]

yeah, I wondered about that...I would like to each row to compare to eachother, and then filter the results...but I guess this is impossible.

What if I used just 1 table, created an expression with that table which would be the first word in that column. I then want to compare each row to that word, and show any "like" matches.

And could you specify what you mean by order of precedence?

By order of precedence, I am referring to which Table needs to be JOINed First, Second, etc. If I were to guess, the following would be my first choice:

[I]FROM [Profiles in Engine] AS [Profiles in Engine_1] [/I]
[I]   [COLOR=red][B]INNER JOIN ([/B][/COLOR][Profiles in Engine] [/I]
[I]       INNER JOIN Sender_SRTs [/I]
[I]       ON [Profiles in Engine].NAME = Sender_SRTs.SNDR_TP_NAME[B][COLOR=red])[/COLOR][COLOR=red] AS A[/COLOR][/B] [/I]
[I]   ON [Profiles in Engine_1].[NAME] = A.[NAME][/I]

If I am correct, you can also make the WHERE statement simpler as well, since you will now only need to check one of the names.

NOTE: I believe that NAME is still a Reserved Word in MS Access, so it should not be used as a Column or Variable name, since doing so could produce unpredictable results.

 

[boblarson]

NOTE: I believe that NAME is still a Reserved Word in MS Access, so it should not be used as a Column or Variable name, since doing so could produce unpredictable results.

That is a BIG yes, it is a reserved word and is the worst one to use as a field or object name because everything has a name property. And there is even the ability to put the name of a report on a report by using
=[Name]

which then would be screwed up by using Name as a field.

 

[MSAccessRookie]

That is a BIG yes, it is a reserved word and is the worst one to use as a field or object name because everything has a name property. And there is even the ability to put the name of a report on a report by using
=[Name]

which then would be screwed up by using Name as a field.

DOH! I wasn't even thinking about that! I remember using a Name As Statement in VB. At least we agree that the OP should modify the code and not use the word NAME as a Column Name.

 

[boblarson]

And congrats on hitting the 2000 post mark MSAccessRookie (I think you're due for a name change)

 

[MSAccessRookie]

And congrats on hitting the 2000 post mark MSAccessRookie (I think you're due for a name change)

WOW! I have 2009 I do not look very often and I did not notice. Thanks a lot for the show of confidence, but I like my name and think I want to keep it for a little while longer if that is OK. Maybe I will reconsider after 5000 posts.

 

[boblarson]

I want to keep it for a little while longer if that is OK. Maybe I will reconsider after 5000 posts.

Then I want to refer to you as MSAR then as I have trouble typing Rookie at the end of it as I don't "feel it" is true anymore.