Distinct not working in 2007 as in 2003

[Eldon]

I am using a 2003-structure db file, necessary due to the need to keep
user-level security on the db, and a query that uses distinct that worked
fine when under 2003, is now no longer returning distinct records under
Access2007.
Here is the query:

SELECT DISTINCT GACLOSE.[Oversight Person2] AS SStaff, GACLOSE.[Oversight2
Email Address] AS SAddr, GACLOSE.DateDue
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date()) AND ((GACLOSE.[Displayed completed date])
Is Null) AND ((GACLOSE.OverdueEmailSent)=No))
ORDER BY GACLOSE.[Oversight Person2];

This query is used to produce a report, and the report now has Oversight
Person2 repeated many times, where it did not in Access2003. It is also used
to feed a Module that sends out emails, and instead of producing the five
emails to five individuals, it want to send 20+, over and over to the same
people, with the same report attached.
Any suggestions on how/why 2007 acts differently from 2003 in this case,
and what the fix is, would be appreciated.

 

[vanderghast]

I don't have Access 2007 installed but if you want duplicated triplet
(SStaff, Saddr, DateDue) and if DISTINCT does not do that job, then you
can try with GROUP BY:


SELECT GACLOSE.[Oversight Person2] AS SStaff,
GACLOSE.[Oversight2 Email Address] AS SAddr,
GACLOSE.DateDue
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date())
AND ((GACLOSE.[Displayed completed date]) Is Null)
AND ((GACLOSE.OverdueEmailSent)=No))
GROUP BY GACLOSE.[Oversight Person2],
GACLOSE.[Oversight2 Email Address],
GACLOSE.DateDue


On the other hand, if you want just one SStaff, and pick ONE of its Saddr
and ONE of its DateDue, try, instead:


SELECT GACLOSE.[Oversight Person2] AS SStaff,
LAST(GACLOSE.[Oversight2 Email Address]) AS SAddr,
LAST(GACLOSE.DateDue)
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date())
AND ((GACLOSE.[Displayed completed date]) Is Null)
AND ((GACLOSE.OverdueEmailSent)=No))
GROUP BY GACLOSE.[Oversight Person2]




Vanderghast, Access MVP

 

[Jerry Whittle]

Strange. Unless the DateDue field has a time component, it should work find.

Try this instead:

SELECT G.[Oversight Person2] AS SStaff,
G.[Oversight2 Email Address] AS SAddr,
G.DateDue
FROM GACLOSE As G
GROUP BY G.[Oversight Person2],
G.[Oversight2 Email Address],
G.DateDue
HAVING G.DateDue <Date()
AND G.[Displayed completed date] Is Null
AND G.OverdueEmailSent) = No
ORDER BY G.[Oversight Person2];

 

[John Spencer]

Your query should be returning multiple records if you have multiple DateDue
values and the other criteria are met.

So the first thing I would check is to make sure DateDue is not different on
the multiple records.

Is your copy of Access 2007 fully patched? That is do you have the latest
service pack installed?

A work around would be to use an aggregate (totals) query. The one below
might work for you.

SELECT GACLOSE.[Oversight Person2] AS SStaff
, GACLOSE.[Oversight2 Email Address] AS SAddr
, Max(GACLOSE.DateDue) as LatestDate
FROM GACLOSE
WHERE GACLOSE.DateDue<Date()
AND GACLOSE.[Displayed completed date] Is Null
AND GACLOSE.OverdueEmailSent=False
GROUP BY GACLOSE.[Oversight Person2]
,GACLOSE.[Oversight2 Email Address]
ORDER BY GACLOSE.[Oversight Person2];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Eldon]

Since we no longer have the 'about' tab to check in the Help, how do I know
what version of Office/Access I am on, or for that matter, what the 'current'
version is? At this installation, the network guys control all that anyway,
but it would be nice to be able to check.
Thanks for the help so far, still checking the results. Currently, the
immediate problem of reducing the list to one per person is working, but
inside the module, where the same query is used, the email addresses are
being provided to the module in a different order, from the same query.
Working on that now.
--
Eldon

Your query should be returning multiple records if you have multiple DateDue
values and the other criteria are met.

So the first thing I would check is to make sure DateDue is not different on
the multiple records.

Is your copy of Access 2007 fully patched? That is do you have the latest
service pack installed?

A work around would be to use an aggregate (totals) query. The one below
might work for you.

SELECT GACLOSE.[Oversight Person2] AS SStaff
, GACLOSE.[Oversight2 Email Address] AS SAddr
, Max(GACLOSE.DateDue) as LatestDate
FROM GACLOSE
WHERE GACLOSE.DateDue<Date()
AND GACLOSE.[Displayed completed date] Is Null
AND GACLOSE.OverdueEmailSent=False
GROUP BY GACLOSE.[Oversight Person2]
,GACLOSE.[Oversight2 Email Address]
ORDER BY GACLOSE.[Oversight Person2];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am using a 2003-structure db file, necessary due to the need to keep
user-level security on the db, and a query that uses distinct that worked
fine when under 2003, is now no longer returning distinct records under
Access2007.
Here is the query:

SELECT DISTINCT GACLOSE.[Oversight Person2] AS SStaff, GACLOSE.[Oversight2
Email Address] AS SAddr, GACLOSE.DateDue
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date()) AND ((GACLOSE.[Displayed completed date])
Is Null) AND ((GACLOSE.OverdueEmailSent)=No))
ORDER BY GACLOSE.[Oversight Person2];

