Take results from one query and put them straight into another (1 Viewer)

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
Hello,

I have a strange problem that I would appreciate some help with. I have a query that is basically search a table with one linked table and I need to use the DISTINCT command as there is possibility of duplicate results. However as I understand it with the DISTINCT command you can only have the one field in the query SELECT, so I am ending up with a list of numbers (record key fields). Which is fine..

But I then need to use those ID numbers to get the actual data the user wants to see.

So I am wondering if I can take the results of the first query and pump them straight into another query and then showing results to user

Probably easy once you know how .. at least I hope so

My current query is being generated based on the users input ... but the bit that I think that is currently relevent is:

Code:
'Concatenate the two SQL strings and then use.
                    CurrentDb.QueryDefs("flexQuery").SQL = "SELECT DISTINCT(address_book.pID) FROM address_book INNER JOIN prsn_interests_assoc ON prsn_interests_assoc.Persno=address_book.pID WHERE " & vWhere
                    DoCmd.OpenForm stDocName, , , vWhere

any help/direction would be great.

Thanks in advanced.

Graham.
 

jal

Registered User.
Local time
Today, 04:05
Joined
Mar 30, 2007
Messages
1,709
I have no idea, nor have I ever used DoCmd.OpenForm. Question, however: aren't you filtering twice? You have "vWhere" both in the query and in the OpenForm. Isn't that a double-filter?

Also, are you getting an error message?
 

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
Hi Jal,

Thanks for the reply. The code i put in the post actually works as I expect it to, so not sure about the double filitering thing...I have to confess it is just modified code from another query that works fine and has the same vWhere in both places...

So at the moment no, I don't get any errors...but I haven't really begun changing the code to what I need it to do as I don't really knoww here to begin...I was thinking some sort of inner join might do the trick...but can't see how I can do that.

Any thoughts?

Cheers,

Graham.
 

jal

Registered User.
Local time
Today, 04:05
Joined
Mar 30, 2007
Messages
1,709
Sorry, I didn't read well enough. I think I'm seeing what your problem is, finally.

However as I understand it with the DISTINCT command you can only have the one field in the query SELECT
I am of a different opinion, but could be wrong, as I never seem to have occasion to use DISTINCT.

In my view, DISTINCT works much like GROUP BY. Pick as many fields as you want, for example:

GROUP BY City, State

will eliminate duplicates in the sense of returning 1 instance of

Tulsa, OK

and only 1 instance of

Los Angeles, California

even if the table has multiple instances of Tulsa, Ok and multiple instances of Los Angeles, Ca.
 

jal

Registered User.
Local time
Today, 04:05
Joined
Mar 30, 2007
Messages
1,709
By the way, the only difference between Group By and DISTINCT is that, with Group By, the engine does more work behind the scenes - secretly it keeps track of how many dups are in each set, and the exact row number of each dup in the set. This information allows you to perform aggregates, for example you could use the COUNT function to report how many instances of Tulsa, OK, and how many instances of Los Angeles, CA.
 

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
Hi, I am not sure that the Group By function does what I need it to do..group by just seems to group them together for output. I just want one of each person record...

Cheers,

Graham.
 

neileg

AWF VIP
Local time
Today, 11:05
Joined
Dec 4, 2002
Messages
5,975
Use a second query to join the query you have back to the table. You can do this by adding your query to the design grid.
 

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
Hey Neil,

Thanks for the reply....sounds good but I am VERY new to this still..and not sure what you mean by design grid...?

Thanks in advanced :)

Graham.
 

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
Hey Neil,

Thanks for the reply....sounds good but I am VERY new to this still..and not sure what you mean by design grid...?

Thanks in advanced :)

Graham.
 

neileg

AWF VIP
Local time
Today, 11:05
Joined
Dec 4, 2002
Messages
5,975
The query designer. Where you add tables, you can also add queries.
 

grae.uk

Access Noob!
Local time
Today, 11:05
Joined
Oct 13, 2008
Messages
9
The query designer. Where you add tables, you can also add queries.
not sure that will work because the whole thing is part of a big block of code...let me post that..

Code:
Private Sub searchDBButton_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim vWhere As Variant
    Dim x As Integer
    x = 1
        
    DoCmd.Close acForm, "sResults_address_book", acSaveNo
    stDocName = "sResults_address_book"
    
    'check there is something in the search textbox
    If Nz(Me.searchDB, "") <> "" Then
    
        If IsNull(Me.Controls("Criteria" & x)) Then
            MsgBox "You have not selected where to search for " & Me.searchDB, vbInformation, "Error - Something is wrong!"
            Exit Sub
        Else
        
            vWhere = Null
           
            vWhere = Me.Controls("Criteria" & x) & " LIKE '*" & Me.searchDB & "*'"
            Dim counter As Long
                For counter = 2 To 5
                    If Not IsNull(Me.Controls("Criteria" & counter)) Then
                     vWhere = vWhere & " " & Me.Controls("ANDOR" & counter) & " " & Me.Controls("Criteria" & counter) & " LIKE '*" & Me.searchDB & "*'"
                    End If
            Next
                If Nz(vWhere, "") = "" Then
                    MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
                    "Search Cancelled.", vbInformation, "Error - Something is wrong!"
                Else
                    'Concatenate the two SQL strings and then use.
                    CurrentDb.QueryDefs("flexQuery").SQL = "SELECT firstname, pID FROM address_book INNER JOIN prsn_interests_assoc ON prsn_interests_assoc.Persno=address_book.pID WHERE " & vWhere
                    'CurrentDb.QueryDefs("flexQuery").SQL = "SELECT * FROM address_book WHERE " & vWhere
                    DoCmd.OpenForm stDocName, , , vWhere
                    
                    
                End If
        End If
    Else
        MsgBox "You have not entered the value that you wish to search for.", vbInformation, "Error - Something is wrong!"
        Exit Sub
    End If
 
End Sub

is it still possible to do what you are suggesting? because the WHERE clause is generated based on the users input...?

Cheers,

Graham.
 

jal

Registered User.
Local time
Today, 04:05
Joined
Mar 30, 2007
Messages
1,709
BTW, I wasn't advising you to use Group By necessarily, but merely pointing out that SELECT Distinct can, as far as I know, select more than one column.
 

Users who are viewing this thread

Top Bottom