Data type of a text field changing because of group by in query

[mismag]

Hi,



I created a query as follows.

SELECT TblDealers.DealershipName, TblSales.SaleDate, TblSales.FullName, TblSaleDetails.ProductName, Sum(TblSaleDetails.Deals) AS Deals, Sum(TblSales.TotalDeals) AS TotalDeals, Sum(TblSaleDetails.GrossAmount) AS GrossAmount
FROM (TblDealers INNER JOIN TblSales ON TblDealers.[DealershipID] = TblSales.[DealershipID]) INNER JOIN TblSaleDetails ON TblSales.[SaleID] = TblSaleDetails.[SaleID]
GROUP BY TblDealers.DealershipName, TblSales.SaleDate, TblSales.FullName, TblSaleDetails.ProductName
HAVING (((TblSales.SaleDate)=#1/1/2019# Or (TblSales.SaleDate)=#2/1/2019#))
ORDER BY TblSales.SaleDate;

If you see in the query the field ProductName from the SaleDetails table actually is a text field.

When I am connecting to this query from Tableau (Reporting tool), the ProductName field is automatically changing its data type to Number and is displaying the productID instead of ProductName.

Any idea how to solve this?

TIA....

Regards,
ASD

[pbaldy]

If I had to guess, the data type is actually numeric but it's a lookup field, where you see the text value but the numeric value is actually what gets saved.

http://www.theaccessweb.com/lookupfields.htm

[mismag]

Is there a way to fix this?

[pbaldy]

I don't/won't use lookup fields. You can probably join to the product table and get the description from there.