Creating an Archiving function for a database (Design)

[dragon232]

Hi everyone,



We're using a Microsoft Access file to keep track of monthly salaries, and currently have 1 file that we've saved on the computer for each month in the past. Instead of copy/pasting the file for future months, we want to have an entire program with an archiving function. Basically my question is what should be taken into account in the design of the archiving function?

At the moment, we have four tables/queries that need to be copied.

1. A Taxes Table, containing the current month, and the level of taxes applied for each employee that month (a VBA function extracts the taxes percentages and uses them to calculate salaries for each employee)
2. A Salaries Query that has one record for each employee with their salaries, bonuses, taxes and everything else.
3. A Summary Query of the above mentioned Query (to summarise things like total salaries paid, total taxes paid by kind, etc.)
4. A Bonuses Table which can have multiple entries for each employee (the total bonus for each is added up and can be found in the Salaries Query)

Now I'm thinking to make 4 tables which will host all this information in an archive format, which have the same structure as the queries/tables they will be hosting with the addition of a date field for the respective month. These will be created using 4 Update Queries, which will be run every time the user wants to archive.

So these four tables will be as follows:
- A Past_Taxes table which holds 1 record for each time the archive program gets run containing the date of the month (DOM) + the 1 record contents of the Taxes Table
- A Past_Bonuses table which holds all the records of bonuses. Basically when the update query will be run, the records contained by the Bonuses Table will be pasted in here and a date added to all of them (the same as the previously mentioned DOM)
- A Past_Salaries Table which holds one record for each employee with the added DOM field for each.
- A Past_Summary_Salaries Table which holds 1 record for each DOM value summarising the data for each month.

Now the DOM value will be the indexing field for the Past_Taxes table, and for the Past_Summary_Salaries Table but not in the other two (since there will be more than 1 record with the same DOM value). So when I make the macro to run the 4 update queries a possible problem will arise if the user accidentally hits the update button twice, etc. When the macro will run the update query for the Past_Taxes table, and for the Past_Summary_Salaries_Table it will fail - so no records will be added (because there already is a record there with the specific DOM value). However - the update query will work for the Past_Salaries Table and for the Past_Bonuses table (these two tables have a Many-to-One relationship with the Past_Taxes and Past_Summary_Salaries Tables). Is there any way to prevent the macro from running further if the first two update queries fail? If so, how? If I can prevent that, then no errors could lead to corruption of the data.

After that it seems quite straight-forward, we can apply filters by the date to extract the month(s) we're interested in from the database. So what do you think? Is there a better way to design this? And how should the macro-issue be solved? Thanks!

[Bulzie]

I assume you have the year in the Taxes and Bonuses table. So on a secure form that say only an admin can edit, set a Begin and End Date that will run your queries and reports so you will only be looking at the most current year or date range. Your old data just stays in the original tables and you could create historical reports from it as needed?

You could do archiving tables but sometimes it gets messy keeping it all in sync.

[dragon232]

I assume you have the year in the Taxes and Bonuses table. So on a secure form that say only an admin can edit, set a Begin and End Date that will run your queries and reports so you will only be looking at the most current year or date range. Your old data just stays in the original tables and you could create historical reports from it as needed?

We have a date of the month in the Taxes table. This is contains the year and the month, and the date of most of the bonuses. Say this is 30/12/2016 - that would mean that the current month in question is 12/2016 and the day most of the bonuses were given was the 30th. There can be bonuses given on other dates though. So each employee can have more than 1 bonus - the total is calculated and added into the Salaries table for each employee.

The problem with what you're saying is that the file isn't set that way. For example, the taxes table is set to contain only one record and it always contains only one record. The idea is to keep the current month program separate from the archive. That's why current months sit in their own tables, and then they get moved to some other tables for archiving. The Past_Taxes file though can contain many records (1 for each month)

Furthermore, to do what you're suggesting would mean to have a table containing month/year dates + employees names + hours they worked etc. and then the Salaries query on top of that, filtered by begin/end date so there is only one of them. This would mean that every month someone needs to introduce all previous employees into this table again, with a different month date, etc. which is very error prone. The current way, the employees are introduced once. They stay there for the next months without change, or another can be added, or deleted, without impact upon the archive.


The archiving table cannot possibly be out of sync with the current month tables because they're not related at all and in anyway. The only possible problem is hitting the archiving button twice really. Or the user forgetting they have archived. But this could all be prevented because the two update queries for the Past_Taxes and Past_Summary_Salaries Tables will not work - so, as long as the macro stops running the queries if those two add zero records, then no error can happen.

Is there any way to give the update query macro a condition such as, say, execute Query 1, execute Query 2, if both failed to add any records, exit, otherwise execute query 3 and query 4?

[Bulzie]

Databases are designed to hold and use data, the power of Access is the ability to limit the data you interact with using Queries and VBA. But If you want to do the archiving you mentioned above, you can just check the key fields in the Archive tables when you run the process to move the records and if they already exist, just skip moving them over. Can you explain the timeline or process on the archiving? Will the archiving happen on say each Employee's start Month or the beginning of fiscal or calendar year? So on one of these dates the employee starts over again with new/current salary, taxes and then accrues the bonuses throughout the year?

