Comments inline.
BillCo <co**********@gmail.comwrote in
news:11**********************@w5g2000hsg.googlegro ups.com:
On May 9, 1:24 pm, DBDriver <DBDri...@no.cowrote:
>Thanks for looking at this, BillCo
I found that Access complained about the AND clauee in qryDuplicates,
telling me it should have been part of an Aggregate function.
I found that qryEmployees considered qryDuplicates to be a Parameter.
(Testing in Win98 and Access 97)
It's been an interesing exersize.
Cheers,
DBDriver
here's where it gets tricky - it's so hard to advise without seeing
first hand.
Very true. As I said:
"The final AND condition is actually a Date in my "real" project. LastName
and FirstName are Product Labels and ID Numbers. A query that works as
above will work on my real data."
In this case, the data didn't matter. My interest was in different ways of
structuring the queries and subQueries.
I've always managed to get exactly what I want using multiple queries. In a
few cases I've found that a MakeTable was needed if I wanted same-day
results. Often the single query has been slower.
Try changing qryDuplicates to:
SELECT [LastName] & [FirstName] AS FullName
FROM Employees
WHERE Employees.TitleOfCourtesy='Mr.'
GROUP BY [LastName] & [FirstName]
HAVING Count([LastName] & [FirstName])>1
My analogy (TitleOfCourtesy here = a unique Year in my "real" data) is not
good. The most common criterion will be that field, as duplications in
separate Years are OK while duplications within a Year are not.
The query I used in my original message is fine.
I was just curious about a join using the []. method, since I would use an
actual query instead.
Actually, try to make this one work aswell (i'm curious after writing
the thing!):
Sorry, still won't work for me. :-)
Office 97 on Win98 and Office 2003 on XP.
Error message:
Syntax Error in FROM Clause.
I noticed and changed qryDuplication, which of course made no difference.
Select
Employees.name,
qryDuplicates.FullName
From
Employees Inner Join
(SELECT
FirstName, LastName,
[LastName] & [FirstName] AS FullName
FROM Employees
GROUP BY [LastName] & [FirstName]
WHERE Employees.TitleOfCourtesy='Mr.'
HAVING Count([LastName] & [FirstName])>1
) as qryDuplicates
On ((tblEmpoyees.FirstName = qryDuplicates.FirstName
AND (tblEmployees.LastName = qryDuplication.LastName))
some final tips:
- You may have to go back to basics and analyse what you are trying to
achieve here... is there a better way to get there? Maybe stop
duplicates getting in to begin with.
That's the stage I'm trying to reach. The current query is to help me
prepare the Excel sheet for its new and exciting career as an Access
Database.
- you may wish to revisit your use of first and last names as a
combined unique identifier. if there is no naturally absolutely unique
entity to use, then consider a syntetic primary key
That's just my analogy. My "real" data is more relevant.
- If you absolutely need a fast updatable query (and my experience
tells me that exposing users to data tables is not a good plan - even
masked by a bound form), consider filling a temporary table on the fly
and joining it on your main table to reduce the results returned...
speedville!
You are absolutely right - the Temp table method is a good one.
Slight shift of approach - here's the *structure* of the kind of thing I
was attempting to explore;
A table of File information, obvious fields, DirID for my use.
tblDirInfo:
ID, AutoNumber, primary key
DirID, Long
FileName, Text
FileSize, Long
FileDate, Date/Time
This Query on that table:
SELECT DX.ID, DX.DirID, DX.FileName, DX.FileSize, DX.FileDate
FROM tblDirInfo AS DX INNER JOIN
[SELECT FileName, FileSize, FileDate
FROM tblDirInfo
GROUP BY FileName, FileSize, FileDate
HAVING (COUNT(*) = 1)].
AS Virt ON DX.FileName = Virt.FileName
ORDER BY DX.FileDate;
Look at that in Design View as an example of the kind of "virtual tables
through subqueries" picture I had in my head.
I shouldn't have used the Employees Table to illustrate what I wanted,
because although it provides some handy data for testing, there's a shift
of emphasis when looking at false data.
I mapped my Year info onto the TitleOfCourtesy field.
When you look at a field which can contain values such as Mr, Mrs, Miss,
Master, MZ, Hey You and Plenipotetatissimuss you tend to consider it
unimportant in terms of Grouping. How often, after collecting 20 years of
data would you need to group People information by TitleOfCourtesy, rather
than by Year?
Thanks for your interest.
Cheers,
DBDriver.