asked on

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

Patrick Matthews

phmurphy,

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

Patrick

phmurphy

ASKER

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.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phmurphy

ASKER

Thanks again.

phmurphy

ASKER

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 "