This query is used to produce a report, and the report now has Oversight
Person2 repeated many times, where it did not in Access2003. It is also used
to feed a Module that sends out emails, and instead of producing the five
emails to five individuals, it want to send 20+, over and over to the same
people, with the same report attached.
Any suggestions on how/why 2007 acts differently from 2003 in this case,
and what the fix is, would be appreciated.

 

[John Spencer]

It's there - just buried (as a lot of things seem to be in 2007).
== Click the Office button
== Click the Access Options button
== Click Resources
== Look at the About Microsoft Office Access 2007 - under it should be the
version information. At a minimum you should have SP 1 installed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Eldon]

Found my answer. Internally, a report was set to a different order. Don't
remember that, but have the fix. Thanks vanderghast, Jerry Whittle, and John
Spencer. for your help.
Still curious about how to get Access' version though.
--
Eldon

Since we no longer have the 'about' tab to check in the Help, how do I know
what version of Office/Access I am on, or for that matter, what the 'current'
version is? At this installation, the network guys control all that anyway,
but it would be nice to be able to check.
Thanks for the help so far, still checking the results. Currently, the
immediate problem of reducing the list to one per person is working, but
inside the module, where the same query is used, the email addresses are
being provided to the module in a different order, from the same query.
Working on that now.
--
Eldon

Your query should be returning multiple records if you have multiple DateDue
values and the other criteria are met.

So the first thing I would check is to make sure DateDue is not different on
the multiple records.

Is your copy of Access 2007 fully patched? That is do you have the latest
service pack installed?

A work around would be to use an aggregate (totals) query. The one below
might work for you.

SELECT GACLOSE.[Oversight Person2] AS SStaff
, GACLOSE.[Oversight2 Email Address] AS SAddr
, Max(GACLOSE.DateDue) as LatestDate
FROM GACLOSE
WHERE GACLOSE.DateDue<Date()
AND GACLOSE.[Displayed completed date] Is Null
AND GACLOSE.OverdueEmailSent=False
GROUP BY GACLOSE.[Oversight Person2]
,GACLOSE.[Oversight2 Email Address]
ORDER BY GACLOSE.[Oversight Person2];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am using a 2003-structure db file, necessary due to the need to keep
user-level security on the db, and a query that uses distinct that worked
fine when under 2003, is now no longer returning distinct records under
Access2007.
Here is the query:

SELECT DISTINCT GACLOSE.[Oversight Person2] AS SStaff, GACLOSE.[Oversight2
Email Address] AS SAddr, GACLOSE.DateDue
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date()) AND ((GACLOSE.[Displayed completed date])
Is Null) AND ((GACLOSE.OverdueEmailSent)=No))
ORDER BY GACLOSE.[Oversight Person2];

This query is used to produce a report, and the report now has Oversight
Person2 repeated many times, where it did not in Access2003. It is also used
to feed a Module that sends out emails, and instead of producing the five
emails to five individuals, it want to send 20+, over and over to the same
people, with the same report attached.
Any suggestions on how/why 2007 acts differently from 2003 in this case,
and what the fix is, would be appreciated.

 

[Eldon]

Thanks, got it. I just hadn't dug deep enough. We are on SP1. Any
suggestions on how to find out where Microsoft is version-wise, so I can
check it in the future?
--
Eldon

Found my answer. Internally, a report was set to a different order. Don't
remember that, but have the fix. Thanks vanderghast, Jerry Whittle, and John
Spencer. for your help.
Still curious about how to get Access' version though.
--
Eldon

Since we no longer have the 'about' tab to check in the Help, how do I know
what version of Office/Access I am on, or for that matter, what the 'current'
version is? At this installation, the network guys control all that anyway,
but it would be nice to be able to check.
Thanks for the help so far, still checking the results. Currently, the
immediate problem of reducing the list to one per person is working, but
inside the module, where the same query is used, the email addresses are
being provided to the module in a different order, from the same query.
Working on that now.
--
Eldon

Your query should be returning multiple records if you have multiple DateDue
values and the other criteria are met.

So the first thing I would check is to make sure DateDue is not different on
the multiple records.

Is your copy of Access 2007 fully patched? That is do you have the latest
service pack installed?

A work around would be to use an aggregate (totals) query. The one below
might work for you.

SELECT GACLOSE.[Oversight Person2] AS SStaff
, GACLOSE.[Oversight2 Email Address] AS SAddr
, Max(GACLOSE.DateDue) as LatestDate
FROM GACLOSE
WHERE GACLOSE.DateDue<Date()
AND GACLOSE.[Displayed completed date] Is Null
AND GACLOSE.OverdueEmailSent=False
GROUP BY GACLOSE.[Oversight Person2]
,GACLOSE.[Oversight2 Email Address]
ORDER BY GACLOSE.[Oversight Person2];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Eldon wrote:
I am using a 2003-structure db file, necessary due to the need to keep
user-level security on the db, and a query that uses distinct that worked
fine when under 2003, is now no longer returning distinct records under
Access2007.
Here is the query:

SELECT DISTINCT GACLOSE.[Oversight Person2] AS SStaff, GACLOSE.[Oversight2
Email Address] AS SAddr, GACLOSE.DateDue
FROM GACLOSE
WHERE (((GACLOSE.DateDue)<Date()) AND ((GACLOSE.[Displayed completed date])
Is Null) AND ((GACLOSE.OverdueEmailSent)=No))
ORDER BY GACLOSE.[Oversight Person2];

This query is used to produce a report, and the report now has Oversight
Person2 repeated many times, where it did not in Access2003. It is also used
to feed a Module that sends out emails, and instead of producing the five
emails to five individuals, it want to send 20+, over and over to the same
people, with the same report attached.
Any suggestions on how/why 2007 acts differently from 2003 in this case,
and what the fix is, would be appreciated.