Re: Writing Executable VBA Statements

From: Ted (Ted_at_discussions.microsoft.com)
Date: 02/10/05 

Date: Thu, 10 Feb 2005 07:13:06 -0800

i'm not sure if the system 'took' my last reply to your posting below marsh,
so i'll reprise it (and i found that in the code i posted as written i
overlooked adding the final AND from yours)

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.Cycle + 1 As Cycle = T2.Cycle), AND (T1.[Patient Number]=T2.[Patient
Number]),
AND (T1.[AE Description] = T2.[AE Description]),
AND (T1.Subtype = T2.Subtype),
AND (T1.Onset = T2.Onset)
AND (T1.Cycle + 1 = T2.Cycle)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=1328164 And
T1.ContinuingEndCycle="Yes";

is the way the SQL query stands at present. when i try to run ("!") it, the
following message "Syntax error (missing operator) in query expression
'T1.Cycle + 1 As Cycle = T2.Cycle" is returned.

i see that in my code above the same expression appears more than once,
following, as i'm trying to your posting's instructions. i've tried massaging
it on the assumption i misunderstood the syntax and still no cigar.

-ted

"Marshall Barton" wrote:

> 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]
>

Relevant Pages

  • Re: Writing Executable VBA Statements
    ... regarding the wherabouts of the 'Patient Number' id field it is selected by ... >>the bulk duplicating button could be on the main form, ... >>query which would then morph into an append query. ...
    (microsoft.public.access.formscoding)
  • Re: Microsoft Cleanliness
    ... preceeds each table with the word TABLE and each query with "QUERY". ... Make sure the import window can't be resized. ... We have a beautiful marsh, Cootes Paradise, here at the west end of Lake ... Ontario but it's been dying for decades because of the invasion of carp, ...
    (comp.databases.ms-access)
  • Re: Microsoft Cleanliness
    ... preceeds each table with the word TABLE and each query with "QUERY". ... We have a beautiful marsh, Cootes Paradise, here at the west end of Lake Ontario but it's been dying for decades because of the invasion of carp, which muddy the water, and uproot the vegetation. ...
    (comp.databases.ms-access)
  • Re: textbox controsource as recordset field
    ... Marsh, ... Thanks for the suggestion. ... The field being returned in this query is a text field. ... >>> the case when it is zero? ...
    (microsoft.public.access.reports)
  • Re: Aggregate Sum
    ... Hi Marsh ... This is the SQL for the query I am using. ... The result I should be getting for Comm is $3000 ...
    (microsoft.public.access.formscoding)