[dragon232]

Will the archiving happen on say each Employee's start Month or the beginning of fiscal or calendar year? So on one of these dates the employee starts over again with new/current salary, taxes and then accrues the bonuses throughout the year?

The archiving happens once every month. Basically when the current month is over, it gets archived. The tables, queries, etc. for the current month then don't get changed (apart from the bonuses table which gets wiped out of all its records via a macro, and all employees are re-inserted [also via the macro] with the new date (specified in the Taxes table) into the empty Bonuses table. Employees are given bonuses every month mostly, because it's the legal way to pay them earlier than the usual date they get their salary. An excel sheet is kept in alphabetic order with the employees, and someone counts how many hours they work, how many days of medical leave, etc. and then all this information is copy/pasted into a form datasheet (which is possible because they are in the same alphabetical order in both places) - this is taken by an updatable query which calculates their salaries given a reference level salary (which can be changed by the user), and the taxes involved, taking into account all sorts of different factors (such as if their medical leave is insured or not, etc.).

The point really is that we rarely if ever need the archive. We only need it if some legal organ demands to see, say our activity from 2 years ago in the month of May. Then we access the archive, and get the necessary reports out. Otherwise, the program is focused on the current month. Producing reports, sending them to accounting, etc. And I can't have one query for the current month and archive because if the law changes, and the calculations of the query change, then the archive calculations will also change (and this shouldn't happen). That's why archive has to be in separate tables, so it can't be easily edited, etc.

What would be the way to check every record of a certain field in the archive table for a certain value? How can I get it to check every record in the Past_Taxes table, in the Date field for a certain date without, say, writing a VBA while loop going through each record? I guess maybe an if then macro clause, with the condition that if isNull(DLookUp("[Date]", "Past_Taxes", "[Date]=Whatever_date")), then run the macro, otherwise exit.

[ssanfu]

I'm going to have to lean towards Bulzie on this one. You seem to be doing a lot of extra work.
From what you have explained, it sure sounds like you are trying to use Access as an Excel spreadsheet.
If your archive table only have 1 months data in it, how do you get EOY totals by employee?

How many employees per month are we talking about?

An excel sheet is kept in alphabetic order with the employees, and someone counts how many hours they work, how many days of medical leave, etc. and then all this information is copy/pasted into a form datasheet (which is possible because they are in the same alphabetical order in both places) - this is taken by an updatable query which calculates their salaries given a reference level salary (which can be changed by the user), and the taxes involved, taking into account all sorts of different factors (such as if their medical leave is insured or not, etc.).

I do not understand why Excel comes into play. And it really sounds like the Access tables are set up like spreadsheets......

As far as Macros, I never use them, never have; just VBA code. Sorry.



I would be interested in seeing your dB.... just 5 - 10 records - use cartoon character names.....

[dragon232]

I'm going to have to lean towards Bulzie on this one. You seem to be doing a lot of extra work.
From what you have explained, it sure sounds like you are trying to use Access as an Excel spreadsheet.
If your archive table only have 1 months data in it, how do you get EOY totals by employee?

No you misunderstand. Only the current months table has 1 months data. The archive table has everything, all the months. As for how I can get EOY totals by employee (we never need them, so we never get them - but, if we did) you just filter by employee identification number and filter between this and that date and have a query on top of the past_salaries_table adding them up.

How many employees per month are we talking about?

50ish

I do not understand why Excel comes into play. And it really sounds like the Access tables are set up like spreadsheets......

No only a few data-entry sub-forms are set that way. As for why Excel is used, it's because, for example every day an employee works, someone adds an X in a table, and writes the numbers of hours worked. This data is then summarised into a few columns in excel automatically - like full days worked, medical leave, insured medical leave, etc. then these columns are copy/pasted for everyone in the salaries file. It literarily takes two seconds, why would you think this is complicated? Would it be better if the user had to enter the data manually into access for each employee, one by one from that excel file which is used? And if the excel file isn't used, then the data can't be entered straight into access unless someone keeps track of it in a means other than excel. I don't see another way?

[ssanfu]

Sorry, just trying to understand the process. It (now) sounds like you have a spreadsheet that has everyone's name, someone enters the hours worked every day, then at the EOM, a total of the hours for everyone is pasted into an Access table.

Would it be better if the user had to enter the data manually into access for each employee, one by one from that excel file which is used? And if the excel file isn't used, then the data can't be entered straight into access unless someone keeps track of it in a means other than excel. I don't see another way?

If someone enters the data into Excel, why couldn't they just enter it directly into Access instead of Excel? Let Access do the summarizing (totals query)?

Granted, your structure is not the way I would use Access, but I'm not trying to change your mind/process..... just suggesting alternatives.

