Inner Join Statement (For VBA Code) (A table & a query)

[VAer]

I have two tables: Table_abc Table_xyz
XyzQuery is a query from Table_xyz
I would like to set up AbcQuery which needs inner join XyzQuery


How to correct my Inner Joint statement?

Thanks.

XyzQuery = "Select Xyz_field1, Xyz_field2 from Table_xyz"


AbcQuery = "Select Abc_field1, Abc_field2 From Table_abc Where Abc_field1 = " & _
        TextBoxfield1.Value & " Inner Join " & XyzQuery & " On Table_abc.Abc_field1 = XyzQuery.Xyz_field1 ORDER BY Abc_field1 ASC, Abc_field2 DESC"

[kagoodwin13]

The way this is written is confusing WHERE with INNER JOIN. They are more mutually exclusive, not meant to be combined together.

Also, you need an alias (AS) for your XyzQuery to use it.

This should be debugged as SQL first, and VBA second.

Right now it is reading as:

SELECT Abcfield1, Abcfield2
FROM Table_abc
WHERE Abc_field1 = Textboxfield1.Value
INNER JOIN
  SELECT Xyzfield1, Xyzfield2
  FROM Table_xyz
  ON Table_abc.Abcfield1 = Xyzquery.Xyz_field1
ORDER BY Abc_field1 ASC, Abc_field2 DESC

It would be a valid SQL query as:

SELECT Abcfield1, Abcfield2
FROM Table_abc
INNER JOIN
  (SELECT Xyzfield1, Xyzfield2
  FROM Table_xyz) AS XyzQuery
  ON Table_abc.Abcfield1 = Xyzquery.Xyz_field1
WHERE Abc_field1 = Textboxfield1.Value
ORDER BY Abc_field1 ASC, Abc_field2 DESC

[VAer]

The w
[/CODE]

It would be a vASC, Abc_field2 DESC
[/CODE]

So I should also put WHERE after INNER JOIN ?

Thanks.

[kagoodwin13]

So I should also put WHERE after INNER JOIN ?

Thanks.

Yes. INNER JOIN always follows a FROM statement. https://www.w3schools.com/sql/sql_join_inner.asp

[VAer]

Yes. INNER JOIN always follows a FROM statement. https://www.w3schools.com/sql/sql_join_inner.asp

How to write (SELECT Xyzfield1, Xyzfield2 FROM Table_xyz) AS XyzQuery in VBA? The code can understand one SELECT inside another SELECT? The code can understand left parenthesis and right parenthesis?

Thanks.

[davegri]

How to write (SELECT Xyzfield1, Xyzfield2 FROM Table_xyz) AS XyzQuery in VBA?

Are you asking how to create a named query from an SQL statement?
Or
Asking about SELECT statements within SELECT queries? (Nested queries or subqueries)

[VAer]

How to write (SELECT Xyzfield1, Xyzfield2 FROM Table_xyz) AS XyzQuery in VBA?

Are you asking how to create a named query)

I am asking how to correctly write VBA for AbcQuery? How to correctly write SQL in the AbcQuery statement (Initial post)?

[orange]

I have two tables: Table_abc Table_xyz
XyzQuery is a query from Table_xyz
I would like to set up AbcQuery which needs inner join XyzQuery

What exactly are you trying to accomplish - in plain English?
It would help if you provide
a) some sample data and
b) a sample of the output you expect from that data.

You have 2 tables that are related. Where does Textboxfield1 fit in this?

[VAer]

What exactly are you trying to accomplish - in plain English?
Textboxfield1 fit in this?

1) Copy XyzQuery to one Excel Sheet
2) Copy AbcQuery to another Excel Sheet
3) TextBoxfield1.Value is an input value from Userform textbox. It is not really important for this question. The only purpose is to make code dynamic for users. You may ignore this part.

Maybe I can modify the sample code as below:

XyzQuery = "Select Xyz_field1, Xyz_field2 from Table_xyz"

AbcQuery = "Select Abc_field1, Abc_field2 From Table_abc Inner Join " & XyzQuery & " On Table_abc.Abc_field1 = XyzQuery.Xyz_field1 Where Abc_field1 = " & TextBoxfield1.Value & " ORDER BY Abc_field1 ASC, Abc_field2 DESC"

[orange]

??? We're 9 posts in and this is the first mention of Excel???

I don't understand what you're trying to do. Your topic/subject Inner Join... has nothing to do with Excel.
Perhaps someone else will understand your request and respond.

[VAer]

Wha?

I made up my question, which is similar to my issue. Maybe I can make up sample data too.

Actually, this is NOT good example, I should make another new field for Textbox value, maybe TextBoxField6

Never mind, thanks for reading my post.

[VAer]

??? We're 9 posts in and this is the first mention of Excel???

Title use the word "For VBA code", write SQL statement inside VBA program.

[VAer]

Never mind. I can just ignore Inner Join statement. I was trying to ask a question, but the example is not good enough.

I can just ignore Inner Statement, and write a VBA loop to delete any unwanted records.

Let me mark the post as solved.

Thanks.