Writing Executable VBA Statements

[Ted]

i'm into this marsh, but i'm getting a "Syntax error (missing operator) in
query expression 'T1.Cycle + 1 As Cycle = T2.Cycle" message from the code as
created thus far

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)
WHERE T2.[Patient Number] Is Null And T1.[Patient Number]=1328164 And
T1.ContinuingEndCycle="Yes";

it won't even let me save the modified version you see above?

-ted

Comments inline below.
--
Marsh
MVP [MS Access]

i'm a little confused by this (below)


what i ultimately want to let the user do is press a 'DUPLICATE RECORDS'
button which actuates something behind the scense. the user can always be
assumed correctly to have this button available to him/her at the bottom of
the sub-form he is using to view some combination of Patient Number and Cycle
number.

when the user clicks the DUPLICATES RECORDS button, its effect is to go
through all the sub-form records having a 'Yes' for 'Continuing at end of
cycle' and duplicate them into the next cycle (which he will have been
previously advised to guarantee already exists in the mainform).

regarding your remarks above, i believe that if a record is marked as
'Continuing...' the point is that it must appear in the subsequent cycle and
that user is too lazy to want to have to bother herself with manually
entering the same information into the sub-form for that record. so i don't
think it's an issue, or to put it another way, i think you've hit on a
non-issue.
Good.


on the flip-side of this, there may be a problem arising from the primary
keys which are comprised of all of the following in this order: Patient
Number, Cycle, AE Description, Subtype, Onset......

That will just make the query a little messier

if each time the button is clicked, the effect is to unfailingly try to
duplicate every record that meets the Where criteria, the don't you run afoul
of the constraint imposed by the pk. if i'm right, then possibly we need to
have a field in the sub-form table called 'Duplicated' which by default is
'No' and which the sql query toggles to 'Yes' and which is added into the
where clause so as to overlook any records not having 'Duplicated' = 'No' and
thefore avoid duplicating previously duplicated records.

No need for that. The query will only duplicate the ones
that have not been duplicated before. That's what Joining
the table to itself and the Is Null criteria are for. We'll
just have to expand the ON clause to include all the PK
fields.

 

[Ted]

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

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.

 

[Marshall Barton]

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.

I see I have to be very explicit here. You made a change in
the wrong place. Here's what I think you should have now:

SELECT T1.[Patient Number], T1. Cycle + 1 As 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.[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"

 

[Marshall Barton]

i'm into this marsh, but i'm getting a "Syntax error (missing operator) in
query expression 'T1.Cycle + 1 As Cycle = T2.Cycle" message from the code as
created thus far

Enough of this subthread. See my last reply at the end ot
the thread.

 

[Ted]

between the point i submitted my last response/query and now, i played around
and around .... with this and got something like the one below which i put
into the sql query window

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

which looks like it works. when i pasted yours into a new query sql window i
got a 'join expression not supported' message.

-ted

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.

I see I have to be very explicit here. You made a change in
the wrong place. Here's what I think you should have now:

SELECT T1.[Patient Number], T1. Cycle + 1 As 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.[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"

 

[Marshall Barton]

between the point i submitted my last response/query and now, i played around
and around .... with this and got something like the one below which i put
into the sql query window

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

which looks like it works. when i pasted yours into a new query sql window i
got a 'join expression not supported' message.

You're getting good at spotting my paste errors ;-)
I left some extraneous commas in the ON expression, but your
version looks like it's the equivalent of what I intended.

Now, you say it works, but how extensively have you tested
it? Did you try to get it to generate a record that was
already duplicated? Did you run it in a situation where it
should generate multiple records? What about the situation
where there are a lot of data for a patient some needing
duplicates, some not, and some already duplicated? I'm
especially conerned about the scenario of duplicating all
the appropriate records in your subform.

Test, more tests and test again. If every possible
situation is covered, then we can move on to executing the
query from a command button.

Question, the Patient Number for bulk duplicating the
records in the subform is in a text box on the main form,
right?

 

[Ted]

testing testing testing 1,2,three.....

in no particular order, marsh, here are some answers to your questions:

the bulk duplicating button could be on the main form, i guess. it doesn't
exist as yet. so if it'll make the coding any simpler, let's put it on the
main one.

i'm not at all sure i understand the question about the extent of my testing
and the breadth of the future testing in the context of what this select
query does or did. i thought we were going to first create a working select
query which would then morph into an append query.

at this point, i had two patients' worth of data. one patient had cycle 1
and cycle 2 data as did the other patient. and it appeared from repeated use
of the sql that it was picking up the ones with the 'Yes' in the 'Continuing
at end of cycle' control and incrementing the value of the 'Cycle' field by
adding 1 to its existing value.

haven't we forgotten something, i.e. the part about appending the subset
generated by sql to the 'parent' sub-form table?

-ted

between the point i submitted my last response/query and now, i played around
and around .... with this and got something like the one below which i put
into the sql query window

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

which looks like it works. when i pasted yours into a new query sql window i
got a 'join expression not supported' message.

You're getting good at spotting my paste errors ;-)
I left some extraneous commas in the ON expression, but your
version looks like it's the equivalent of what I intended.

Now, you say it works, but how extensively have you tested
it? Did you try to get it to generate a record that was
already duplicated? Did you run it in a situation where it
should generate multiple records? What about the situation
where there are a lot of data for a patient some needing
duplicates, some not, and some already duplicated? I'm
especially conerned about the scenario of duplicating all
the appropriate records in your subform.

Test, more tests and test again. If every possible
situation is covered, then we can move on to executing the
query from a command button.

Question, the Patient Number for bulk duplicating the
records in the subform is in a text box on the main form,
right?

 

[Marshall Barton]

testing testing testing 1,2,three.....

in no particular order, marsh, here are some answers to your questions:

the bulk duplicating button could be on the main form, i guess. it doesn't
exist as yet. so if it'll make the coding any simpler, let's put it on the
main one.

i'm not at all sure i understand the question about the extent of my testing
and the breadth of the future testing in the context of what this select
query does or did. i thought we were going to first create a working select
query which would then morph into an append query.

at this point, i had two patients' worth of data. one patient had cycle 1
and cycle 2 data as did the other patient. and it appeared from repeated use
of the sql that it was picking up the ones with the 'Yes' in the 'Continuing
at end of cycle' control and incrementing the value of the 'Cycle' field by
adding 1 to its existing value.

haven't we forgotten something, i.e. the part about appending the subset
generated by sql to the 'parent' sub-form table?

More testing.
Create some data so that one patient already has the
duplicated records. Then test to make sure the query
returns no records.

Set up another patient that need several continuing records
and check to see if the query returns all the ones it
supposed to. Then include some more records that are not
supposed to be duplicated and test the query to make sure
the query only duplicates the ones it's supposed to.

More questions.
Before morphing this into an append query, I still need to
double check that the Patient Number is displayed in a text
box on the main form or where it is. If it is on the main
form, then you can run a few more tests after replacing the
specific patient number you've been entering by hand
(01328164 in your previous post) with a reference to the
text box:
Forms!mainformname.patientnumbertextboxname
Now, the form will have to be opened to the appropriate
patient before running the query. More testing . . .

When all that is working correctly, then you can use the
Query menu (in query design view) to change it to an Append
query. At this point, check to see if all the appropriate
fields are identified in both the:
INSERT INTO table (field list)
and in the:
SELECT field list
Pay particular attention to make sure all the primary key
fields are included and have the proper values. You should
make a copy of the table before performing still more
tests to make it easy to undo whatever happens.

As far as the button is concerned, don't sweat it. It
doesn't matter if it's on the main form or in the subform,
besides, making the button run the query is the trivial part
of this exercise ;-)

 

[Ted]

marsh,

testing is the word for the day. got it!

regarding the wherabouts of the 'Patient Number' id field it is selected by
user from the main menu via a combo-box l/u table. it appears as a linked
child field on the sub-form in a text box (and not a combo box).

will test some more and get back....

later and thx!!

-ted

 

[Ted]

marsh,

i just thought of something, unless i'm mistaked we always assume the next
(n+1) value of "Continuing at end of cycle" is "Yes". so that if the button
were pressed twice succession the same record would now appear in cycle+2.
would we not want to generate a 'No' value for the "Continuing...." field
when the query's activated. by my reckoning this would mean the user would
have to manually 'override' it in order for a cycle+1 duplicate to show up in
'cycle+2'.

hope this makes sense,

-ted

 

[Marshall Barton]

Another good catch. See, I told you all this testing is a
good thing ;-)

