Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to create PIVOT in Excel from access database | Excel Discussion (Misc queries) | |||
Create Pivot from access database by VBA | Excel Discussion (Misc queries) | |||
Create Access like report in Excel. | Excel Programming | |||
Create Access like report in Excel. | Excel Programming | |||
Access 2000: Print Database name in Report footer | Excel Programming |