Shanan212
asked on
Too many results for query
Morning,
I have the following error and could not find any resources to help me here or in internet
I am running a query which will sort a column of combinations and related fields based on user entered values. Since I did not know any other ways, I went in to query design view and in the criteria (of the combination column) put in the following
GetO is a function and rest are user entered fields. I've done a self test on the fields and they are populating fine. The problem is, when I run the query, instead of populating nearly 17 records (based on combination), its populating 94,000 rows and related data
The combination is same but its duplicating the data on the output of query!
Anyone know any common source of this? Any help is much appreciated as this is blocking me from proceeding :/
Thanks!
I have the following error and could not find any resources to help me here or in internet
I am running a query which will sort a column of combinations and related fields based on user entered values. Since I did not know any other ways, I went in to query design view and in the criteria (of the combination column) put in the following
(GetO([Forms]![Point2Point]![ocitytxt] & [Forms]![Point2Point]![oprovcombo])) & (GetO([Forms]![Point2Point]![dcitytxt] & [Forms]![Point2Point]![dprovcombo]))
GetO is a function and rest are user entered fields. I've done a self test on the fields and they are populating fine. The problem is, when I run the query, instead of populating nearly 17 records (based on combination), its populating 94,000 rows and related data
The combination is same but its duplicating the data on the output of query!
Anyone know any common source of this? Any help is much appreciated as this is blocking me from proceeding :/
Thanks!
did you use Group By in your query ?
Please post the sql view of your query
It would also be useful to know either what the Get0() function code looks like, or what the function returns for a couple of values.
ASKER
SQL of query
Only 1 column is filtered using function above
GetO function is in Module1 - Returns a combination based on 4 user entered values. Again, this part is working as the query is actually sorting but somehow bringing in way too many duplicates instead of 17.
SELECT INTER.TCOMBI, INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO BTER
FROM OUT, INTER
WHERE (((INTER.TCOMBI)=(GetO([Forms]![Point2Point]![ocitytxt] & [Forms]![Point2Point]![oprovcombo])) & (GetO([Forms]![Point2Point]![dcitytxt] & [Forms]![Point2Point]![dprovcombo]))));
Only 1 column is filtered using function above
GetO function is in Module1 - Returns a combination based on 4 user entered values. Again, this part is working as the query is actually sorting but somehow bringing in way too many duplicates instead of 17.
Function GetO(origin As String) As String
Dim rst As DAO.Recordset
Dim strSQL As String
Dim varResult As String
strSQL = "Select OUT.TCOMBI " & "FROM OUT " & "WHERE OUT.OUTCOMBI = '" & origin & "' " '& "ORDER BY tblAll.City;"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not (rst.EOF And rst.BOF) Then
GetO = DLookup("TCOMBI", "OUT")
End If
Set rst = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow! Its a matter of having that table opened in the design view eh!
I removed that table and it worked like a charm :)
Thanks a ton!
I removed that table and it worked like a charm :)
Thanks a ton!
ASKER
Thank you for an eye-opening lesson!