Another easy one though, just change the Select field for
continuing from:
T1.[Continuing as of]
to:
"No" As [Continuing as of]

Everthing else looking good, I hope??

 

[Ted]

hi marsh,

catch of the day dept here

well here's the latest version of this looming program:

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" As ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Cycle+1=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)
WHERE ((([Forms]![Treatment and Toxicity]![Adverse Events
(child)].Form![Patient Number]) And ((T1.ContinuingEndCycle)="Yes") And
((T2.[Patient Number]) Is Null)));

where your "No" As ContinuingEndCycle idea works quite well and sets the
value to "No" from "Yes"

as you can see in the WHERE clause, i'm trying to reference the subform

i'm trying to configure it so that when it launches it knows to use the
value of the "Patient Number" textbox on the sub-form so with the form and
it's nested/correlated subform open, i launch the query from the query
objects window and what i see is that it seems to work for all the (2)
patients data i got in the sub-table??? can't quite see where this has gone
awry.

best,

-ted

Another good catch. See, I told you all this testing is a
good thing ;-)

Another easy one though, just change the Select field for
continuing from:
T1.[Continuing as of]
to:
"No" As [Continuing as of]

Everthing else looking good, I hope??
--
Marsh
MVP [MS Access]

i just thought of something, unless i'm mistaked we always assume the next
(n+1) value of "Continuing at end of cycle" is "Yes". so that if the button
were pressed twice succession the same record would now appear in cycle+2.
would we not want to generate a 'No' value for the "Continuing...." field
when the query's activated. by my reckoning this would mean the user would
have to manually 'override' it in order for a cycle+1 duplicate to show up in
'cycle+2'.

 

[Ted]

sort of as a postscript to my previous....

in taking out my finest fine tooth comb and sifting through the
characteristics of the 'parent' table and the 'duplicated' one, i noticed
that i started out with 14 records (spread btwn 2 patients and two cycles, 1
and 2) in the parent. of the 14, only 10 had 'Yes' in the 'Continuing at the
end of cycle' control and so only 10 were candidates for duplication. in
looking at the printed version of the 'selected' table of duplicates, i
counted only 9!!!! however, i noticed that the parent had a pair of records
for pateint 01097977, one for cycle 1 and one for cycle 2 which where
duplicates of one another (probably from my earlier following through on a
suggestion of yours) including the 'Yes' field. on the other hand, and this
seems like a good thing, the cycle number of the duplicated version of this
pair of twins was 3 !! -- which i guess means that it picks the value of the
highest cycle number when there's more than one of them. in the 'real' world
of the user, she probably might've mistakenly overlooked toggling the
'Continuing....' field from 'Yes' to no when the first record (from cycle 1)
got duplicated into cycle 2, so now we know what would've happened to her
table under that condition, the higher cycled numbered record would've gotten
duplicated. interesting to me. perhaps not news to you?

-ted

Another good catch. See, I told you all this testing is a
good thing ;-)

Another easy one though, just change the Select field for
continuing from:
T1.[Continuing as of]
to:
"No" As [Continuing as of]

