Struggling

[fadwen]

First of all, HI!

Second, I've been toying with access in the hopes that it will save me headaches, but I want to make sure it's within the scope of the program/if I should use something else.

Basically, I've been using an Excel generated report and have to manually filter/sum totals. I think I can use Access to auto-populate and filter the fields, but I'm not sure HOW.

Let's say there are four fields that I am concerned with in the spreadsheet. I've figured out how to only import them, but I'm struggling with how to display it. Fields would be something like: Name, Account#, Amount, Type.

I basically want to import the excel sheet, and show the information filterable by name. So like Name, worked on this Account for this Amount doing this Type.

I'm seeing how to do totals, how to generate a report, and how to import only the needed fields. But I'm struggling with how to tie it all together.

The excel spreadsheet shows me what each individual has done to an account per transaction and I need to pull the grand total for reconciling purposes, as well as to filter it by account/type in case there is a discrepancy. Is this possible or overkill? I'm not asking for a full step-by-step, but it feels like I've hit a roadblock and some pointers would be most appreciated.

 

[plog]

Is this possible or overkill?

It sounds like a frankenstein. Not man (Excel), not beast (Access), but some unholy monster waiting to scare the villagers into pitchfork and fire it out of its misery.

Seems your afraid to move to Access so you create some sort of hybrid that just makes life more complicated than a straight Excel or straight Access solution would provide. My advice is to either replace your Excel spreadsheet entirely with Access, or just stay in Excel. Access can store data, retrieve data, allow you to edit data and generate reports, it has it all--you don't need Excel if you move to Access.

 

[fadwen]

Thank you so much for your response!

I deal with a government approved system meaning it is years old and they can't find any programmers/developers to wade through it to fix things, or at least not at the price they are willing to pay. The report that I normally filter is generated as the Excel spreadsheet so I can't really change that.

I was hoping to be able to use Access to essentially automate the filters that I normally apply in Excel, with the potential to keep the information maintained for audit purposes. If from what you are saying it isn't in the realm of possibility, I suppose I must continue doing things the same. At least they pay me hourly.

 

[plog]

I'm saying it can be done--all entirely in Access. Import your data properly and then work in Access. Don't build some system that passes data back and forth between Access and Excel.

 

[fadwen]

Hrmmm, I'm not looking to export anything to Excel. The Excel spreadsheet with the data is generated from the program we have -- I can't change that. All I want to do is import the Excel spreadsheet and be able to filter the thousands of lines automatically or make it at the very least not as time consuming vs the manual filters in Excel.

 

[Simon_MT]

Find the program in Access and do your filtering in Access and export the results into Excel. Sounds like the existing program does a dumb dump. Give it some intelligence - you know how to filter in Excel so you are quite capable to doing the filtering in Access.

Simon

 

[Gasman]

I'm saying it can be done--all entirely in Access. Import your data properly and then work in Access. Don't build some system that passes data back and forth between Access and Excel.

May I jump in here please Plog?

I created a system a little while back at the place I worked where I imported the same type data each day into access from Excel after deleting the previous days data and the db grew at an alarming rate?

I have since discovered you can link Access to Excel without the need for importing.

I believe I will have need of a similar process in the coming months and am wondering about the pros and cons with each method?

Could you and others advise please.?

 

[plog]

I created a system a little while back at the place I worked where I imported the same type data each day into access from Excel after deleting the previous days data and the db grew at an alarming rate?

"Alarming" is a vague term. COBOL programmers from the 70's think 100 KB is a ton of space, the minecraft generation thinks a gigabytes is the smallest unit of storage.

Also, I'm a biased source--I'm not really familiar with the method you have proposed, so all my arguments will be in favor of an Access storage approach:

1. You can define the proper type and length for your fields. That reduces the size on disk.

2. Indexes. You can add indexes which allows for faster data searches than non-database solutions.

3. Compact and Repair. When you are "replacing" data daily, you can compact and repair Access making it smaller.

4. Easier to debug. When you use 2 pieces of software its harder to track down issues. Is it a problem with the connection? Is it on the Access side or the Excel side? Is it good in Excel but not Access?

Again, I am biased, there are probably advantages to your method, but I'm not really familiar with your method to know them.

 

[jdraw]

Seems there may be a variety of solutions possible. The real issue, as I read it, is to understand exactly WHAT is required.
If you are toying with Access, you might want to do some tutorials and samples to get more familiar with capabilities.

"...within the scope..."

suggests that the details may not be well defined. If you don't have complete requirements, it's extremely difficult(futile) to solve the problem regardless of tool/software etc.

As the others have said, it seems it could all be done in Access, but getting to the details is important in offering more focused advice.

If you are dealing with a REPORT from Excel, then many details may have been processed and what you have is a "summary". Might be better to look at the details in the data before any "summarization" takes place.

Also, if you are dealing with financial info, the auditors may have their own requirements which could influence any choice you make.

Access has some reserved words like Name that could cause you grief.

Good luck.

 

[fadwen]

I suppose I am dealing with a report from Excel that contains all the information I need and I was looking at a way to use Access to filter it down more. I know exactly what information I/auditors need from the report and how it would best be summarized.

From what I am seeing here Access would do best at being the one that disseminates and categorizes the information at the source rather than filtering the data generated second hand. Unfortunately I am unable to do this, both due to my skill and company policy.

I started looking to Access as a solution as a kind of Hail Mary play, but I will continue learning it and maybe be able to utilize it in other projects.