Need help with Query on linked tables

[BalloutMoe]

Hello All, I am completely new to access and was trying to do this with Excel Power query and was successful however the data is so large that it is taking forever to run, so I decided to give access a go. Here is the situation, I have 10 locations I manage and each day I receive a csv file for each location that contains the whole data some dating back to 2015 along with that days sales. I have linked to the csv and got the data in however I would like to change the store ID value and Sum the fldAmount based on pay type from the same date. For example if its a credit card payment which is "Visa, Mastercard, Amex" ect, if its Cash or Check keep it as is. I would like to combine them together in one row and Change the value to Credit Card with the sum of amount. Is this possible with a linked table? I do not know how to link it so I always get the newest data every day. Thank you in advance.

This is the result I am hoping for so I end up with three rows for each date with pay types Cash, Check, CreditCard

fldID	fldDate	fldPayType	fldAmount
8	3/18/2019	Cash	$336.53
8	3/18/2019	Check	$233.72
8	3/18/2019	Credit Card	$2590.77 which is the sum of (Amex,Debit,Discover,MasterCard,Visa)

 

[Micron]

Not clear what you want. Combine them in one row could mean all values comma separated but in one column. Could mean all values in one row, each in a separate column. If the latter, possibly you need to explore a cross tab query: group by ID, date as row header, pay type as column headers and amounts as values. I do struggle with cross tab queries at times so that's just a wild guess.

 

[Joe4]

If you are just trying to total up the values by fieldID/date/type, I would first create a query and add a calculated field that returns the "type" (so your different credit cards would all return "Credit Card").
Then, you can do another query, based on this query, that is a Totals (Aggregate Query) grouping on the fldID, fldDate, and this new "type" field, and taking the Sum of the "fldAmount" field.

See here for more details on Aggregate (Totals) Queries: Aggregate Function Queries in Access - Instructions