2 Replies Latest reply on Nov 7, 2013 2:50 PM by Toby Erkson

    microsoft access query connection

    janelle.theobald

      Hi,

      I have a Microsoft Access Database that has a query I would like to connect to. It is named "TOT Rev All By Load J"; see SQL below. When I try to connect Tableau to Access this query is not an available connection. I have read it other similar posts that I should just use this query to create a new one and then use that query to connect to. So i just made a simple query using the "TOT Rev All By Load J" query, pulled all the columns into a new query and saved it. There are no new calculations or anything in the new query, but it still is not showing up in available connections. Anybody have a similar issue and able to help?

      Thanks,

      Janelle

       

      SELECT [Revrep all j].[Driver Employee Number], [Revrep all j].BRREG, [Revrep all j].Product, [Revrep all j].com1, [Revrep all j].[Haul To], [Revrep all j].Date, [Revrep all j].[Haul From], [Revrep all j].[Tractor RS #], [TRANS RS].[VEHICLE MAKE], [TRANS RS].[VEHICLE MODEL], [TRANS RS].[VEHICLE CATEGORY], [Revrep all j].[Trailer Rolling Stock Number], [Revrep all j].[Scale Ticket or BOL #], [Revrep all j].[BU or TON], Sum(([Revrep all j]![AMT])+(([Revrep all j]![BU or TON]*[Revrep all j]![Cust Farm Rate]))) AS [Non Wait], Sum(([Revrep all j]![Wait])) AS Wait, Sum(([Revrep all j]![BU or TON]*[Revrep all j]![Cust Farm Rate])) AS [Farmer Billed], Sum([Revrep all j]![AMT]+([Revrep all j]![BU or TON]*[Revrep all j]![Cust Farm Rate])+[Revrep all j]![Wait]) AS TOT, Sum([Revrep all j].MAINTENENCE) AS SumOfMAINTENENCE, Sum([Revrep all j].[TIME OFF OR MEETINGS]) AS [SumOfTIME OFF OR MEETINGS], [Non Wait]/[BU or TON] AS [base rate]

      FROM [Revrep all j] INNER JOIN [TRANS RS] ON [Revrep all j].[Tractor RS #] = [TRANS RS].[ASSET ID]

      GROUP BY [Revrep all j].[Driver Employee Number], [Revrep all j].BRREG, [Revrep all j].Product, [Revrep all j].com1, [Revrep all j].[Haul To], [Revrep all j].Date, [Revrep all j].[Haul From], [Revrep all j].[Tractor RS #], [TRANS RS].[VEHICLE MAKE], [TRANS RS].[VEHICLE MODEL], [TRANS RS].[VEHICLE CATEGORY], [Revrep all j].[Trailer Rolling Stock Number], [Revrep all j].[Scale Ticket or BOL #], [Revrep all j].[BU or TON];