Allie12380
New member
- Local time
- Today, 07:21
- 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.
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.