Destructive update on certain records

[Greycom4]

I have a regular import from an Excel sheet range using the TransferSpreadsheet method which populates a ‘Holding Table’ and then this information is used to update and append to a Final Junction table. During each import there is only ever one Holder (PK).

Sample Data in Final Junction Table after first import

Holder (PK)                        System (PK)                         Number
100                                   1                                        10
100                                   2                                        10
100                                   3                                        25
100                                   4                                        15
100                                   5                                        50

Sample Data in Holding Table after holder 100 refreshes it’s data via the new imported spreadsheet

Holder (PK)                         System (PK)                        Number
100                                    1                                        6   
100                                    2                                        6   
100                                    3                                        6

As you can see the last two records have dropped of the refreshed import and my question is how can I delete these in the Final Junction Table. The Final Junction Table will store multiple Holders so I can’t do a destructive update for all records. I could however do a destructive update for only that Holder and then import all the remaining 3 records but I need to know how to pass the current holder number to either a delete query or some VBA.

Looking forward to any advice, regards
ps had to put it in the code to keep the format

 

[CJ_London]

putting your data in code tags to preserve format is the right thing to do. Wish more people did it - saves a lot of time!

I'm assuming your unique ID is effectively a combination of the HolderPK and the SystemPK.

Assuming this is the case after your two tables have been updated with your transfersheets, run a query which would be something like

DELETE Junction.* 
FROM Junction 
WHERE Exists (SELECT * FROM Holding as tmp where holderPK=Junction.HolderPK and systemPK=Junction.SystemPK)=False

You might have this query which is run through DoCmd.RunQuery in VBA or you could use Currentdb.Execute(SqlStr) where SqlStr equals the above

 

[Greycom4]

Thanks CJ London for reading my post and then replying.

I think I understand your query which is brilliant, but it's giving me a syntax error. You are absolutely correct when you say my unique ID is effectively a combination of the HolderPK and the SystemPK. Have I understood your query correctly? I think it says 'delete everything in the Junction Table unless both PK's in both tables match'. Please can you explain the 'From Holding as tmp'

I used alias names in my first post to simplify things.

DELETE jctSKHGenEq.* 
FROM jctSKHGenEq
WHERE Exists (SELECT * FROM HoldingSKHGenEq as tmp where SKH=jctSKHGenEq.SKH and Gen Eq=jctSKHGenEq.Gen Eq )=False

 

[CJ_London]

I think it says 'delete everything in the Junction Table unless both PK's in both tables match'

Correct - in your example, there were 2 records in the junction table which did not have a matching records in the holding table, so they are the ones to be deleted.

'From Holding as tmp'

This is just aliasing the holding table which you should do in sub queries

The syntax error is because you have spaces in your field names. It is better not to have spaces, then you don't get errors like this

Change to

[Gen Eq]=jctSKHGenEq.[Gen Eq]

 

[Greycom4]

Thanks for your support and quick responses, also I acknowledge the 'school boy error' around the field name spaces .I have completed the changes and run the query which removes all the non matching records in the junction Table which is great. Unfortunately this also deletes any other records which exist in the table that have been provided by other holders that I need to preserve.

The Final Junction Table will store multiple Holders so I can’t do a destructive update for all records

I'm hoping you can help me with this last piece as I will have multiple range imports that will all follow your same solution. Just to recap, the two Red records should not appear in the junction table as per your query, but I need to preserve any any other Holder such as the two 600 records

Current Data in Holding  Table

Holder (PK)			System (PK)			Number
100				1				7
100				2				7
100				3				7




Current data in Junction Table

Holder (PK)			System (PK)			Number
100				1				7
100				2				7
100				3				7
[COLOR="Red"]100[/COLOR][COLOR="Red"]				4				15
100				5				50[/COLOR]
600				1				40	
600				2				30


Final Result in Junction Table After Query is Run

Holder (PK)			System (PK)			Number
100				1				7
100				2				7
100				3				7
600				1				40	
600				2				30

Curent code

DELETE jctSKHGenEq.*
FROM jctSKHGenEq
WHERE Exists (SELECT * FROM HoldingSKHGenEq as tmp where [SKH FK]=jctSKHGenEq.[SKH FK] and [Gen Eq FK]=jctSKHGenEq.[Gen Eq FK])=False;

Apolagies for not making myself clearer at the outset.

Kind
Regards

Greycom4

 

[Greycom4]

CJ,

I was wondering if you would consider looking at my problem once more, I know your busy as I see lots of post from you on this forum. I am really keen to get some help here as I have many junction tables that I need to import in to for this database. If i can just get this one working it will be the perfect model to implement on all the others



Regards
Greycom4

 

[CJ_London]

I think the only way you could do this is include something like a Import Session ID in jctSKHGenEq and limit your delete query result to records in jctSKHGenEq which match the session ID.

1. Add a new field to jctSKHGenEq called SessionID of type long

2.

DELETE jctSKHGenEq.*
FROM jctSKHGenEq
WHERE [COLOR=red]SessionID =Forms!Import!SessionID AND[/COLOR] Exists (SELECT * FROM HoldingSKHGenEq as tmp where [SKH FK]=jctSKHGenEq.[SKH FK] and [Gen Eq FK]=jctSKHGenEq.[Gen Eq FK])=False;

