Repost: SQL SELECT WHERE Problem

[Robin]

Hello,

I have the following scenerio:

An Engagement is made up of 5 Assignments to be completed sequentially by
different staff members. When the Engagement is first set up, I want the
first Assignment in the sequence to show up in a "New Assignments" listbox
which is based on listbox "lstSMainS". When the staff "checks" the
Assignment complete (CAComplete) it shows up when the next staff's ID is
chosen in the lstSMainS.

I have the following SQL which I thought would give me the the record where
the sequence number (CASequence) was the "next in sequence" after the
completed Assignments when CAStaffID matched lstSMainS. Instead it is giving
me each staff's assignment whether the assignment preceding it has been
complete or not.

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CADescriptionI, ClientAssignment.CADueDate
FROM ClientAssignment
WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND ClientID = """ & [ClientID] & """ AND EngagementID = """ &
[EngagementID] & """ AND EngagementYr = """ & [EngagementYr] & """ AND
AssignmentID = """ & [AssignmentID] & """")) AND
((ClientAssignment.CAStaffID)=[Forms]![zfmMain]![lstSMainS]))
ORDER BY ClientAssignment.CADueDate;

Any thoughts would be appreciated,
Robin

In case my original explanation was a bit fuzzy, this is what I'm trying to
do:

Assignment
CASequence=1, CAStaffID=MEL
CASequence=2, CAStaffID=DTM
CASequence=3, CAStaffID=RLC
and so on...

