Hi there.
I am in the process of building a new database that will be located at 2 different locations. (Headoffice & Onsite)
Onsite the database is used all the time and throughout the day many records are added and deleted.
I have a few tables that are joined e.g. (tblPersonalInformation & tblBookingInformation)
With our current database which has only 1 massive table the way we get information to our headoffice is to export the table into excel then cut it into a few pieces then zip each piece to email over our server that only allows small file sizes.
My idea was to have a field in the tblPersonalID that records date modified. I then have another table called tblExportedDates which records when I export the information to excel.
My problem is im not to sure how to do it.
I need a query that looks at the last record within the tblExportedDates (the last record will contain the last import date) I then have a query run using the date from the last record and Now()
The query would then export all the changes to an excel spreadsheet so I can email the changes to headoffice.
I know how to export the query into excel not a problem I just need some help building all the hard stuff.
1. Where do I start with a piece of code that gives me the last record of a table (tblExportedDates)
The next step if I understand it correctly would be a SELECT query statement which I know how to do. The select string would be based on the result from step 1 and the Now() feature.
I would then export the results into excel.
Then an INSERT string which would put the date that the tblExportedDates ready for the next time.
My other question is how can I do this to both tables as I will need to do it to both tables to keep everything matching.
I am in the process of building a new database that will be located at 2 different locations. (Headoffice & Onsite)
Onsite the database is used all the time and throughout the day many records are added and deleted.
I have a few tables that are joined e.g. (tblPersonalInformation & tblBookingInformation)
With our current database which has only 1 massive table the way we get information to our headoffice is to export the table into excel then cut it into a few pieces then zip each piece to email over our server that only allows small file sizes.
My idea was to have a field in the tblPersonalID that records date modified. I then have another table called tblExportedDates which records when I export the information to excel.
My problem is im not to sure how to do it.
I need a query that looks at the last record within the tblExportedDates (the last record will contain the last import date) I then have a query run using the date from the last record and Now()
The query would then export all the changes to an excel spreadsheet so I can email the changes to headoffice.
I know how to export the query into excel not a problem I just need some help building all the hard stuff.
1. Where do I start with a piece of code that gives me the last record of a table (tblExportedDates)
The next step if I understand it correctly would be a SELECT query statement which I know how to do. The select string would be based on the result from step 1 and the Now() feature.
I would then export the results into excel.
Then an INSERT string which would put the date that the tblExportedDates ready for the next time.
My other question is how can I do this to both tables as I will need to do it to both tables to keep everything matching.
