Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    correct SQL (Access 2000 all updates)

    The code below is part of the removing one person from a 2 person record and creating a new record. There is a subform in which the EnvNbr is the important factor, and both persons in the record have an EnvNbr (assume one person had EnvNbr 13 and the other had EnvNbr20).

    What I need to do, following the creation of the new record, is to be able to update the UniqueID field in tblNewGivings with the value from lngNewUniqueID, but only where the EnvNbr matches the EnvNbr on the subform that relates to the person being moved to the new record.

    This SQL puts the lngNewUniqueID value into the table but for both EnvNbr 13 and EnvNbr20, where I want the update to apply only to EnvNbr 20.
    strSQL = "UPDATE tblNewGivings SET UniqueID = " & lngNewUniqueID & _
    " WHERE UniqueID = " & lngOldUniqueID
    DoCmd.RunSQL strSQL

    How do I correct the SQL to reference the EnvNbr value in the subform? It's called [tblEnvelopeNumbers subform]

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: correct SQL (Access 2000 all updates)

    What is the relationship between tblNewGivings and tblEnvelopeNumbers, i.e. on which field(s) are they joined?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: correct SQL (Access 2000 all updates)

    Hans
    They are joined on EnvNbr

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: correct SQL (Access 2000 all updates)

    How can they be joined on EnvNbr if there are two records in the subform with different values for EnvNbr?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: correct SQL (Access 2000 all updates)

    Hans
    I worked out this system with an EnvNbr and an AssignedTo designation. Thus, John and Mary Smith could have a joint EnvNbr, in which case the AssignedTo designation is "A", or they could have individual EnvNbr's, John having an AssignedTo designation of "B" and Mary having an AssignedTo designation of "C"

    tblEnvelopeNumbers is related to tblTrinity (the members' table) by a UniqueID field.
    tblNewGivings is related to tblTrinity by the UniqueID field.

    tblEnvelopeNumbers is related to tblNewGivings by the EnvNbr field because otherwise donations can't be tracked. Those two tables won't relate to each other on the UniqueID field. I have tried to do that but Access keeps telling me that there is no unique index in the primary table by which the join can be made.

    So I have become worried about ending up, somewhere down the line, with orphaned records. To try and solve this, I am forcing the UniqueID value into the tblNewGivings at the time of donations data entry. I have to force it in by code because it won't populate there naturally.

    I know that this is an unusual database, and the problem is that EnvNbr's must be able to be discontinued and reassigned. This is what causes me to lose sleep, trying to work out all the intracicies.

    I have had the database running for about 5 years in a much simpler way, by which if a donor changed contribution categories (from regular to PAR, automatic cheque) his EnvNbr would change and all of his existing records would change to the new EnvNbr. This was because the EnvNbr was in tblTrinity and related directly to tblNewGivings, with Referential Integrity and Cascading Updates applied.

    But I have been persuaded that changing the EnvNbr violates the integrity of the original data, and that it wouldn't stand up to a forensic audit, so am trying to rebuild the database to satisfy that. However, I keep wondering if changing one row on existing records is that huge a violation. After all, if the donor's name changed we would change his records accordingly. The EnvNbr is less of a personal identification matter than his name and, in fact, more an arbitrary designation.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: correct SQL (Access 2000 all updates)

    I think what you're going to have to do is make EnvNbr an index but not a unique key except in combination with another field. If you use an automnumber key in the envelope number table and you insert *that* key instead of the envelope number into the foreign key field in other tables, it won't matter if you reassign an EnvNbr because that is not the primary key or the relating field. And yes, changing EnvNbr if that *is* a primary key does invalidate your database integrity, even if it is only one row in a table. The fact is that that change cascades throughout the database, and since you have already said that an EnvNbr can be reassigned to someone else, how would you be able to determine exactly who a payment belonged to after such a change?
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: correct SQL (Access 2000 all updates)

    Charlotte
    Thanks for your reply.

    In my original database, the EnvNbr was part of the donor's table. When a person's EnvNbr changed all his existing records changed with it. So if the EnvNbr was reassigned it didn't matter because no old records with that EnvNbr existed.

    In the redesign, the EnvNbr is in an EnvelopeNumbers table, related to the donor's table by UniqueID, and then the tblNewGivings relates to the EnvelopeNumbers table. The EnvNbr's have StartDate and EndDate fields, so they can only be reassigned to somebody else if discontinued to the current holder of that #.

    I need to satisfy 2 concerns: (1) data integrity, (2) no orphaned records. With my original database, there were no orphaned records, but it is the case, as you point out, that the integrity of the original data is compromised...although nobody has ever complained because donations have been perfectly tracked, and the main point was that people got receipts for all of their donations, regardless of the # under which they were entered.
    With my redesign, I'm still not satisfied that I can be 100% certain of satisfying both concerns. If I can find a way to be sure of this, I'm home free.

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •