Solved

Conditional Select Distinct?

Posted on 1998-11-28
10
334 Views
Last Modified: 2012-05-04
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
Comment
Question by:babymugs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 3

Accepted Solution

by:
NullTerminator earned 170 total points
ID: 1969507
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
 

Author Comment

by:babymugs
ID: 1969508
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
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1969509
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
Industry Leaders: 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!

 

Author Comment

by:babymugs
ID: 1969510
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
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1969511
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
 

Author Comment

by:babymugs
ID: 1969512
Would you be so kind as to give me an example of the code specifics?  I would be ever indebted.

0
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1969513
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
 

Author Comment

by:babymugs
ID: 1969514
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
 
LVL 3

Expert Comment

by:NullTerminator
ID: 1969515
I never tried ICQ, but you coould e-mail me at JebBeasley@Berksnet.com and tell me how it works!
0
 

Author Comment

by:babymugs
ID: 1969516
Would you be so kind as to give me an example of the code specifics?  I would be ever indebted.

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question