Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2019, 12:17 PM   #1
Allie12380
Newly Registered User
 
Join Date: Jul 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Allie12380 is on a distinguished road
Check to ensure all imports done

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.

Allie12380 is offline   Reply With Quote
Old 07-10-2019, 12:46 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,793
Thanks: 50
Thanked 1,096 Times in 1,077 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Check to ensure all imports done

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-11-2019, 03:03 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Check to ensure all imports done

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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 07-11-2019, 06:27 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,388
Thanks: 87
Thanked 1,649 Times in 1,531 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Check to ensure all imports done

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).
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-11-2019, 07:00 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,144
Thanks: 82
Thanked 2,010 Times in 1,958 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Check to ensure all imports done

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 07-11-2019 at 08:26 AM.
jdraw is offline   Reply With Quote
Old 07-25-2019, 08:39 AM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Check to ensure all imports done

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-25-2019, 05:40 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,388
Thanks: 87
Thanked 1,649 Times in 1,531 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Check to ensure all imports done

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-25-2019, 10:00 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Check to ensure all imports done

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-29-2019, 03:42 AM   #9
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 161
Thanks: 5
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Check to ensure all imports done

Quote:
Originally Posted by Allie12380 View Post
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.

RogerCooper is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferText - delay/check to ensure fully imported bignose2 Modules & VBA 3 11-06-2018 05:08 AM
Check to ensure form is open before proceeding BonnieG Modules & VBA 2 01-22-2013 07:29 AM
If then/ ensure not Null... Help? morfusaf Forms 3 05-02-2012 07:47 AM
best way to ensure saving changes icemonster Forms 11 09-03-2010 10:06 AM
How to ensure ... Alix Forms 5 05-02-2003 12:34 AM




All times are GMT -8. The time now is 04:14 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World