code to look for "old" dupe data??

[jeffmoore]

Hi
I have to run some sort of qry or code to do the following:
I have a record set that has records considered similar basied on four fields. there is also a date stamp field attached to each record.
I need to be able to view in a form records that are "new" within the last 10 days.

Of these records the similar ones must be grouped without regard to date IF there is NO associated similar record in a coresponding table with in the last 10 days.

Confused??
I can't figure out how to do it
TIA
Jeff

 

[SBendBuckeye]

Hello Jeff,

I think a SubQuery (which is basically a query within a query) would help you here. Let's agree to call your main table Table1 and your corresponding table Table2. Try doing the following:

1. Create a query for Table2 that will return the primary key for all records within the last ten days. Because of the way we will use it later, it can only return one field. If you have a multicolumn primary key then you will need to concatenate them into one. Here are a couple examples:

Select PriKey
From TableName
Where CreateDate >= Today() - 10
Order By PriKey

Select LastName & FirstName & MiddleInit As PriKey
From TableName
Where CreateDate >= Today() - 10
Order By LastName, FirstName, MiddleInit

2. Now create your main query and use In or Not In to pull in the above Table2 query something like this:

Select FieldList
From Table1
Where SomeConditions
And PriKey In/Not In (Select PriKey
From TableName
Where CreateDate >= Today() - 10
Order By PriKey)
Order By PriKey

Select FieldList
From Table1
Where SomeConditions
And LastName & FirstName & MiddleInit In/Not In
(Select LastName & FirstName & MiddleInit As PriKey
From TableName
Where CreateDate >= Today() - 10
Order By PriKey)
Order By PriKey

3. If you need to show all records and sort them differently based on whether or not they are In/Not In the subselect then use the above in a union query with a defined field for your sort something like this:

Select FieldList, 0 As NewSortField
From Table1
Where SomeConditions
And PriKey Not In (Select PriKey
From TableName
Where CreateDate >= Today() - 10
Order By PriKey)
Union
Select FieldList, SortField As NewSortField
From Table1
Where SomeConditions
And PriKey In (Select PriKey
From TableName
Where CreateDate >= Today() - 10
Order By PriKey)
Order By 2, 4, NewSortField

The last example I believe should match your stated goals. By setting NewSortField to 0 if there was no corresponding Table2 match they will be grouped irrespective of date in that case. However, if there was a corresponding Table2 match in the bottom half of the Union query, then some Table1 value is attached to NewSortField so that they will sort as desired.

Not everyone knows that you can Order By Column Number as well as by Column Name. I threw that in as an extra because it allows you to use different column names from different tables in the top and bottom half of a Union query (of course the data types must match) and still be able to sort them even though they have different names. Good Luck!

Have a great day!

j2consulting@yahoo.com