Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same person
every time who visits the dealer. I assume I would just change the groupby
clause to reflect these columns instead?
The dealer can have several audits though or different activities, I only
need to check the history if the LAST activity was a VV. If there wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at zero,
but
if there was an audit I want to update the AuditNo to 99.
Thanks...
:
I may be missing something, but
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
will give you a list of the last audit conducted by each specialist.
You could then check for any activity after that date:
SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check
to
see
if an audit exists. I thought it would have to be done in code.
What details do you need?
Table is Itinerary
Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).
Fields are:
ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)
Thanks, Sue
:
You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?
Kind Regards
Sue
:
Is my interpretation of Exists correct: it should be True if there
are
any
Audit records in the Itinerary table for that Country and Dealer?
If
so,
it
implies to me that you want:
Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Doug
Thanks for the quick reply! I've used this code before elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)
I did it this way using someone else's example a long time ago. I
am
only
a
novice so welcome any better suggestions
The bit after that code is:
If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If
Thanks in advance for any help.
Sue
:
You don't actually explain what's going wrong...
No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why
you
bother
assigning the comparison of DCount to 0 to a variable. Why not
just
put
the
comparison in the If statement?)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi all
I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When
an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set
it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it
is
going
wrong... can anyone help please?
I've added the code below and the data returned from adding a
watch
to
Jetwhere.
(Activity and DealerCode are text fields, CountryCode is
Numeric)
Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] =
"
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then
"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode]
=
"1377""
Thanks in advance for any help.
Sue