I have a table from an excel sheet that comes in the format above with up to 5000 rows in record groups from 1 to 10 rows in each. The repeating data is how it comes from our all singing and dancing case recording software and I can’t change that fact.

Take a group of records with the same TradingName such as “The Herriots” group, There should be one row in the group where FdInspAction = LastInspDate (in this case 06/01/2007) . In this group there is no match so we need to identify the group. The “Bloggs” group is fine (see Row 8) so can be ignored.

My feeling is it can’t be done other than by visual checking. Can anyone think of a way around this?


ID TradingName FdInspAction LastInspDate
1 The Heriotts 03/02/2001 06/01/2007
2 The Heriotts 10/02/2002 06/01/2007
3 The Heriotts 16/02/2003 06/01/2007
4 The Heriotts 25/01/2005 06/01/2007
5 The Heriotts 04/01/2006 06/01/2007
7 Bloggs Ltd 02/01/2005 25/01/2005
8 Bloggs Ltd 25/01/2005 25/01/2005

Not hard. Create a query. Add a calculated colum that uses DateDiff() to find the difference between FdInspAction and LastInspDate. Where this value is zero, the two dates are the same. So if you have a group where there is no zero value, this is what you want.

I would run a second query with the first as source filtering on the 0 value to avoid any visual scanning.

Brian

Thanks gentlemen I shall have a bash later.

I added a number column to the table called "new" and this is what I've got so far, the only expression that returns a value is Count and I'm not sure that's what I should be using. It does look like it's finding all the groups with a match and places a zero in "new" but I need to find the groups without.

SELECT tblALL.ID, tblALL.CaseFullRef, tblALL.TradingName, tblALL.FdInspAction, tblALL.LastInspDate, Count(tblALL.new) AS CountOfnew
FROM tblALL
GROUP BY tblALL.ID, tblALL.CaseFullRef, tblALL.TradingName, tblALL.FdInspAction, tblALL.LastInspDate
HAVING (((Count(tblALL.new))=DateDiff("d",[FdInspAction],[LastInspDate])));


I've tried various forms based on this expression posted in an old thread by by pbaldy IIf(DateDiff(...) < 1, new, 0) but keep gettin type mismatch or syntax errors.

Try this:SELECT tblAll.TradingName, Min(DateDiff("d",[FdInspAction],[LastInspDate])) AS NewField
FROM tblAll
GROUP BY tblAll.TradingName
HAVING (((Min(DateDiff("d",[FdInspAction],[LastInspDate])))<>0));

Perfect Neil Thanks. Me I just get banjaxxed with all the brackets. I don't understand entirely how it works but I'll study it closely.

Once again the forum and its people come up with the goods.:)

I didn't write that SQL, Access did. I just designed the query in the query grid!

I didn't write that SQL, Access did. I just designed the query in the query grid!


Mmm but your understanding of what to put into the grid and why is greater than mine. ;)