This presumes you have an open form called Import with a control called SessionID.

There are a number of ways of doing the following, but this is probably the simplest to implement.

3. To create your sessionID I would create a table called Sessions with two fields

SessionID autonumber PK
SessionDate Date

Then populate a number of records

4. In your import form create a new unbound control called SessionID and in the controlsource put =DMin("SessionID","Sessions","isNull(SessionDate)=True")

5. Modify your update and append query to update the new SessionID field with the same DLookup as above - or refer to the Form variable

6. Once your importing and appending session is finished, either manually or with an update query update the Session table with the date of the session

As a side benefit you will at least be able to see when a record was imported.

Another alternative is to include a timestamp field in jctSKHGenEq with default set to now(). Then depending on the circumstances you could simply change

WHERE SessionID =Forms!Import!SessionID AND

to

WHERE TimeStamp=>SomeDateTime AND

 

[Greycom4]

Thanks CJ for your advice and guidence. I would like to try both suggestions, however I do have around 6 junction tables that would then require setting up multiple forms and Session Tables. So in the interim I will go with the

Another alternative is to include a timestamp field in jctSKHGenEq with default set to now(). Then depending on the circumstances you could simply change

Are you suggesting I would require to change the

=>SomeDateTime AND

manually at each import, also when I Update existing records in the Junction Table the import time stamp does not change, is that correct ? I think I need to get my head round it.

 

[CJ_London]

SomeDateTime is up to you - the idea is that you have ran updates yesterday so the timestamp has been populated with date times of say 16/10/2013 14:20:22 and later, and today you are running it again so the timesstamps are populated with 17/10/2013 13:41:11. You will have run the delete after yesterdays import so you only want to run the deletes on whatever has happend since the last delete.

So I would suggest the SomeDateTime is the time you ran the last delete which you would need to store in a table somewhere. However I don't know your routines, for all I know you are doing this around midnight but if you always run this during the day and complete all operations during the same day then you could use

TimeStamp>=Date() AND

 

[Greycom4]

CJ, I have implemented the timestamp process in my junction table to trigger a snapshot in time of when the data was imported.
During my initial testing I have used the criteria of >=Date() -1 in place of

=>SomeDateTime

which works. However It is possible that I can receive multiple holders Imports in one day. To that effect this means I could have 3 or more holders with the same import stamp and therefore I think they would be included in the delete query ?

I’m wondering if it would be best to do a destructive delete query on the Junction table based on the Holder number sitting in the import/holding table and then append all the records from the import/holding table. When we receive import data It is the ‘single current version of the truth’ . I’d be interested in your thoughts.
Destructive Query:

DELETE jctSKHGenEq.[SKH FK], *
FROM jctSKHGenEq
WHERE (((jctSKHGenEq.[SKH FK]) In (SELECT HoldingSKHGenEq.[SKH FK] FROM HoldingSKHGenEq)));

Thank You

 

[CJ_London]

I would go for my other suggestion

So I would suggest the SomeDateTime is the time you ran the last delete which you would need to store in a table somewhere

 

[Greycom4]

CJ, your response time is phenomenal....but how can this work If I have more than one import in a single day meaning multiple holder details with the same date stamp ending up in the junction table, what am I missing here ?

 

[CJ_London]

multiple holder details with the same date stamp ending up in the junction table

They won't - it is a timestamp.

So Day1:

The first day - you do some imports on 25/9/2013 starting at 11:15 and finishing at 12:45

At 12:50 you run the delete query. Since this is the first day there is no previous time so the value stored in 'LastRun' (in a table with just this one field) is 0, so the value is 0, therefore the criteria is

WHERE TimeStamp=>SomeDateTime AND

which for the first row translates to
WHERE 25/9/2013 11:15:00>0

When the delete process is completed the LastRun field is updated to now() which is say 25/9/2013 12:50:45 - this can be done in the VBA you are using to do your imports and deletes

So, The next day you run some imports between 13:45 and 14:20 and then run the delete query at 14:22

WHERE TimeStamp=>SomeDateTime AND

which for the first row translates to
WHERE 25/9/2013 11:15:00>25/9/2013 12:50:45 - it isn't so it is ignored
but
WHERE 26/9/2013 13:45:00>25/9/2013 12:50:45 - is so is deleted subject to other criteria

As the previous day when the delete process is completed the LastRun is updated to now() which is say 26/9/2013 14:22:45

The same day, you have some more imports to run, which are run between 14:40 and and 14:45

and again you run the delete query

WHERE TimeStamp=>SomeDateTime AND

which for the first row translates to
WHERE 25/9/2013 11:15:00>26/9/2013 14:22:45 - it isn't so it is ignored
a later row from the first run of the day
WHERE 26/9/2013 13:45:00>26/9/2013 14:22:45 - it isn't so it is ignored
but the latest import
WHERE 26/9/2013 14:40:00>25/9/2013 14:22:45 - is so is deleted subject to other criteria

Hope that makes sense

 

[Greycom4]

Thanks CJ for the detailed explanation, I was using dates without the time in my testing to simplify things and completely missed the crucial time part of the process. So I understand the limiting the data in the query results and I will get to testing this evening. Since I have a number of these import to various junction tables I will investigate having a single TimeStamp table with a catagory field for each type of import.

I appreciate your time.