When MEL opens the form his StaffID is already chosen in lstSMainS and
CASequence1 shows up in the "New Assignments" listbox. When DTM or RLC open
the form (their StaffID's now populate lstSMainS), CASequence 2 and 3 are NOT
in the listbox because CASequence 1 is not complete.

When MEL marks the record Complete (CAComplete = True) then DTM sees
CASequence 2 in the "New Assingments" listbox when he opens the form. RLC
still won't see CASequence 3 in "New Assignments" until DTM marks CASequence
2 complete.

Right now everyone sees their assignment in the "New Assignments" listbox
before the preceding CASequence has been completed.

Does that make sense?

Thanks,
Robin

 

[kc-mass]

Try setting a first query that just selects the data that relates to that
client,
date, et al but with no "dmin".
Use that query as the source for a second query that applies
"Min(CASequence)"

Regards

Kevin

 

[Robin]

kc-mass
Thank you for the response.

I tried your suggestion with:
Query1 as
SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CADescriptionI, ClientAssignment.CADueDate,
ClientAssignment.CASequence, ClientAssignment.CAStaffID,
ClientAssignment.CAAccept
FROM ClientAssignment
WHERE (((ClientAssignment.CAStaffID)=[Forms]![zfmMain]![lstSMainS]) AND
((ClientAssignment.CAAccept)=False))
ORDER BY ClientAssignment.CADueDate;

Query 2 as
SELECT qsNewCAStaff1.ClientID, qsNewCAStaff1.EngagementID,
qsNewCAStaff1.EngagementYr, qsNewCAStaff1.AssignmentID,
qsNewCAStaff1.CADescriptionI, qsNewCAStaff1.CADueDate,
qsNewCAStaff1.CAStaffID, qsNewCAStaff1.CAAccept
FROM qsNewCAStaff1
WHERE
(((qsNewCAStaff1.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND ClientID = """ & [ClientID] & """ AND EngagementID = """ &
[EngagementID] & """ AND EngagementYr = """ & [EngagementYr] & """ AND
AssignmentID = """ & [AssignmentID] & """")))
ORDER BY qsNewCAStaff1.CADueDate;

And also tried Query 2 as
SELECT qsNewCAStaff1.ClientID, qsNewCAStaff1.EngagementID,
qsNewCAStaff1.EngagementYr, qsNewCAStaff1.AssignmentID,
qsNewCAStaff1.CADescriptionI, qsNewCAStaff1.CADueDate,
qsNewCAStaff1.CAStaffID, qsNewCAStaff1.CAAccept
FROM qsNewCAStaff1
WHERE (((qsNewCAStaff1.CASequence)=Min("[CASequence]")))
ORDER BY qsNewCAStaff1.CADueDate;

But I'm still getting the minimum CASequence for EACH Staff rather than for
each Assignment.

If you have any other ideas, I would be most appreciative.

Thank you,
Robin

Try setting a first query that just selects the data that relates to that
client,
date, et al but with no "dmin".
Use that query as the source for a second query that applies
"Min(CASequence)"

Regards

Kevin

Hello,

I have the following scenerio:

An Engagement is made up of 5 Assignments to be completed sequentially by
different staff members. When the Engagement is first set up, I want the
first Assignment in the sequence to show up in a "New Assignments" listbox
which is based on listbox "lstSMainS". When the staff "checks" the
Assignment complete (CAComplete) it shows up when the next staff's ID is
chosen in the lstSMainS.

I have the following SQL which I thought would give me the the record
where
the sequence number (CASequence) was the "next in sequence" after the
completed Assignments when CAStaffID matched lstSMainS. Instead it is
giving
me each staff's assignment whether the assignment preceding it has been
complete or not.

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CADescriptionI, ClientAssignment.CADueDate
FROM ClientAssignment
WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND ClientID = """ & [ClientID] & """ AND EngagementID = """ &
[EngagementID] & """ AND EngagementYr = """ & [EngagementYr] & """ AND
AssignmentID = """ & [AssignmentID] & """")) AND
((ClientAssignment.CAStaffID)=[Forms]![zfmMain]![lstSMainS]))
ORDER BY ClientAssignment.CADueDate;

Any thoughts would be appreciated,
Robin

In case my original explanation was a bit fuzzy, this is what I'm trying
to
do:

Assignment
CASequence=1, CAStaffID=MEL
CASequence=2, CAStaffID=DTM
CASequence=3, CAStaffID=RLC
and so on...

When MEL opens the form his StaffID is already chosen in lstSMainS and
CASequence1 shows up in the "New Assignments" listbox. When DTM or RLC
open
the form (their StaffID's now populate lstSMainS), CASequence 2 and 3 are
NOT
in the listbox because CASequence 1 is not complete.

When MEL marks the record Complete (CAComplete = True) then DTM sees
CASequence 2 in the "New Assingments" listbox when he opens the form. RLC
still won't see CASequence 3 in "New Assignments" until DTM marks
CASequence
2 complete.

Right now everyone sees their assignment in the "New Assignments" listbox
before the preceding CASequence has been completed.

Does that make sense?

Thanks,
Robin

 

[Robin]

NEVER MIND! I fiqured it out. I had the DMin looking down to the Assignment
level (AssignmentID = [AssignmentID]) when in reality it should be looking
for the minimum assignment within the engagement!!! Sometimes I can be so
blind.

Thank you,
Robin

Try setting a first query that just selects the data that relates to that
client,
date, et al but with no "dmin".
Use that query as the source for a second query that applies
"Min(CASequence)"

Regards

Kevin

Hello,

I have the following scenerio:

An Engagement is made up of 5 Assignments to be completed sequentially by
different staff members. When the Engagement is first set up, I want the
first Assignment in the sequence to show up in a "New Assignments" listbox
which is based on listbox "lstSMainS". When the staff "checks" the
Assignment complete (CAComplete) it shows up when the next staff's ID is
chosen in the lstSMainS.

I have the following SQL which I thought would give me the the record
where
the sequence number (CASequence) was the "next in sequence" after the
completed Assignments when CAStaffID matched lstSMainS. Instead it is
giving
me each staff's assignment whether the assignment preceding it has been
complete or not.

SELECT ClientAssignment.ClientID, ClientAssignment.EngagementID,
ClientAssignment.EngagementYr, ClientAssignment.AssignmentID,
ClientAssignment.CADescriptionI, ClientAssignment.CADueDate
FROM ClientAssignment
WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND ClientID = """ & [ClientID] & """ AND EngagementID = """ &
[EngagementID] & """ AND EngagementYr = """ & [EngagementYr] & """ AND
AssignmentID = """ & [AssignmentID] & """")) AND
((ClientAssignment.CAStaffID)=[Forms]![zfmMain]![lstSMainS]))
ORDER BY ClientAssignment.CADueDate;

Any thoughts would be appreciated,
Robin

In case my original explanation was a bit fuzzy, this is what I'm trying
to
do:

Assignment
CASequence=1, CAStaffID=MEL
CASequence=2, CAStaffID=DTM
CASequence=3, CAStaffID=RLC
and so on...

When MEL opens the form his StaffID is already chosen in lstSMainS and
CASequence1 shows up in the "New Assignments" listbox. When DTM or RLC
open
the form (their StaffID's now populate lstSMainS), CASequence 2 and 3 are
NOT
in the listbox because CASequence 1 is not complete.

When MEL marks the record Complete (CAComplete = True) then DTM sees
CASequence 2 in the "New Assingments" listbox when he opens the form. RLC
still won't see CASequence 3 in "New Assignments" until DTM marks
CASequence
2 complete.

Right now everyone sees their assignment in the "New Assignments" listbox
before the preceding CASequence has been completed.

Does that make sense?

Thanks,
Robin