Use Access or Excel? (monthly billing)

[pofe333]

Hello, I'm hoping someone can help with the most efficient way to do this. Every month I download a billing report listing costs for several telephone numbers. Each cost is broken down separately by description, so each .csv file equates to about 12,000 rows in Excel. I've attached a shortened sample .xls file.

The goal is to give each department a monthly report with a full breakdown of their costs by number, but not anyone other departments. Excel's pivot table filters well, but I can't export the details easily without the other departments being able to see each other's costs. So I turned to Access for reports. Here are my questions:

1) If I import a .csv like this every month, should I append it to the same table every time or import them as additional tables every month?

2) If I import each .csv as a separate table, how do I design a query or form to be able to see the added tables without manually updating it?

Any ideas on the best setup for something basic like this? Thanks for any help in advance.

 

[The_Doc_Man]

I would vote for importing to a single table each month. You can build constraining queries that would prevent a data bleed into other areas, and reports can be built equally well on queries or tables. Better, in cases where you need to do filtration.

 

[gemma-the-husky]

Access is an excellent tool, but its not as easy to learn and use as Excel.

you need a fair bit of vba to do stuff like this. you will probably end up spending a lot of time on it.

Seriously - if you are new to this, it would be cheaper spending some money on a developer who already has a nice set of tools ready to manage data imports and handling.

you can easily prepare analyses and print, or better still email them as pdf's with virtually 1 click.

you could extract high usage numbers/ strange numbers etc etc.

Far easier than with excel.

 

[Thales750]

The whole point of using Access would be the single table routine.

Dave is right it would be more expedient to get a developer.

Having said all that, you should give it a try. The best way to learn how to build a data base is to need one. You never know, you may have been born to do this work. And certainly in our evolving economy know a little bit about Database is really beneficial.

 

[pofe333]

Thank you all for the input. I've built a working database, but do find there are areas where some sort of scripting would significantly lessen the amount of manual work. For example, every time I get a new .csv I am going through the import wizard to append to the existing table. I'm sure that is not the quickest way.

 

[Thales750]

Thank you all for the input. I've built a working database, but do find there are areas where some sort of scripting would significantly lessen the amount of manual work. For example, every time I get a new .csv I am going through the import wizard to append to the existing table. I'm sure that is not the quickest way.

If the CVS is always the exact same format then link it instead of import. While Access is not running replace the old CVS with the new one and run an append query from the CVS to an internal table.

 

[pofe333]

If the CVS is always the exact same format then link it instead of import. While Access is not running replace the old CVS with the new one and run an append query from the CVS to an internal table.

What would happen in the event that I run an append query, but forget to replace the csv with a newer one? Will I end up with duplicate data or is there a way to prevent that?

 

[Thales750]

What would happen in the event that I run an append query, but forget to replace the csv with a newer one? Will I end up with duplicate data or is there a way to prevent that?

Well, one way would be:

OldDate = DMax("DateField","tblPermTable")
NewData = DMin( "DateField" , "LinkCSV" )
 
If NewData > OldDate then
Run the query

This assumes that the linked table would be clean. No overlapped data.

 

[Lightwave]

What would happen in the event that I run an append query, but forget to replace the csv with a newer one? Will I end up with duplicate data or is there a way to prevent that?

Yes you will probably end up with duplicate data unless you make some aspects of the target table unique.

Is there a column or combination of columns in the table to be imported that are truly unique?

Usually the most unique thing in a single source stream of information tends to be the date/time. An item can only be recorded input at a particular time and this will be slightly before or slightly after any other records. With telephones which I suspect are not a single source there is a likelihood that time by itself will not be unique. Two phones can be used at the same time. However the ID of the phone and the date and time of the phone call should as a pair of columns be completely unique.

It is possible to set up the target table so that all new records are required to have one column unique or a combiation of columns to be unique. In this way when access is asked to import / or append information into it it will look to the new informaition look at the unique field or combination of fields and import those records that are new and send duplicates to a table marked Paste errors (you may have seen this table before Access creates it if it doesn't already exist). This can infact be a complete saviour in preventing duplicates going into a table.

 

[pofe333]

I'm working with a guy here that is doing something similar to both of your suggestions actually. He is going to have the db add a time stamp column and when running the append query he will be referencing multiple columns to avoid duplicate data. Thanks again.