%"$!&*~ nested SQL Querys

[MrSandman666]

Hello again!

I have another problem that I urgently need your help with.
I have an SQL Query which contains aliased fields from a table ( e.g. "SELECT txtLstName as LastName&quot. I hand this string to another program where a user can build a filter for this query, based on the aliases of the original query. I wanted to solve the problem like this:
SELECT * FROM (SELECT txtLstName as LastName) WHERE LastName = 'Johnson';

For some reason this doesn't work. What do I do wrong? How can I do it right?

Thanks in advance!

 

[MrSandman666]

Anyone...?

...

...please?

 

[MrSandman666]

Or maybe a way to work around it and achieve the same effect?

 

[mymou]

Dont really understand the problem very well.

And dont recognise your nested SQL as standard syntax - but that might be my natural reluctance to use nested queries kicking in. Are you sure a join cant be used instead?

Stew

 

[MrSandman666]

Yeah, the stuff that I have up there is syntacticaly incorrect. That's exactly my problem: I don't know how to do it correctly.
I just wrote that stuff up there to clarify the logic. I wish it could work this way.
I have no experiences with joints (I'm new to SQL). Could you please explain that?

 

[JoeMiller]

Here's a general SQL statement, hopefully it will help you see what you're after:

SELECT MyField As xYzField
FROM MyTable

I am GUESSING that you put the "SELECT *" because you wanted all fields and put the stuff in parenthesis to tell Access to change txtLstName to LastName. This is not a "nested query" as you stated. A nested query is something completely different (which I won't get into right now). The problem is that with SQL you can specify SELECT * which is like saying SELECT ALL, and that will give you the fields as is. If you want to change ONE field to something else, you have to select each field individually. IE:

SELECT MyField1 As xYzField1, MyField2, MyField3
FROM MyTable

You can use this as well:

SELECT *, MyField1 As xYzField1
FROM MyTable

The problem with this is that you'll get both txtLastName and LastName. So you'll have to select each field individually to get rid of txtLastName.

My question is why are you building the query in SQL view if you are unfamiliar with SQL? The QBE editor in Access is a wonderful tool and will help you become familiar with SQL. Do what you're after in the QBE, and then switch to SQL view to look at the SQL statement generated by Access. Hopefully this has helped and my assumptions about what you're trying to do are correct. Good luck!! Joe Miller
joe.miller@flotech.net

 

[mymou]

Ouch.

If you are new to SQL do not start with nested queries!!

If you explain
1. what you have (tables and fields)
2. what you want (output explained)
3. what you have tried - if you try to put something together in QBE (query deseigner) and show us the SQL it generates (in SQL veiw - an easy way to pick up (acccess style) SQL). If any error or unexpected output - explain.

note, im confused by 'another program where a user can build a filter for this query, based on the aliases of the original query'. It sounds impressive but it goes over my head!!


From info up to date - i think you probably just need to query a query (instead of a table).

Hope im not oversimplifing


Stew

 

[MrSandman666]

Well, it's not like I have a choice. My boss simply told me to do it and if I don't know it I have to learn it. Oh, and I'm developing with VB and not Access, but I guess there are more SQL Gurus on this forum than the VB forum.

The problem is a little more complicated. Right now I gotta fly but I'll explain it as soon as I find time.

Thanks, though.