Everthing else looking good, I hope??
--
Marsh
MVP [MS Access]

i just thought of something, unless i'm mistaked we always assume the next
(n+1) value of "Continuing at end of cycle" is "Yes". so that if the button
were pressed twice succession the same record would now appear in cycle+2.
would we not want to generate a 'No' value for the "Continuing...." field
when the query's activated. by my reckoning this would mean the user would
have to manually 'override' it in order for a cycle+1 duplicate to show up in
'cycle+2'.

 

[Marshall Barton]

hi marsh,

catch of the day dept here

well here's the latest version of this looming program:

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" As ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Cycle+1=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)
WHERE ((([Forms]![Treatment and Toxicity]![Adverse Events
(child)].Form![Patient Number]) And ((T1.ContinuingEndCycle)="Yes") And
((T2.[Patient Number]) Is Null)));

where your "No" As ContinuingEndCycle idea works quite well and sets the
value to "No" from "Yes"

as you can see in the WHERE clause, i'm trying to reference the subform

i'm trying to configure it so that when it launches it knows to use the
value of the "Patient Number" textbox on the sub-form so with the form and
it's nested/correlated subform open, i launch the query from the query
objects window and what i see is that it seems to work for all the (2)
patients data i got in the sub-table??? can't quite see where this has gone
awry.

You left out the field you want to match:

WHERE (((T1.[Patient Number] = Forms![Treatment and
Toxicity]![Adverse Events (child)].Form![Patient Number])
AND . . .

But, I think it would be a little better to use the Main
form form this (should be the same patient)

WHERE (((T1.[Patient Number] = Forms![Treatment and
Toxicity].[Patient Number]) AND . . .

 

[Marshall Barton]

sort of as a postscript to my previous....

in taking out my finest fine tooth comb and sifting through the
characteristics of the 'parent' table and the 'duplicated' one, i noticed
that i started out with 14 records (spread btwn 2 patients and two cycles, 1
and 2) in the parent. of the 14, only 10 had 'Yes' in the 'Continuing at the
end of cycle' control and so only 10 were candidates for duplication. in
looking at the printed version of the 'selected' table of duplicates, i
counted only 9!!!! however, i noticed that the parent had a pair of records
for pateint 01097977, one for cycle 1 and one for cycle 2 which where
duplicates of one another (probably from my earlier following through on a
suggestion of yours) including the 'Yes' field. on the other hand, and this
seems like a good thing, the cycle number of the duplicated version of this
pair of twins was 3 !! -- which i guess means that it picks the value of the
highest cycle number when there's more than one of them. in the 'real' world
of the user, she probably might've mistakenly overlooked toggling the
'Continuing....' field from 'Yes' to no when the first record (from cycle 1)
got duplicated into cycle 2, so now we know what would've happened to her
table under that condition, the higher cycled numbered record would've gotten
duplicated. interesting to me. perhaps not news to you?

No. It does not duplicate only the highest numbered cycle.
It duplicates all records that have ContinuingEndCycle set
to Yes and have not already been duplicated. I don't think
you can ever get a situation where that won't be the highest
numbered cycle, but highest is not part of our criteria (nor
should it be).

You probably should clean up your test data so you know
what's in there and what to expect from each test.

Make sure you have a test patient with nothing to duplicate
as well as another patient that needs several duplicates.

 

[Ted]

marsh,

just thought i'd let you know that i tried your idea wrt converting all of
the following

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" AS ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

into an append query. the result is a message that said:

"Microsoft Access can't represent the joint expression T1.Cycle+1=T2.Cycle
in Design View

* One or more fields may have been deleted or renamed
* The name of one or more fields or tables specified in the join expression
may be misspelled
* The join may use an operator that isn't supported in Design view such as >
or <. "

the code however seemed to work as a Select query.

-ted

 

[Marshall Barton]

just thought i'd let you know that i tried your idea wrt converting all of
the following

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" AS ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

into an append query. the result is a message that said:

"Microsoft Access can't represent the joint expression T1.Cycle+1=T2.Cycle
in Design View

* One or more fields may have been deleted or renamed

Did you switch to design view? As the message says, it
can't handle that kind of Join. Just continue to work in
SQL view.

I think all you need to do is add:

INSERT INO [Adverse Events (child)]

before the SELECT.

 

[Ted]

Hi marsh,

This seems to have turned off the alarm bells and it works now albeit w/
some warnings that an append query is about to append X rows giving the user
a last chance to abort the mission.

I’m going to try to write this up.

Whenever a ‘Yes’ appears in the ‘Continuing at end of cycle’ control the
adverse event will be a candidate for duplication. If it has not been
duplicated in the sub-form for the proximal (Cycle+1) field then it will show
up there; if it was previously duplicated, then it will be bypassed. This is
true always but only for the patient the user is reviewing from the open
main/sub-form. Other sub-records which may have their “Continuing at end of
cycle†control set to “Yes†will be ignored by this process unless/until the
user is viewing the patient’s record number (irrespective of which cycle
number)

When it does show up in the next cycle, the value of the “Continuing at end
of cycle†control will be toggled from “Yes†to “No†(so that if that if it’s
going to get duplicated into the “Cycle+2†sub-form, the user must override
it.

In a word, records having “Yes†in this “Con…..†field for a particular will
always get duplicated into the next cycle provided the user is ‘sitting’ on
any main-form having that patient’s id number and not until this condition is
met AND it has not been previously duplicated.

Perhaps there ‘s something I left out.


I haven’t gotten to creating the button as yet….I am thinking about
actuating this query from VBA. Before reaching the Open query command in it I
will try to create a message which gets a response from the user, namely that
if the user cannot assure us that there exists a record in the database
having a cycle number 1 more than the current one for this patient that the
application will abort the process.

In the earlier duplication button’s ‘ON Click’ event, I used this code to
get this going


Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and Cycle) were already entered.
If not, press 'Cancel', otherwise press 'OK'.", vbOKCancel + vbCritical +
vbDefaultButton2, "Critical !")
If Response = 1 Then…..


So I reckon the penultimate step is to create some On click event in vba
which uses it and launches the query (which I have named ‘Append Duplicate
Adverse Events’.

I was going to save this for another posting, but I’d really like to know a
bit more about how this great SQL code we cobbled together worked;

INSERT INTO [Adverse Events (child)]
SELECT T1.[Patient Number] AS [Patient Number], T1.Cycle+1 AS Cycle, T1.[AE
Description] AS [AE Description], T1.Subtype AS Subtype, T1.Onset AS Onset,
T1.Resolved AS Resolved, T1.[Continuing as of] AS [Continuing as of],
T1.Grade AS Grade, T1.Attribution AS Attribution, T1.Serious AS Serious,
T1.Action AS [Action], T1.Outcome AS Outcome, T1.DLT AS DLT, T1.[AER Filed]
AS [AER Filed], "No" AS ContinuingEndCycle, T1.Updates AS Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Cycle+1=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)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

Question: how does the ON clause work above, specifically regarding the
Cycle values. Why is it “T1.Cycle+1 = T2.Cycleâ€. In the WHERE clause, how
does the “And ((T2.[Patient Number]) Is Null)));†part work? I can understand
the first part of the WHERE clause which is straightforward enough, but this
part throws me.

Lastly, in my earlier approach to duplicating (one AE record at a time), my
code proceeded to enter the following into the next cycle’s “Update†field. I
think it’s kind of self-evident what it does (below is code), but how would
that get incorporated into this SQL query?

"On " & Now() & " , " & LAS_GetUserName() & " duplicated this patient's
Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] & " record."

-ted

just thought i'd let you know that i tried your idea wrt converting all of
the following

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" AS ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

