Recent content by spaddhu

I'm sorry, I oversaw that query.. Here is the one that should work... ... WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 ))) AND ((mid(PERSON_NAME.Lastname_name_local1,1,3) In (SELECT mid([Lastname_name_local1],1,3) FROM [PERSON_NAME] As TMP1 GROUP...

You can do it in 2 steps. First write an insert query that inserts distinct ProductNames, with Null ProductSorts from your Product table to the NewTable. Next write an update query that appends ProductSorts from Product table to NewTable where NewTable.ProductName=ProductTable.ProductName

Your first string pattern will not work. However, the second one seems correct. I would suggest take a copy of your main table, convert the memo to text field and see if it works. Memo fields do behave peculiarly sometimes...

Rick, I don't know why you want to stick to Design View. I agree it helps you build queries easier and faster. But to have absolute control over the query I would suggest you also acquaint yourself with the SQL view. apr pillai is correct in terms of SQL.. but if you want in terms of Design...

... WHERE (((PERSON.Dob) In (SELECT [Dob] FROM [PERSON] As Tmp GROUP BY [Dob] HAVING Count(*)>1 ))) AND (((PERSON_NAME.Lastname_name_local1) In (SELECT mid([Lastname_name_local1],1,3) FROM [PERSON_NAME] As TMP1 GROUP BY Mid([Lastname_name_local1],1,3) HAVING Count(*)>1))) ORDER BY PERSON.Dob;

Please refer to this thread, and see if this works out... http://www.access-programmers.co.uk/forums/showthread.php?t=183541

For ORDER BY, it is always better to specify it in the Design View. This way you are not going to face any difficulty. Just indicate Ascending/Descending in the Sort row of the required field.

I see very little scope of achieving this using queries. I think VB interference is necessary. I'm not versed with VB knowledge. However, the logic will be to traverse each record (for loop) in a recordset of one Product, appending the ProductSort field to a string and finally displaying it...

Victoria, Which table do you want to update ? A new table or one that you have already specified? Also can you tell what error is thrown to indicate that the dynaset is not updateable?

It is possible that [CorrectProduct] and [RemarkCodeCorrect] have Yes/No/Null values. If they have Null values, [CorrectProduct]="Yes" will not work for those rows. So add another 'and' clause that checks if the field is not null also. So your expression will look like...

This puts your question in a more precise way. And your other explanation too was better put than your original. Here is your query... SELECT tempQry1.DOB, (tempQry1.FName & ", " & tempQry1.LName) as Name1, (tempQry2.FName & ", " & tempQry2.LName) as Name2 FROM myQry as tempQry1 INNER JOIN...

The query will look something like SELECT tblEmp.Name,NZ(Count(tblReprints.Error),0) FROM tblEmp LEFT OUTER JOIN tblReprints ON tblEmp.Name=tblReprints.Name GROUP BY tblEmp.Name

Correct Namliam. Thanx for the pointing out. I would go by your view.

It's OK Ankit, next time be sure to post a new thread for your queries. Krist51 - Thanx. :)

I assume that this means you got it working ?? Happy for being able to help...