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

ASKER CERTIFIED SOLUTION
Bembi
CEO

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