into an append query. the result is a message that said:

"Microsoft Access can't represent the joint expression T1.Cycle+1=T2.Cycle
in Design View

* One or more fields may have been deleted or renamed

Did you switch to design view? As the message says, it
can't handle that kind of Join. Just continue to work in
SQL view.

I think all you need to do is add:

INSERT INO [Adverse Events (child)]

before the SELECT.

 

[Marshall Barton]

Comments in line below.
--
Marsh
MVP [MS Access]

This seems to have turned off the alarm bells and it works now albeit w/
some warnings that an append query is about to append X rows giving the user
a last chance to abort the mission.

I’m going to try to write this up.

Whenever a ‘Yes’ appears in the ‘Continuing at end of cycle’ control the
adverse event will be a candidate for duplication. If it has not been
duplicated in the sub-form for the proximal (Cycle+1) field then it will show
up there; if it was previously duplicated, then it will be bypassed. This is
true always but only for the patient the user is reviewing from the open
main/sub-form. Other sub-records which may have their “Continuing at end of
cycle” control set to “Yes” will be ignored by this process unless/until the
user is viewing the patient’s record number (irrespective of which cycle
number)

When it does show up in the next cycle, the value of the “Continuing at end
of cycle” control will be toggled from “Yes” to “No” (so that if that if it’s
going to get duplicated into the “Cycle+2” sub-form, the user must override
it.

In a word, records having “Yes” in this “Con…..” field for a particular will
always get duplicated into the next cycle provided the user is ‘sitting’ on
any main-form having that patient’s id number and not until this condition is
met AND it has not been previously duplicated.

Perhaps there ‘s something I left out.

Right, that sounds like what we did. I would run the
writeup by an actual user to see if they can relate the
language us programmers use.

I haven’t gotten to creating the button as yet….I am thinking about
actuating this query from VBA. Before reaching the Open query command in it I
will try to create a message which gets a response from the user, namely that
if the user cannot assure us that there exists a record in the database
having a cycle number 1 more than the current one for this patient that the
application will abort the process.

In the earlier duplication button’s ‘ON Click’ event, I used this code to
get this going


Response = MsgBox("Before proceding to duplicate this record, verify that
the top part of this form " & Chr(13) & _
"for this patient's next cycle (i.e., MR and Cycle) were already entered.
If not, press 'Cancel', otherwise press 'OK'.", vbOKCancel + vbCritical +
vbDefaultButton2, "Critical !")
If Response = 1 Then…..

So I reckon the penultimate step is to create some On click event in vba
which uses it and launches the query (which I have named ‘Append Duplicate
Adverse Events’.

The code for the button's Click event procedure would be
like this:

Dim db As Database
Response = MsgBox("Before . . .
If Response = 1 Then
Set db = CurrentDb()
db.Execute "Append Duplicate Adverse Events"
MsgBox db.RecordsAffected & " Record" _
& IIf(db.RecordsAffected <> 1, "s", "") _
& " have been continued to the next cycle.", _
vbOkOnly + vbInformation, "Duplication"
End If

If there are any terms/methods you are not familiar with, be
sure to check them out in Help so you know what it's all
about.

I was going to save this for another posting, but I’d really like to know a
bit more about how this great SQL code we cobbled together worked;

INSERT INTO [Adverse Events (child)]
SELECT T1.[Patient Number] AS [Patient Number], T1.Cycle+1 AS Cycle, T1.[AE
Description] AS [AE Description], T1.Subtype AS Subtype, T1.Onset AS Onset,
T1.Resolved AS Resolved, T1.[Continuing as of] AS [Continuing as of],
T1.Grade AS Grade, T1.Attribution AS Attribution, T1.Serious AS Serious,
T1.Action AS [Action], T1.Outcome AS Outcome, T1.DLT AS DLT, T1.[AER Filed]
AS [AER Filed], "No" AS ContinuingEndCycle, T1.Updates AS Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Cycle+1=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)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

Question: how does the ON clause work above, specifically regarding the
Cycle values. Why is it “T1.Cycle+1 = T2.Cycle”. In the WHERE clause, how
does the “And ((T2.[Patient Number]) Is Null)));” part work? I can understand
the first part of the WHERE clause which is straightforward enough, but this
part throws me.

