SQL Select Statement Help..

**jordan2322** · 04-16-2013, 06:32 PM

Hi Guys,

I am currently running a SQL server using MySQL. I have multiple tables in this database and I am trying to use a select to join these tables together.

The select statement I am using in MySQL Works and this is it here:

SELECT tblplant.PlantNumber as `Plant Number`, FleetNumber as `Fleet Number`, RegoNumber as `Registration`, RegoDate as `Registration Expiration`, FuelCardDate as `Fuel Card Expiration`, tblPlantMake.PlantMake as `Make`, Person.FirstName as `First Name` , tblPerson.LastName `Last Name`, tblCostCentre.CostCentre, tblDepot.Depot, tblPlantType.PlantType 
From tblplant
Join tblPlantType on tblplant.idPlantType = tblplantType.idPlantType
Join tblPlantMake on tblplant.idPlantMake = tblplantMake.idPlantMake
Join tblPerson on tblplant.idPerson = tblPerson.idPerson
Join tblCostCentre on tblplant.idCostCentre = tblCostCentre.idCostCentre
Join tblDepot on tblplant.idDepot = tblDepot.idDepot
Where tblPlantType.PlantType ="Car" And tblPlantMake.PlantMake ="Advance";

VBA Statement


Sql = "Select tblplant.PlantNumber AS `Plant Number`, " & _
"FleetNumber As `Fleet Number`, " & _
"RegoNumber As `Registration`, " & _
"RegoDate As `Registration Expiry`, " & _
"FuelCardDate As `Fuel Card Expiry`, " & _
"tblPlantMake.PlantMake As `Make`, " & _
"tblPerson.FirstName As `Operator`, " & _
"tblCostCentre.CostCentre As `CostCentre`, " & _
"tblDepot.Depot As `Depot`, " & _
"tblPlantType.PlantType As `Plant Type`, " & _
"From tblplant, tblplantmake, tblPerson, tblCostCentre, tblDepot, tblPlantType, " & _
"Join tblPlantType On tblPlant.idPlantType = tblPlantType.idPlantType, " & _
"Join tblPlantMake On tblPlant.idPlantMake = tblPlantMake.idPlantMake, " & _
"Join tblPerson On tblPlant.idOperator = tblPerson.idPerson, " & _
"Join tblCostCentre On tblPlant.idCostCentre = tblCostCentre.idCostCentre, " & _
"Join tblDepot On tblPlant.idDepot = tblDepot.idDepot, " & _
"Where tblPlantType.PlantType = Car And tblPlantMake.PlantMake = Advance;"

However I am having some translation issues getting this to work in VBA (Excel)

Once I try open the recordset the connection crashes as theres some syntax errors in my statement.

If you could you please point me out as to where I've gone wrong with this statement that would be much appreciated!

For all those who might also be struggling with a mySQL database the following link is a great read: http://www3.ntu.edu.sg/home/ehchua/p..._Beginner.html

From this website the "More than One Table" Chapter is where I learnt how to retreive data accorss multiple tables.

**jordan2322** · 04-17-2013, 07:55 PM

I got this working using:

Public Sub Create_SQL_String(SQL_Query)

SQL_Headers = "SELECT "
    SQL_PlantNumber = "tblplant.idPlant as `id Plant Number`"
    SQL_FleetNumber = ", tblplant.FleetNumber as `Fleet Number`"
    SQL_RegoNumber = ", tblplant.RegoNumber as `Registration`"
    SQL_RegoDate = ", tblplant.RegoDate as `Registration Expiration`"
    SQL_FuelCardDate = ", tblplant.FuelCardDate as `Fuel Card Expiration`"
    SQL_PlantMake = ", tblPlantMake.PlantMake as `Make`"
    SQL_PlantType = ", tblPlantType.PlantType as `Plant Type`"
    SQL_Person = ", tblPerson.Person as `Operator`"
    SQL_CostCentre = ", tblCostCentre.CostCentre as `Cost Centre`"
    SQL_Depot = ", tblDepot.Depot as `Depot`"

    SQL_Headers = SQL_Headers & _
                    SQL_PlantNumber & _
                    SQL_FleetNumber & _
                    SQL_RegoNumber & _
                    SQL_RegoDate & _
                    SQL_FuelCardDate & _
                    SQL_PlantMake & _
                    SQL_PlantType & _
                    SQL_Person & _
                    SQL_CostCentre & _
                    SQL_Depot


SQL_Tables = " From tblplant "
    SQL_Join_PlantType = "Join tblPlantType on tblplant.idPlantType = tblplantType.idPlantType "
    SQL_Join_PlantMake = "Join tblPlantMake on tblplant.idPlantMake = tblplantMake.idPlantMake "
    SQL_Join_Person = "Join tblPerson on tblplant.idPerson = tblPerson.idPerson "
    SQL_Join_CostCentre = "Join tblCostCentre on tblplant.idCostCentre = tblCostCentre.idCostCentre "
    SQL_Join_Depot = "Join tblDepot on tblplant.idDepot = tblDepot.idDepot"

    SQL_Tables = SQL_Tables & SQL_Join_PlantType & SQL_Join_PlantMake & SQL_Join_Person & SQL_Join_CostCentre & SQL_Join_Depot

'SQL_Where = "Where tblPlantType.PlantType =""Car"" And tblPlantMake.PlantMake =""Advance"";"
'SQL_Where = "Where tblPlantMake.PlantMake =""Advance"";"
SQL_Query = SQL_Headers & SQL_Tables & SQL_Where

Debug.Print SQL_Query

It just had some small syntactical errors that were more easily identified when using the routine

Cheers Guys!

Jordan