Advice on data management practice (1 Viewer)

HelenaT

New member
Local time
Today, 05:08
Joined
Aug 27, 2019
Messages
9
I am looking for advice on how to handle this data. I use a SQL database that is provided to our program for annual reporting. This database application has limited reporting capability and I do not have access to the back end data.

I export reports from this database into Access, where I process data for reports monthly, quarterly, annually.

I also get data regularly that I manipulate in Access to input to the SQL database via it's import program.

My issues:
1. Do I need to keep "old" data in these Access databases? For ex, in December, do I keep October's data in the Access database? My reasons for doing so is if I have to compare to previous reports, I have the data at hand, and I know that it hasn't changed. We do enter data as we get it, so I could have October data entered in November, so re-running the report in December could be different.
2. I also keep the old data as a way of logging what I have done - especially on the import side. I think there should be another way to do this - ie create a log table -- Is that a good approach?
3. I have created several different databases for different data sources - for the import side, mostly. This mostly because the data comes from different sources (housing data versus pharmacy data, for ex), and thus needs slightly different processing. This also let me keep track of what I have processed. The work is similar, and the end result is the same - the data ends up in the same 2 tables for the import program.

I would like ideas on how to be more efficient with this. I hope I am clear!
Thanks
Helena
 

vba_php

Forum Troll
Local time
Today, 05:08
Joined
Oct 6, 2019
Messages
2,884
I use a SQL database that is provided to our program for annual reporting. This database application has limited reporting capability and I do not have access to the back end data.
Why don't you have access to the back end? If you are the admin, u should have. when you say "SQL database", do u mean SQL server?


1. Do I need to keep "old" data in these Access databases? For ex, in December, do I keep October's data in the Access database? My reasons for doing so is if I have to compare to previous reports, I have the data at hand, and I know that it hasn't changed.
if you ever have to compare data, then obviously you should keep the old data, otherwise how else r u going to be able to compare?

2. I also keep the old data as a way of logging what I have done - especially on the import side. I think there should be another way to do this - ie create a log table -- Is that a good approach?
If your data spans across multiple tables, then i would thinking keeping a "log table" would be impractical. Log tables are used, I believe, for smaller transaction histories, such as an audit trail for form data entry and changes.

3. I have created several different databases for different data sources - for the import side, mostly. This mostly because the data comes from different sources (housing data versus pharmacy data, for ex), and thus needs slightly different processing. This also let me keep track of what I have processed. The work is similar, and the end result is the same - the data ends up in the same 2 tables for the import program.
Well if you're doing business with more than one company (for instance, processing in-house data vs. working with a 3rd party pharmacy) and the data coming from the 2 different entities is not related, then you would prolly want to keep different databases available. You can of course keep everything in 1 database, but if you're doing business with a lot of different entities, you might end up getting confused in the future about "which dataset belongs to who?"
 

HelenaT

New member
Local time
Today, 05:08
Joined
Aug 27, 2019
Messages
9
Thanks for the quick reply. It's SQL server, yes, and I don't have access to the back end because the database resides on a server that belongs to a different group organizationally (I am school of medicine and the server is the hospital) and therefore I am not allowed to have access to the back end.

It's good to know that I am not off base in what I have done so far. Thanks again.
 

vba_php

Forum Troll
Local time
Today, 05:08
Joined
Oct 6, 2019
Messages
2,884
It's SQL server, yes, and I don't have access to the back end because the database resides on a server that belongs to a different group organizationally (I am school of medicine and the server is the hospital) and therefore I am not allowed to have access to the back end.
maybe you should request access to the back end? That might help you along. But healthcare is so stupid quirky with all the laws, security is #1 importance for those people.

It's good to know that I am not off base in what I have done so far.
you might give it a bit of time and see what other people here have to say. They might say something different than what I did. But regardless, I'm glad I could help somewhat.
 

isladogs

MVP / VIP
Local time
Today, 10:08
Joined
Jan 14, 2017
Messages
18,164
Agree with the advice you've been given so far.
1. Keep the old data - if the tables are linked rather than local, this won't add to the size of the Access FE. In addition it is a requirement for GDPR compliance
2. Keep the data for each source separate. You can always swop the BEs as required. Using DSN less connections makes this fairly simple
You may find this parallel thread useful https://www.access-programmers.co.uk/forums/showthread.php?t=307700
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:08
Joined
Feb 19, 2002
Messages
42,872
but if you're doing business with a lot of different entities, you might end up getting confused in the future about "which dataset belongs to who?"
Not if you properly tag the data as you import it.

I have an application where I import bank statements for four different accounts. It is important that no month be missed for an account or imported more than once. To control this, I keep a log table that logs the import file name when it is imported. The code verifies that the filename hasn't been imported previously and that there are no gaps. I keep the path separate from the file name since the path might change if a different person downloaded the files but the file name always includes the account number and the period of the data so it is the file name rather than path+file name that must be unique. Inserting a row in the log table to save the path, filenname, importdate, I use an autonumber as the PK and a unique index for FileName. I then use the autonumber which is called BatchID and include that on each record that is imported. Therefore, every row is tied to the specific file that imported it. This makes it easy to back out an import if the bank sends a correction before the next month. I can delete batch 128 and then replace it with the next available batch number. I flag the log record row as "deleted" so I know what happened to the details. After I import the data from the statement file, I move the file to the archive folder for that account.
 

Users who are viewing this thread

Top Bottom