The LEFT JOIN clause connects the table to itself and the ON
phrase specifies that the records be connected to their next
cycle. The T2.[Patient Number] Is Null part in the WHERE
clause selects only the records that don't have a next
cycle. )This kind of query is sometimes called a frustrated
outer join query.)

Lastly, in my earlier approach to duplicating (one AE record at a time), my
code proceeded to enter the following into the next cycle’s “Update” field. I
think it’s kind of self-evident what it does (below is code), but how would
that get incorporated into this SQL query?

"On " & Now() & " , " & LAS_GetUserName() & " duplicated this patient's
Cycle #" & Forms![Treatment and Toxicity].[Current Cycle Number] & " record."

I don't understand. Is this another field in the
[Adverse Events (child)] table?

 

[Ted]

hi marsh,

the story continues to continue as i think of some facets of this process
that i dhtink might've been glossed over in the quest to create the magic
button...

INSERT INTO [Adverse Events (child)]
SELECT T1.[Patient Number] AS [Patient Number], T1.Cycle+1 AS Cycle, T1.[AE
Description] AS [AE Description], T1.Subtype AS Subtype, T1.Onset AS Onset,
T1.Resolved AS Resolved, T1.[Continuing as of] AS [Continuing as of],
T1.Grade AS Grade, T1.Attribution AS Attribution, T1.Serious AS Serious,
T1.Action AS [Action], T1.Outcome AS Outcome, T1.DLT AS DLT, T1.[AER Filed]
AS [AER Filed], "No" AS ContinuingEndCycle, T1.Updates AS Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));


here's the code (above) as it stands in the query. so, i guess my question
has to do with the fact that there's the possibility the user might go to one
of the duplicate aes (or possibly even the cycle where the first one that
gets subsequently duplicated) and modify a value of one of the fields that
are being cloned in the next cycle. e.g. lets say that for a particular
patient id, an ae occurs for the first time in cycle 2 and that it's later
duplicated into cycles 3 and then maybe even 4 whereby it comes to a halt and
continues no further. then let's say that the user realizes something was not
coded quite correctly in cycle 2 and modifies its value. user hits the
duplicate button....question: does the ae get duplicated to cycle 3. if not,
then is it because cycle 3 and 4 records for this ae exist? does the sql ON
clause need to be expanded to include every parameter on the record?

best,

-ted

just thought i'd let you know that i tried your idea wrt converting all of
the following

SELECT T1.[Patient Number], T1.Cycle+1 AS 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],
"No" AS ContinuingEndCycle, T1.Updates
FROM [Adverse Events (child)] AS T1 LEFT JOIN [Adverse Events (child)] AS T2
ON (T1.Onset=T2.Onset) AND (T1.Subtype=T2.Subtype) AND (T1.[AE
Description]=T2.[AE Description]) AND (T1.[Patient Number]=T2.[Patient
Number]) AND (T1.Cycle+1=T2.Cycle)
WHERE (((T1.[Patient Number]=[Forms]![Treatment and Toxicity].[Patient
Number]) And ((T1.ContinuingEndCycle)="Yes") And ((T2.[Patient Number]) Is
Null)));

into an append query. the result is a message that said:

"Microsoft Access can't represent the joint expression T1.Cycle+1=T2.Cycle
in Design View

* One or more fields may have been deleted or renamed

Did you switch to design view? As the message says, it
can't handle that kind of Join. Just continue to work in
SQL view.

I think all you need to do is add:

INSERT INO [Adverse Events (child)]

before the SELECT.