Outer join

[JBirdieH]

I am trying to document database data changes from one week to the next. My qbe question is what records from last week don't exist this week (making sure nothing erroneous is happening). The problem starts because I need ALL the fields involved. This takes forever and/or fails. Is there a neater way?

ANSWER: RIV:ANSWER.DB

N:\dbonlyverify\Title\recorded.DB
| Instrument | Geo5 |
| Check _join1! | Check _join2! |

N:\dbonlyverify\Title\recorded.DB
| PlatCODE | Division |
| Check _join3! | Check _join4! |

N:\dbonlyverify\Title\recorded.DB
| Block | Lot/Tract |
| Check _join5! | Check _join6! |

:tpost:RECORDED.DB
| Instrument | Geo5 | PlatCODE |
| _join1, count=0 | _join2, count=0 | _join3, count=0 |

:tpost:RECORDED.DB
| Division | Block | Lot/Tract |
| _join4, count=0 | _join5, count=0 | _join6, count=0 |

EndQuery

 

[JoeNg]

Without knowing what your data structure look like, or the data relations, I could only address this in general term.

I would:
Create secondary indexes.
Minimize the # of fields to match or extract
May be you could create an Id field for each record and do the initial match against the id fields.
Minimize the # of records to work with.
Is that other criteria that you can use to narrow the dataset down to a smaller subset?
Like a date field?
If the data are on the network, extract what you want and work on them on your private or local drive.

 

[footpad]

JBirdieH,

I think JoeNG mentions sme good ideas, but I wonder why you don't simply maintain a LastModified field (@) and check that instead of doing a complex not-in query.

Depending on your form, data model, and so on, you could save the date (and time) of the last update by overriding the DataUlock record action. Something along the lines of the standard "fly-away" code should do the trick.

Hope this helps...

-- Lance

 

[JBirdieH]

Adding a change date is definitely something I will do, but actually I am trying to see if records are being erroneously deleted (or dissappearing-which is what some users claim--even though in my experience table problems don't usually present themselves with individual field changes). Maybe it would be better to prevent deletions entirely and add a field to check for deleted? How would I totally prevent deletions?

 

[footpad]

JBirdieH,

Preventing deletetions is a tall order. You need to first ensure that all access is handled through the forms you provide.

To do this, add code that overrides the DataDeleteRecord action to either block or override the default behavior. Best way, in my experience, is to secretly track deleted records in shadow tables. That way, you can restore the data that's accidentally lost, while letting users do what they think they need to.

To do so, add code to the action event--before the default behavior. Look for the dataDeleteRecord value of eventInfo.id() and then store the record into a separate table. I'd also add a field that indicates the action and who took it.

Hope this helps....

-- Lance