troubleshooting Question

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

Avatar of Cole100
Cole100Flag for United States of America asked on
SQL* Access 2019Microsoft Access
3 Comments1 Solution15 ViewsLast Modified:

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!

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


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros