multiple table query

[shapman]

Hi all,

Another month, a new database, more questions... a quick thankyou to all those who helped with my last database, much appreciated!


I am trying to set up a query run from 12 tables, each table is supposed to be the same but we have had a number of people changing things when they shouldnt have been. Each table is a month of posts in our employee headcount, each post has a ID Key which shuold stay the same throughout, some of these IDs have been deleted when they shouldnt have been, some more have been added when, again, they shouldnt have been. Each post ID has a set FTE number (dictates the hours they do a week), some of these have changed, again when they shuoldnt have, some have changed when they shuold have and we have a record of these ones.

I need to be able to check which post IDs have been deleted, what month (or table) they were first deleted from. What post IDs have been added and what month/table these were first added. I guess i use the first table as the reference point and also that maybe layered queries (compare tables 1st month/second month and then do a query on those results against 3rd month and so forth but not sure how this would pick up changes from the first month...) but not sure quite how to do this and whether this is the right way to do things. The next thing to do will be to check the FTEs but will wait until i sort this query out first.

thanks in advance

Shapman

 

[Dennisk]

It sounds as though you need to protect the db first. if you apply workgroup protection you may prevent this sort of thing happening in the future.

However once a record has been deleted you cannot recover it.

One standard technique is set the data property for the form so records cannot be physically deleted. You can then add a deleted flag to the table along with the name of the current user and the date last modified. Then create a delete button that only sets the delete flag, the date last modified and the CurrentUser() (if the db is workgroup protected then this function return the login name of the person using the db on this PC).

 

[shapman]

thanks for your reply,

I inherited the database at the beginning of the year, old monthly copies of it have been saved in spreadsheets, im going to bring these all back into a new database for a comparison check but at present arnt sure how to do this. I just need to know from the original database (or spreadsheet as it is now held in) which ID numbers are missing or have been added on. I guess that each month has to be checked against the previous month and the original at the same time... im hoping theres a quick way of doing this (preferably all databases at once)....

thanks in advance

Shapman