Post reply

MS Access query - SQL query

  • March 16, 2005 at 10:15 am

    #67169

    Hi Folks

    Apprecaite any help with this one as I am a bit stuck with this. Recently upsized an Access database to SQL server. Need to change one of the queries so that it runs on the SQL server .

    However do not have a great deal of knowledge in changing the script so it runs on SQL server.

    This is the original script , can anyone suggest what I need to change in order to get it to run on SQL ?

    SELECT tblTicket.Id, "PA" & Format([tblTicket]![Id],"000000") AS TicketId, tblTicket.OpenDate, tblTicket.Createdby, tblTicket.StartDate, tblTicket.EstCompletionDate, tblTicket.ActCompletionDate, tblTicket.SummaryOfWork, tblEquipment.Equipment, tblSubNetwork.SubNetwork, tblLocation.Location, tblRisk.Risk, tblStatus.Status, tblTicket.ClosedDate, tblAdmin.Admin, tblTicket.Notes, tblTicket.SwitchingActivity, tblLocation.Country, tblTicket.OrginatorName

    FROM ((((tblStatus RIGHT JOIN (tblTicket LEFT JOIN tblAdmin ON tblTicket.fk_Admin_Id = tblAdmin.ID) ON tblStatus.Id = tblTicket.Status_Id) LEFT JOIN tblLocation ON tblTicket.Location_Id = tblLocation.Location_Id) LEFT JOIN tblEquipment ON tblTicket.EquipmentID = tblEquipment.Id) LEFT JOIN tblRisk ON tblTicket.RiskID = tblRisk.ID) LEFT JOIN tblSubNetwork ON tblTicket.SubnetworkID = tblSubNetwork.Id

    ORDER BY tblTicket.Id DESC;

    Many thanks for any advice , really want to learn what needs to be done.

    Cheers 

  • March 16, 2005 at 10:39 am

    #547319

    SELECT tblTicket.Id, 'PA' + Right('000000'+ cast(tblTicket.Id as varchar(6)),6) AS TicketId, tblTicket.OpenDate, tblTicket.Createdby, tblTicket.StartDate, tblTicket.EstCompletionDate, tblTicket.ActCompletionDate, tblTicket.SummaryOfWork, tblEquipment.Equipment, tblSubNetwork.SubNetwork, tblLocation.Location, tblRisk.Risk, tblStatus.Status, tblTicket.ClosedDate, tblAdmin.Admin, tblTicket.Notes, tblTicket.SwitchingActivity, tblLocation.Country, tblTicket.OrginatorName

    FROM ((((tblStatus RIGHT JOIN (tblTicket LEFT JOIN tblAdmin ON tblTicket.fk_Admin_Id = tblAdmin.ID) ON tblStatus.Id = tblTicket.Status_Id) LEFT JOIN tblLocation ON tblTicket.Location_Id = tblLocation.Location_Id) LEFT JOIN tblEquipment ON tblTicket.EquipmentID = tblEquipment.Id) LEFT JOIN tblRisk ON tblTicket.RiskID = tblRisk.ID) LEFT JOIN tblSubNetwork ON tblTicket.SubnetworkID = tblSubNetwork.Id

    ORDER BY tblTicket.Id DESC

     

     

    hth


    * Noel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply