Re: Writing Executable VBA Statements
From: Marshall Barton (marshbarton_at_wowway.com)
Date: 02/10/05
- Next message: Anthony Bollinger: "Re: Text of Startup Option Application Title"
- Previous message: Mark via AccessMonster.com: "Re: Overflow Message"
- In reply to: Ted: "Re: Writing Executable VBA Statements"
- Next in thread: Ted: "Re: Writing Executable VBA Statements"
- Reply: Ted: "Re: Writing Executable VBA Statements"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 09 Feb 2005 22:21:57 -0600
Ted wrote:
>right, i'll cool my jets...
>
>and with that, things appear to have gone a bit awry for some reason.
>
>the code below:
>
>SELECT T1.[Patient Number], T1.Cycle, T1.[AE Description], T1.Subtype,
>T1.Onset, T1.Resolved, T1.[Continuing as of], T1.Grade, T1.Attribution,
>T1.Serious, T1.Action, T1.Outcome, T1.DLT, T1.[AER Filed],
>T1.ContinuingEndCycle, T1.Updates
>FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
>ON (T1.[Patient Number]=T2.[Patient Number]) AND (T1.Cycle+1=T2.Cycle)
>WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=01328164 And
>T1.ContinuingEndCycle="Yes";
>
>which i'm pasting as copied from the sql view window returns the desired
>patient number when the continuingcycle value is 'Yes' but it doesn't bump up
>the value of the cycle number by '1'.
Good catch. I missed that.
Just change the T1.Cycle in the field list above to:
T1.Cycle + 1 As Cycle
As you pointed out in another post, the ON clause needs to
include all the PK fields:
ON (T1.[Patient Number]=T2.[Patient Number])
AND (T1.[AE Description] = T2.[AE Description]),
AND (T1.Subtype = T2.Subtype),
AND . . .
AND (T1.Cycle+1=T2.Cycle)
Keep checking to see if we've missed anything else. This
query is the key to the whole operation of duplicating a
record and making sure that it doesn't duplicate it more
than once. So we have to make sure that it does everthing
correctly, including not doing anything if it has already
been done.
Let me know if the above changes are an improvement or what
happens.
-- Marsh MVP [MS Access]
- Next message: Anthony Bollinger: "Re: Text of Startup Option Application Title"
- Previous message: Mark via AccessMonster.com: "Re: Overflow Message"
- In reply to: Ted: "Re: Writing Executable VBA Statements"
- Next in thread: Ted: "Re: Writing Executable VBA Statements"
- Reply: Ted: "Re: Writing Executable VBA Statements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