At one time we had a dB with 3000 - 4000+ emp to enter hours monthly. (We now import a CSV file.) There was/is a form (form still exists for onesies/corrections) that adds all employees and monthly hours were entered (plus a couple of other fields). (Hours were reported from our clients)

Is there any way to give the update query macro a condition such as, say, execute Query 1, execute Query 2, if both failed to add any records, exit, otherwise execute query 3 and query 4?

Don't know about macros.... But in VBA it would be easy. You could check the "Past" tables to see if a record exists for the DOM for the two tables, then continue or stop the update queries.

Question: in all of the tables, is there a date stamp field (date and time)?

[dragon232]

Sorry, just trying to understand the process. It (now) sounds like you have a spreadsheet that has everyone's name, someone enters the hours worked every day, then at the EOM, a total of the hours for everyone is pasted into an Access table.

No worries, my explanations weren't that clear either. Yes, I think you have the right idea now.

If someone enters the data into Excel, why couldn't they just enter it directly into Access instead of Excel? Let Access do the summarizing (totals query)?

The reason we can't do this is because our boss (and myself) are the only ones who get to see the Access file which contains everyone's salaries, bonuses, etc. These are meant to be secret in the sense that one employee shouldn't know what the other is earning. At best, if the person entered the data in access, we'd still have to transfer it to the database that my boss uses to give the salaries so the data can be processed. On top of that, he probably wouldn't know how to import stuff into access except by copy/paste, and he's more familiar with excel, so that's how he wants it.

Granted, your structure is not the way I would use Access, but I'm not trying to change your mind/process..... just suggesting alternatives.

Yes I understand no worries. I'm here just to discuss possibilities, but at the same time I'm aware that it's difficult for others to know exactly all the problems that I (or anyone else really) is facing. But I've been helped quite a by forums like this one, because it just offers different alternatives, so thank you for that.

Don't know about macros.... But in VBA it would be easy. You could check the "Past" tables to see if a record exists for the DOM for the two tables, then continue or stop the update queries.

Yes, that's how I actually ended up doing it as well, because I couldn't find the necessary macros pre-written... But if it's within the possibilities that access already has, I always prefer to use those.

Question: in all of the tables, is there a date stamp field (date and time)?

In all the current months tables there isn't. The date appears only in the "Taxes" table (and obviously gets sent via a VBA function to wherever it is needed such as current months reports). However, in the the archive tables, I must have a date stamp field in each and every table, because otherwise I can't filter to obtain the data that I need. In the meantime I got rid of the Past_Taxes table because we don't need it (we're not doing anymore calculations, so why would we?), so we're down to three archive tables for the Access (but this is quite possibly liable to change...) - the Past_Salaries table, which basically has one field for time stamp (the time stamp is passed from whatever the date set in the Taxes table is when the records are added), and each record is the employee for the specific month (each employee of course has a unique identification number, but which can't be the primary key of this table as it would get repeated in the next month), a Past_Summary_Salaries Table - where basically one record illustrates the totals for a particular month, and a Past_Bonuses table, which illustrates the dates when bonuses were given and the employees in question (this has its own date).

Now I do have a problem at the moment... namely we have two companies actually in the group. So the totals by month in the Past_Summary_Salaries table at the moment are always the totals for both companies combined. I should, and am probably going to change this to a query and get rid of the table. Then we'll have just Past_Salaries table and Past_Bonuses table and the query on top of Past_Salaries, which can be filtered to obtain the results needed. I'll update in due time, but do let me know if you have other questions/proposals!

[ssanfu]

for example every day an employee works, someone adds an X in a table, and writes the numbers of hours worked. This data is then summarised into a few columns in excel automatically - like full days worked, medical leave, insured medical leave, etc. then these columns are copy/pasted for everyone in the salaries file.

The reason we can't do this is because our boss (and myself) are the only ones who get to see the Access file which contains everyone's salaries, bonuses, etc. These are meant to be secret in the sense that one employee shouldn't know what the other is earning. At best, if the person entered the data in access, we'd still have to transfer it to the database that my boss uses to give the salaries so the data can be processed. On top of that, he probably wouldn't know how to import stuff into access except by copy/paste, and he's more familiar with excel, so that's how he wants it.

Do you have to or do you keep the Excel workbooks for each month? At the end of the year you would have 12 Excel workbooks?

Your Access dB should be split - the BE (back end) holds only tables (the data) and the FE (front end) has everything else- forms, queries, reports and modules.

Both you and your boss would each have a FE on your computer that has all forms, reports, etc in it, linked to the BE.
A data entry person (1 or more) could have a specialized FE that has one form and is linked to one table, the current month table. No other table is linked, so no other data (ie salaries) is available.

Now I do have a problem at the moment... namely we have two companies actually in the group. So the totals by month in the Past_Summary_Salaries table at the moment are always the totals for both companies combined. I should, and am probably going to change this to a query and get rid of the table

You can't get rid of a table. That is where the data resides! But, in a query, you can group by company (totals query) or filter by company.



You keep deleting tables and you won't need Access at all!