Distinct not working in 2007 as in 2003

E

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.
 
V

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
 
J

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

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
 
E

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


John Spencer said:
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.
Click to expand...
Click to expand...
 
J

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
 
E

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


Eldon said:
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


John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
E

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


Eldon said:
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


Eldon said:
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


John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

distinct month AND year 2
problem with a DISTINCT query 4
Unique Query in not working correctly ... too complex? 2
Counting Nulls as well in a select distinct... 4
Access 2003 truncates code in queries 3
distinct users per week 5
Count Distinct in a crosstab 2
Access2003 interface preferred over 2007 3

Top