Serious help requested for professional use database > selection queries

[Gobes]

Hello,


I have just started working as a data analyst at a credit management department of an energy company in the netherlands.
The problem in this company is that the IT software was never adjusted to the processes and many processes are executed in Excel. So every analysis I want to make I need to create the data myself first.

To send reminders to customers with overdue invoices an employee takes a .csv produced by a server at night and makes some manual selection steps in Excel to select the correct customers.

Now, as it turns out now, many mistakes were made this year in sending reminders and charging additional costs for late payments.

So what I need to do now is create a database from all the daily .csv (20mb - 150mb in size daily) and make a query that shows from a given batch of customers the payable amount each day. That way we can identify which customers were selected incorrectly.

What I did now was import all the .csv's as tables and first make a selection query for each month, then combine them into one query for the entire year.

It is too messy. And because many data fields need to be recalculated from the given .csv MsAccess gives me many headaches.

I need to have an analysis for the Management Team by Friday.

What do I need help with concretely:

- A way to import 30 .csv for every month in a clean way so that they are easily queryable. In total I need to import 120 csv files.
- A clean way to recalculate fields in a selection query

If necessary I can upload an example .csv with fictional data.

Thanks for any support!

[pbaldy]

An example file would probably help.

[Gobes]

Apologies for posting a thread asking for help and not responding in a timely matter. I also realize that my actual questions were poorly formulated but I do not have much technical knowledge of access or sql but for this job I am going to have to study this.

My problem was easy to handle in the end. I made an insert query that just added each csv file to the same table while adding a field containing the generation date of the csv. I made a create table query to do the recalculations. I could then create some cross table queries using the added date field as column headers to get the data view that I needed.

This solution wasn't viable in the beginning unfortunately because with 6 weeks of data I would reach the access limit of 2 GB. But fortunately from another data source I was able to identify a selection of customers of several thousand instead of the 300k+ customers that are in the generated csv files.

[pbaldy]

No worries, glad you sorted it out. Welcome to the site by the way!