Solved

Conditional Select Distinct?

Posted on 1998-11-28
10
324 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

789 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