• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Conditional Select Distinct?

Is there such a thing as a conditional select distinct query?  The records may have different names, dates, and PO's as well as a unique identifier.  I want no duplicate addresses unless they have a different date.  They can have a different name, PO, or ID but not the same date.  I have tried a million different things and I can't figure it out.  This is probably pretty easy but I haven't been at this very long. Any help would be greatly appreciated.  
0
babymugs
Asked:
babymugs
  • 5
  • 5
1 Solution
 
NullTerminatorCommented:
Without a bit more on the table structure and what is they key this is difficult.
Do you only want the addresses?

select distinct address, theDate from theTable

'\0'
0
 
babymugsAuthor Commented:
Sorry, I guess I wasn't very descriptive of my dilema.  I need all of the fields from the table.  The primary key is currently an auto number field.  We are considering a soundex since autonumber can be volitle.  I've tried the SELECT DISTINCT query you mentioned and that works great until I add the other fields. The table structure is as follows.
fields
ID, Branch, Loc Type, Address, City, St, PO, Date, etc.

I want to be able to list all the sites that have the same address as one record unless they have a different date.  The problem is each address can have many branch names and many POs. That fact is what is hanging me up.  

Does this clarify what I'm looking for?  
0
 
NullTerminatorCommented:
So if you have Ten records at address "123 main st" for 11/1/98 but with differnet branch and PO infomation you want only one, but you want PO, Branch and LOC for only one of the ten because name and address is unique?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
babymugsAuthor Commented:
If I have this
ID,Branch, Address, PO, Date
1,John's, 123 main, 14, 11/18/98
2,Bob's, 123 main, 15, 11/18/98
3,Fred's, 123 main, 16, 11/19/98
4,John's, 456 elm, 17, 11/19/98

I want to see this
1,John's, 123 main, 14, 11/18/98
3,Fred's, 123 main, 16, 11/19/98
4,John's, 456 elm, 17, 11/19/98

because although the address is the same for 3 as it is for 1, the date is different.  

Is this too confusing?  
I do appreciate your time. :)
0
 
NullTerminatorCommented:
I tried several approaches with join, but no luck.  Maybe groupings, but for certain someVBA code would do it

Create recordset based on the fields you want
Set local Address and date variables to NULL
read a record
while not Recordset.EOF()
    if address or date  is different in record
         save new Address and date in variables
         save record to temp table
    end if
    read next record
loop over remaining records

process records based on temp table



0
 
babymugsAuthor Commented:
Would you be so kind as to give me an example of the code specifics?  I would be ever indebted.

0
 
NullTerminatorCommented:
Note Table2 has same structure as table1, you can create empty table by SQL
The where clause forces table creation with no records
SELECT Table1.id, Table1.PO, Table1.Branch, Table1.Address, Table1.theDate INTO Table2
FROM Table1
where 0 = 1;


Function FilterDups()
Dim strSql As String
Dim myDB As Database
Dim rs As Recordset
Dim sAddress As String
Dim dtDate As Date

Set myDB = CurrentDb()
'Create recordset based on the fields you want
strSql = "Select ID, Branch, PO, address, theDate from table1"
Set rs = myDB.OpenRecordset(strSql, dbOpenDynaset, dbReadOnly)

'Set local Address and date variables to known state
sAddress = ""
dtDate = "01/01/1901"

'read a record, (implicit move first on Open)
While Not rs.EOF
    If (rs.Fields("Address") <> sAddress) Or (rs.Fields("theDate") <> dtDate) Then
        'save new Address and date in variables
        sAddress = rs.Fields("Address")
        dtDate = rs.Fields("theDate")
        'save record to temp table
        strSql = "insert into Table2 (id, Branch, PO, address, theDate) "
        strSql = strSql + "values(" + CStr(rs.Fields(0)) + ","
        strSql = strSql + Chr$(34) + rs.Fields(1) + Chr$(34) + ","
        strSql = strSql + CStr(rs.Fields(2)) + ","
        strSql = strSql + Chr$(34) + rs.Fields(3) + Chr$(34)
        ' note-- hashes for MS Access date entry
        strSql = strSql + ",#" + CStr(rs.Fields(4)) + "#)"
        myDB.Execute strSql
    End If
    rs.MoveNext
Wend
rs.Close
Set myDB = Nothing
     
'process records based on temp table

End Function

0
 
babymugsAuthor Commented:
I could kiss you!  I'll try that and see if I can make it work.  I'll keep you posted if you like... Do you ICQ?
0
 
NullTerminatorCommented:
I never tried ICQ, but you coould e-mail me at JebBeasley@Berksnet.com and tell me how it works!
0
 
babymugsAuthor Commented:
Would you be so kind as to give me an example of the code specifics?  I would be ever indebted.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now