SQL in VBA to Access a Database and create Report
 

Greetings.

I've started to program a Microsoft Query in VBA, in order to create an
Excel report.

Presently I'm stuck with a code and need to study further in SQL.

Is there any reference in Internet where I can learn to program in SQL?


Anyway, I leave you my question, hoping someone can help me:

Imagine the tables:

FlownRevenue05
--
AgentCode (integer)
Tickets (integer)
Revenue (float)
JourneyType (char 1, "D" for Domestic Sales, "I" for International)

Groups
--
AgentCode
GroupName (char 10)


The AgentCode are connected via a Many(FlownRev)-One(Groups).

The rabbit I'm trying to pull out now is combine, in the same report, the
following data:


AgentCode CouponsDOM RevenueDOM CouponsINT RevenueINT


And for that, I tried the following SQL:

Quote:
..Sql = Join$(Array( _
"SELECT G05.`Group Name`, FR05.`Agent Code`, Sum(FR05.Coupons) as [CpnsDOM],
Sum(FR05.`Revenue LC`) as [RevDOM], _
"FROM {oj `C:\My Documents\Temporário\Unify\db2006 November-onward
Compact`.`Flown Revenue 2005` FR05", _
"LEFT OUTER JOIN `C:\My Documents\Temporário\Unify\db2006 November-onward
Compact`.Groups G05", _
"ON FR05.`Agent Code` = G05.`Agent Code`}", _
"WHERE FR05.`Journey Type` = 'D' and G05.`Group Name` = '" &
cmdComboBox.Text & "'", _
"GROUP BY G05.`Group Name`, FR05.`Agent Code`", _
"UNION SELECT G05.`Group Name`, FR05.`Agent Code`, Sum(FR05.Coupons) as
[CpnsINT], Sum(FR05.`Revenue LC`) as [RevINT], _
"FROM {oj `C:\My Documents\Temporário\Unify\db2006 November-onward
Compact`.`Flown Revenue 2005` FR05", _
"LEFT OUTER JOIN `C:\My Documents\Temporário\Unify\db2006 November-onward
Compact`.Groups G05", _
"ON FR05.`Agent Code` = G05.`Agent Code`}", _
"WHERE FR05.`Journey Type` = 'I' and G05.`Group Name` = '" &
cmdComboBox.Text & "'", _
"GROUP BY G05.`Group Name`, FR05.`Agent Code`"), vbCr)



Using the "UNION SELECT" I only get the headers AgentCode CouponsDOM
RevenueDOM, and the values appear to be the sum of Domestic and
International.

I hope this is possible, probably it's just a question of arranging the
proper SQL.