SQL filter before another SQL process using an alias

I am a real rookie trying to apply a filter before I run an sql statement.  I have some code that constructs the filter, and I think I just learned how to use an alias to represent the filtered data.  Here is the SQL for that:
SELECT WetVeg.* FROM WetForm INNER JOIN WetVeg ON WetForm.ID1 = WetVeg.ID2 WHERE WetForm.ProjectSite = "a" as x;
So my hope was that the alias x would be used in the code below so the data would be pulled from the filtered result.

I get a syntax error, so what I have left out, commas, quotes, parens?
Dim sqltext As String
sqltext = "SELECT z.Spename INTO ProjectSpeciesListOutput "
sqltext = sqltext & "FROM(SELECT SP1 AS Spename FROM x UNION "
sqltext = sqltext & "SELECT SP2 AS Spename FROM x UNION "
sqltext = sqltext & "SELECT SP3 AS Spename FROM x) as z Group by z.spename"

Open in new window

phmurphyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
phmurphy,

Sorry, but it's not really clear what you are trying to do here.  An example would be helpful :)

Patrick
0
phmurphyAuthor Commented:
I have two linked tables, WetForm and WetVeg.  I need to filter the data based on fields found in WetForm, but I need to stack the fields found in WetVeg as you already showed me how to do.
So instead of just working on the data in WetVeg which I would do using the following:
sqltext = "SELECT z.Spename INTO ProjectSpeciesListOutput "
sqltext = sqltext & "FROM(SELECT SP1 AS Spename FROM WetVeg UNION "
sqltext = sqltext & "SELECT SP2 AS Spename FROM WetVeg UNION "...
I am trying to substitute the filtered data for the WetVeg.

I am trying to plug in the x where WetVeg is in the previous code, and x is the alias (I think) from the following code

SELECT WetVeg.* FROM WetForm INNER JOIN WetVeg ON WetForm.ID1 = WetVeg.ID2 WHERE WetForm.ProjectSite = "a" as x

The filter is WetForm.ProjectSite = "a" on the linked tables WetVeg and WetForm

Does that help?

Thanks.


0
Patrick MatthewsCommented:
In this case, I think this is the best you're likely to get:



SELECT z.Spename INTO ProjectSpeciesListOutput
FROM
    (SELECT v.SP1 AS Spename 
    FROM WetForm f INNER JOIN 
        WetVeg v ON f.ID1 = v.ID2 
    WHERE f.ProjectSite = "a" 
    UNION
    SELECT v.SP2 AS Spename 
    FROM WetForm f INNER JOIN 
        WetVeg v ON f.ID1 = v.ID2 
    WHERE f.ProjectSite = "a" 
    UNION
    SELECT v.SP3 AS Spename 
    FROM WetForm f INNER JOIN 
        WetVeg v ON f.ID1 = v.ID2 
    WHERE f.ProjectSite = "a" ) as z
GROUP BY z.spename

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
phmurphyAuthor Commented:
Thanks again.
0
phmurphyAuthor Commented:
I hybridized you suggestion by  substituting the memory variable for the constructed filter string.
exportquery is the string that was constructed elsewhere.

sqltext = "SELECT z.Spename INTO ProjectSpeciesListOutput "
sqltext = sqltext & "FROM(SELECT SP1 AS Spename FROM " & exportquery & " UNION "
sqltext = sqltext & "SELECT SP2 AS Spename FROM " & exportquery & " UNION "
sqltext = sqltext & "SELECT SP3 AS Spename FROM " & exportquery & " UNION "
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.