Counting 2 columns/fields

[RichO]

I have been pulling my hair out over a query application and I'm looking for some help...

For instance, I have a table with [PrimaryName], [SecondaryName] and [JobDate]. I want a query that will display if there is more than one occurrence of the same name in either name field on the same date.

If it were just one field I could do this using GROUP BY and HAVING but because I need to check both name fields I'm stumped. I tried a union query but that seems to group multiple occurrences together as one entry.

Any help is appreciated.

 

[RV]

Using UNION ALL will retrieve duplicates.

i'd suggest to use an autojoin in your query:

SELECT table.*
FROM table, table AS table1
WHERE
(table.PrimaryName = table1.PrimaryName AND table.JobDate = table1.JobDate)
OR
(table.SecondaryName= table1.SecondaryNameAND table.JobDate = table1.JobDate)

RV

 

[RichO]

Thanks for the reply but I'm not having any luck with it. I tried SELECT ALL but it is not selecting the duplicates and the autojoin query is returning all records, and then some.

I've attached a sample DB. The only 2 names that have more than one occurence on the same date are 'Mary Smith' and 'Bob Johnson', but I can't get this thing to work.

Thanks

 

[RV]

Try this query:

SELECT MyTable.PrimaryName, MyTable.SecondaryName,  MyTable.JobDate
FROM MyTable, MyTable AS MyTable1
WHERE MyTable.JobDate=MyTable1.JobDate 
AND 
(MyTable.PrimaryName=MyTable1.PrimaryName 
OR 
MyTable.SecondaryName=MyTable1.SecondaryName
OR
MyTable.PrimaryName=MyTable1.SecondaryName
OR
MyTable.SecondaryName=MyTable1.PrimaryName
)
GROUP BY MyTable.PrimaryName, MyTable.SecondaryName,  MyTable.JobDate
HAVING COUNT(*) >1

RV

 

[RichO]

Excellent!

I could have done this using VBA and recordsets but I figured there must be a way to do it with a query.

Thanks for your help!