Solved

Conditional Select Distinct?

Posted on 1998-11-28
10
296 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:babymugs
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:babymugs
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Would you be so kind as to give me an example of the code specifics?  I would be ever indebted.

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now