save weekly changes to a table

[filiep]

Ok here's what I'm trying to do.

We import data to a table on a weekly basis. We delete the table and replace the data. Most of the data is the same. It's date's, group names, ...

Now we would like to save the weekly table and add a saved date. Then we would like a query that puts all the saved tables together and that we could filter.

For example. A team is planned to do a work on 01/01/13. The next week it has changed to 07/01/13. So the data in the table it updated but we can't track the changed date. If we had the different tables we could filter that team and so that the work was originaly planned on date x to be done on the 01/01/13, on date y it was 07/01/13....

Of course if we would save the complete table every time it would mean a lot of useless data that hasn't changed. So a better solution would be that a query could see wich data has changed and it only saved those lines on that date.

Thx in advance

Regards,

Filiep

 

[CJ_London]

Why can you just append the new data to your table and have an extra field as a timestamp defaulting to Date()?

Not sure how you are importing at the moment but if you do the following:

1. Copy you spreadsheet to a directory and give it a generic name - lets say SSImport
2. Create a linked table to this spreadsheet
3. Create an append query from the linked table to your main table - providing you have a field or fields which are unique for all time in your spreadsheet you can exclude those records which are already imported from previous weeks
4. run the query
5. The following week, copy your next weeks spreadsheet to the same location and name as in 1 above
6. run the query

To design the query, will need to understand your data better so if you can post a copy of the spreadsheet with sample data and highlight the column or columns which make each row unique for all time I can suggest a query for you

 

[filiep]

Thx for the reply. The sample is added in a zip file.

The weekly table has 1670 rows. The data is a download from SAP where each line is an activity that someone has to do. The download is to excel and imported with a script into access. It deletes the previous table and replaces it with the new one.

Take for example the first line of the file. "activiteit" 1806. It has a field "VrStBasis". That's when the work is planned to start. When we change it in SAP the new download will display a different date. So we loose the original date it was planned.

The ideal would be that we could have a table where we have a primairy download to start with (the 1670 current rows) and that every time we do an update in access of the table it checks if there are different rows. If the rows are not identical it should add the row with the date it has been added.

That way we can filter for each activiteit (activity) and see the changes .

Filiep

 

[CJ_London]

Which column or columns are unique so the system can say 'this has changed' - it looks like it might be the Netw_t column

 

[filiep]

every item can change but we only want to keep the changes in VrStBasis and WerklEinde. VrStBasis is the starting date. The WerklEinde is the date that the work is reported to be finished.

 

[CJ_London]

I'm sorry, you are not making yourself clear.

When you say

but we only want to keep the changes in VrStBasis and WerklEinde

Do you mean you want to import just these changes - in which case import against what? and why do you not need group names as mentioned in your original post?

In the first row of your sample (Activiteit=1806), if the record was to come through again with changed data in VrStBasis and WerklEinde - what in the first row do you compare it with to see if it has changed?

You mention the Activiteit field but in the sample this is repeated 7 times - if this is the field you are measuring to do you want all 7 rows importing or only one - in which case which one and why?

At the moment, from what you have told me and assuming Netw_t is the unique column then you would have a query like this.

INSERT INTO Master
SELECT DISTINCT SS.*
FROM SSImport AS SS LEFT JOIN Master AS MS ON SS.Netw_t = MS.Netw_t
WHERE (((Nz([SS].[VrStBasis]))<>Nz([MS].[vrstbasis]))) OR (((Nz([SS].[WerklEinde]))<>Nz([ms].[WerklEinde])))

Master is the name of your table in your db that you want to append to
SSImport is the name of your linked table linked to your excel spreadsheet

Note that this query is not complete and needs further work but you need to explain more clearly what you require. However you may be able to work this out from the sample above

 

[filiep]

Sorry try to explain it:

but we only want to keep the changes in VrStBasis and WerklEinde

I mean that we only want to keep the rows where the date's in VrStBasis and WerklEinde are different then the week before. So if these are changed the whole row should be saved along with the previous changes to that row.

There's nothing unique because every activiteit(activity) can be in every netwerk (network). Meaning that the Netwerk is the subgroup in SAP and it contains multiple activiteiten so netwerk 20185471 can have activiteit 200 1806 3000 and all will be in the list. Only the combination of both makes it unique but that's something i can filter.

Example add and text in yello. So it's for example the same row but only the date changed and both should be saved so there's a possibility to track the changes.

 

[CJ_London]

OK, so to go back to my original question about what defines a row where a date has changed - how do you determine that the date has changed- do you need to compare every single column, except the two date columns? Since your feed is from SAP I can believe it

But usually you would be limited to a few columns so perhaps Activiteit and Netwerk? or Activiteit and Netwerk and Netw_t?

 

[CJ_London]

you say

Only the combination of both makes it unique but that's something i can filter

If these are the two columns that make it unique then the solution I provided can be adapted.

 

[CJ_London]

based on what you said here

There's nothing unique because every activiteit(activity) can be in every netwerk (network). Meaning that the Netwerk is the subgroup in SAP and it contains multiple activiteiten so netwerk 20185471 can have activiteit 200 1806 3000 and all will be in the list. Only the combination of both makes it unique but that's something i can filter.

try this query - set up as previously advised

INSERT INTO Master
SELECT DISTINCT SS.*
FROM SSImport AS SS LEFT JOIN Master AS MS ON (SS.Netwerk = MS.Netwerk) AND (SS.Activiteit = MS.Activiteit)
WHERE (((Nz([SS].[VrStBasis]))<>Nz([MS].[vrstbasis]))) OR (((Nz([SS].[WerklEinde]))<>Nz([ms].[WerklEinde])))

 

[filiep]

Ok thx I'll try it.

Is there a possibilty to have a query that automaticly searches excel files in a folder and returns the result in a table? For example if where where to save the weekly table in seperate weekly excel files. Could a a query search these excel files and add the matching rows to a table?

 

[CJ_London]

You can't do it in a query but you can in vba with a few provisos

You need to know where to look in each workbook - which sheet/cell/row or some sort of 'signal' in each workbook so access knows where to look - might be a specfic range name or an index sheet for example.

If you want to do this I would start a new thread - in principle it is fairly straightforward but when I had to do something similar - importing some 1000 spreadsheets - I found there were nearly 100 variations due different locations within the workbook, columns in different orders or spelt slightly differently etc. What should have take a few minutes to run ending up taking several weeks to resolve.

In principle your vba runs in a series of loops

loop through each directory
    loop through each workbook
        loop through each worksheet
            import data as required
        next worksheet
    next workbook
next directory