Link to home
Start Free TrialLog in
Avatar of Cole100
Cole100Flag for United States of America

asked on

Need to figure out how to return data based on generated fields

User generated image
I am trying to find a better way to generate the RefNumber field which is a combo of Store_Number and Transaction Date. It has to be 11 characters or less. With my limited knowledge using Access the best I could come up with was creating a table called DateChange that took the 6/1/21 format and turns it into 060121 format. The problem with my "solution" what used to take the query 2 minutes to run now takes 30+ minutes. In case it matters it is a union query that combines 5 queries. I'm sure this is the worse way I could have done it. I thought it was creative at the time. Please help. Thanks!

User generated image
SELECT [Class] AS Customer, dds_Daily_Sales_Tb.Operations_Day AS [Transaction Date], [Store_Number] & "-" & [Date_Change] AS RefNumber, SortByRegion.QbStoreNumber AS Class, SortByRegion.SalesTax AS Item, dds_Daily_Sales_Tb.Total_Tax_Amount AS Price
FROM DateChange INNER JOIN (SortByRegion INNER JOIN dds_Daily_Sales_Tb ON SortByRegion.[StoreNumber] = dds_Daily_Sales_Tb.Store_Number) ON DateChange.Invoice_Date = dds_Daily_Sales_Tb.Operations_Day
GROUP BY dds_Daily_Sales_Tb.Operations_Day, SortByRegion.QbStoreNumber, SortByRegion.SalesTax, dds_Daily_Sales_Tb.Total_Tax_Amount, dds_Daily_Sales_Tb.Store_Number, DateChange.Date_Change
HAVING (((dds_Daily_Sales_Tb.Operations_Day)=#6/1/2021#));

Open in new window

Avatar of Bembi
Bembi
Flag of Germany image

 CONVERT(varchar, Store_Number ) + '-' + CONVERT(varchar, FORMAT([Transaction Date], 'ddmmyy')) as RefNumber
Avatar of Cole100

ASKER

I get an error UNDEFINED FUNCTION 'CONVERT' IN EXPRESSION
User generated image
SELECT [Class] AS Customer, dds_Invoice_Promo_Tb.Invoice_Date AS [Transaction Date], CONVERT([varchar],[dds_Invoice_Tb].[Store_Number])+'-'+CONVERT([varchar],Format([Transaction Date],'ddmmyy')) AS RefNumber, SortByRegion.QbStoreNumber AS Class, SortByRegion.Promotions AS Item, Sum(([Promo_Amount]*-1)) AS Price
FROM dds_Invoice_Tb INNER JOIN (dds_Invoice_Promo_Tb INNER JOIN SortByRegion ON dds_Invoice_Promo_Tb.Store_Number = SortByRegion.[StoreNumber]) ON (dds_Invoice_Tb.Store_Number = dds_Invoice_Promo_Tb.Store_Number) AND (dds_Invoice_Tb.Invoice_Number = dds_Invoice_Promo_Tb.Invoice_Number) AND (dds_Invoice_Tb.Invoice_Date = dds_Invoice_Promo_Tb.Invoice_Date)
WHERE (((dds_Invoice_Tb.Void_Flag)="N"))
GROUP BY dds_Invoice_Promo_Tb.Invoice_Date, SortByRegion.QbStoreNumber, SortByRegion.Promotions, dds_Invoice_Tb.Store_Number
HAVING (((dds_Invoice_Promo_Tb.Invoice_Date)=#6/12/2021#) AND ((SortByRegion.QbStoreNumber)="BLUE REGION:1138"));

Open in new window



ASKER CERTIFIED SOLUTION
Avatar of Bembi
Bembi
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial