Need help with Query on linked tables

BalloutMoe

Board Regular
Joined
Jun 4, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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
fldIDfldDatefldPayTypefldAmount
83/18/2019Cash$336.53
83/18/2019Check$233.72
83/18/2019Credit Card$2590.77 which is the sum of (Amex,Debit,Discover,MasterCard,Visa)
 

Attachments

  • accesspic.png
    accesspic.png
    61.1 KB · Views: 9

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,461
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,991
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,568
Messages
5,770,900
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top