Check to ensure all imports done (1 Viewer)

Allie12380

New member
Joined
Jul 10, 2019
Messages
1
Good afternoon,
I have a general questions that I am seeking direction/ideas on ways to handle.


I work in government. We cannot connect the ACCESS database directly to another system that contains the financial data. Trust me - we have tried and tried but will not be given the data in any format or given access to the data.



Therefore, we have to go into a system that allows us to run a report and download the financial data. However, the issue is that we have to run the report by fund (funding source) and organization (department). This means, each month, we run and download into Excel 134 individual files. A report can have 1 to 400 Excel rows of data. We then copy the data from Excel into ACCESS.


The person who is responsible for this task prints a word document out each month and highlights the reports as they are copy and paste the data into the database. At times, a report gets highlighted for the month but is not loaded. I have controls that do stop duplicate data from being added.



What I would like to be able to do in ACCESS is have a report or query or something....that I can run to help identify the missing financial data for the month. Or a way that system creates a report that shows the data that is and is not loaded.



When we download we get a

Fund # and Organization # --> these combined are primary key and then when we download financial data monthly we add the fiscal year and month to primary key.


An option I have thought about but not implemented is a query that pulls all fund and all organization records and then calculate one column that adds the (budget+actual+commitments) and if that is zero or null then research those only. If an amount exists then rely on the data being loaded.



I have one report that I run that summarizes the financial data that I manually compare to a pdf report to make sure data is right. This is cumbersome and if data is found to be missing means multiple printings of the report.


Any suggestions, idea, topics to check into, or options for having ACCESS to help me identify missing data would be greatly appreciated.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,690
Hi. Welcome to the forum. I'm not sure I follow all that but checking for missing data is pretty straightforward using the Find Unmatched Wizard, provided you have a list of what you have and a list of what you're supposed to have for comparison.
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
The easiest way will be to download the 134 reports with a standard naming structure.

Then develop a routine that imports each of these, and moves the processed reports into a different folder. You can easily log the missing and processed files.

You could even pre-check that all 134 files are there before starting the process.

Put the names of the reports that need importing into a table, so that you can run this each month without needing to change your code. So if you get another export you need, hust add it ito the import table.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,573
I have a different viewpoint on this. Part of your problem is that you cannot directly gather what you want. But you don't want it. Your boss wants it. Or his/her boss wants it. Your solution may therefore involve telling whoever wants this that you are not getting the data you need and might need someone with more political clout to make it happen.

Before you protest, my background is as a U.S. Navy contractor in a shop where Secret clearances were required. Every so often over a period of 28+ years, we would find this situation that you describe. My primary system was the machine that managed Navy Reserve personnel including mobilization and de-mobilization. We also cut pay orders, travel orders, and handled HIPAA data - all of those indirectly since the Navy didn't put all their eggs in one basket. I counted 18 cross-system interactions at the highest point.

The solution was to find these back-door methods such as you are using where you take exports and try to make sense of them. But with enough political clout backing you, it might be possible to get someone with that other database to find a slightly different and more reliable method of export that could improve your data reliability. That is how we handled 18 interfaces to 18 different U.S. Government organizations. In essence, you make YOUR problem suddenly become SOMEONE ELSE'S problem - and then the other side grudgingly holds a "collaboration meeting" and you get something useful done.

Having said that, I do NOT wish my comments to in any way take away from the excellent advice being offered by theDBguy and Dave (Gemma).
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,555
I agree with the other responses you have received. I worked in gov't for several years and the situation you mention certainly arose. As Doc pointed out - someone (higher pay grade) needs the info, so what can he/she do to get access to the data or some more convenient extract procedure?
I had a few experiences where the technical staff knew what they needed, but disparate systems(authorities) prevented getting "all the data from different sources conveniently". The issue was not technical (again as Doc mentioned), it was recognition at the senior levels that the data required was "stove piped". A friendly discussion between these officials made access available and problem resolved.
In other instances after the implementation of online systems involving finances, grant, loans and contributions, static data was no longer available. Some techies decided to do extracts and work with same to do reports --BUT this was an online system and data could change by the moment. Again, after a few discussions with the management involved, we developed month-end, quarter-end, Fiscal Year end "management reporting databases" as part of DB maintenance procedures. This gave user teams the data they needed for forecasting, take-up and related statistics.
Bottom line is this data/info isn't just for you- get the Boss or bosses(patrons) involved.
Good luck.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
That's what I love about working with the military. If your "guy" has enough rank, ANYTHING is possible. At the opposite end of the spectrum is civil service. Those people couldn't care less what you want. No one can tell them what to do. That's why they are called the deep state. They are a power unto themselves and even an act of Congress doesn't carry any weight if they don't agree with it. So they just stall and obstruct until you move on. Corporate America is somewhere in between. There are people who actually consider customer service to be important and are willing to actually do some work to help you as long as your "guy" has enough juice.

Given that, you still might have an issue. In my case, i had to import bank statements. For some reason, the spreadsheet version of our statement didn't have the info we needed so we had to work with the pdf of the statement report. We bought scanning software that could convert the pdf into excel and then we imported them. We couldn't automate everything. Some person still had to download the files but I developed code to import all the files and ensure that I didn't import the same file more than once or miss a month since that would make a mess also. If you think that code would be helpful, let me know and I'll go looking for it. It was several clients and several years ago but I'm pretty sure I kept it.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,573
Pat, if you do it right, you can toss a monkey wrench into the Civil Service wonks too. I've done it more than once. It is all a matter of finding out what THEY want and arranging for a quid pro quo arrangement. And THEY still answer to someone higher up the food chain who wants something.

When it is someone else's problem, you would be surprised at how much you can get away with and never take any blame for non-production. Particularly if you have put it in writing, because if a Civil Servant type becomes obstructive, his boss starts talking about lateral transfers and this gives most CS folks the shivers.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
I've never run into a civil servant who wants anything but to do nothing and be responsible for nothing.

There's a bunch of people at DOJ and FBI who I think need to go to Nome but maybe that's being too political.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Good afternoon,
Therefore, we have to go into a system that allows us to run a report and download the financial data. However, the issue is that we have to run the report by fund (funding source) and organization (department). This means, each month, we run and download into Excel 134 individual files. A report can have 1 to 400 Excel rows of data. We then copy the data from Excel into ACCESS.
I need to deal with this sort of problem, a lot as I often need to interact with external data sources that I can't link to directly. Here are some useful tricks which may help you.

1. Download them as CSV's (if that is an option) or open them and save them as CSV's. Then use a copy command with a wild card character to copy them all into 1 file. That file can be linked to.

2. You can use Access "Dir" command to list files. This would allow you to easily identify missing files.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom