01-08-2004, 07:28 AM
I have a table which has field name and some other fields... now i have multiple records for same name
eg Jane Blah Blah
Jane More bla blah
Now i wrote a simple sql stmt 'select * from mytable where name = 'jane'
and i use it to create my recordset. It gives me just 1 row when there are infact 3 rows !!! well this code worked yesterday and nothing was changed !!! and now suddenly it just conks off on me :(
anyone has any ideas ????
01-08-2004, 07:42 AM
just on a first review Name is a reserved word, change your field name to something else.
And there is nothing else fundamentality wrong with this statement:
"SELECT * FROM myTable WHERE firstName = 'Jane'"
You need to look at your data structure and make sure your records are correct and truely match this condition.
01-08-2004, 07:42 AM
first of all, wich database are you using?
in MS Access, for example, the 'distinct' clause is implicit in some versions
you have to check that the "entire" tuples are diferent from each other.
01-08-2004, 07:43 AM
dont change anything!
SELECT * FROM Table1 WHERE [Name]="Jane"
01-08-2004, 07:46 AM
It is still bad practice to use a reserved word as a field name that was my point. This should be something to always watch out for.
01-08-2004, 07:49 AM
How can i know if distinct is impilicit and how to remove that ????
this what i have to do,
i have some applicants enter data in a d/b. then i have the proper data in another table. now for each applicant i check the mistakes by comparing with original record in the actual table. for this first i selected the distinct names and put in a recorset. then for each name i selected the corresponding rows and put into another recorset and then i go arnd checking.
i changed the name of the field and it still doesnt work. i tried a query with same sql and it gives me correct o/p !!!
01-08-2004, 07:57 AM
Your query WILL return the right records, as you stated...there is nothing fundamentality wrong with it. There is no explicit DISTINCT action occuring..and it is good that you changed your field name...yes, if you use 's it will work, but it is bad practice to use a reserved word as a field name..it is better to avoid the problem then try to fix it. Why it is not returning the correct records in your database is a design issue:
So basically the user enters data onto a user form..is this data going into the same table as the "proper table" or do you have two tables? Are you comparing two tables? You say you are looking for mistakes. And you also commented that you are getting all distinct names and putting them in a recordset. If you are running this query on a table with distinct records based on name, this query will return only one record. I am confused about the structure here. Are you running this query on a "main table" with numerous records, or on recordset with only distinct records?
01-08-2004, 08:03 AM
Well i decided to comment all that and see if it retrives correct rows for a name
I have a name Jane and i write a code as below
Dim mydb As Database
Dim rst As Recordset
Dim sql As String
Set mydb = CurrentDb
MsgBox "set db"
sql = "Select FullName from TestData where FullName = 'Jane';"
Set rst = mydb.OpenRecordset(sql)
I have 3 records for this name and it gives me just 1 !!!!!
01-08-2004, 08:07 AM
I just tried selecting some other field and it always gives me the 1st rec !!!!! no other record !!!! aarrrrrggggghhh whats happening !!!!
01-08-2004, 08:11 AM
I tried select * from testdata and i gives me just 1 record !!!!!!
01-08-2004, 08:16 AM
"Select FullName from TestData where FullName = 'Jane'"
OK, calm down :). Look at this query for a second. In this query you are returning ONLY the fullname field. Then you say, select only the records where FullName = Jane. This will produce one record. See this example below from an Access Database of mine
WHERE DOCID = 241;
I have a table with 2000 records on it..but this query produces ONE record. I am selecting only the DocID field which is unique, and I say in the WHERE Clause this DocID has to equal 241..so I only get one record..the DocID field where it is 241. If I do this...
SELECT * FROM DOCUMENTS WHERE DocCode = 'DC' I get many records, this is because I am selecting all fields within all records where the DocCode field is equal to DC.
You need to structure your query differently, this is the source of your problem.
01-08-2004, 08:18 AM
Select * FROM TestData where FullName = 'Jane'
This will produce one record if there is only one record in the TestData table with the FullName field = 'Jane'
If you run this code...does it show all the records?
Select * FROM testData
If this still returns one record, run your query in the Access Query builder, do they work there? If so, there is something going on within your application interface.