Solved: Problem in creating a query.

[kbsudhir]

Hi All,

I have two queries namely queery1 & query2.
Now I want to create a new query lets say query3, which should execute both query1 & query2 & pull the data from query 2(Query two contains complete list of training for the user) which are not equal in the query1(Query contains the list of training which is laready attended by user).

Hence Query3 should compare & pull the trainings which are not attended from query2 by comparing both query 1&2.


Any guidance is appeciated.

:doh: :doh:

Thanks
Sudhir

:help :dunno

[shamsam1]

do post the query....

[CreganTur]

Hence Query3 should compare & pull the trainings which are not attended from query2 by comparing both query 1&2.

If you're looking for data that does not match, then you can use the Find Unmatched Query Wizard. It's a special wizard that you can use to create querries that shows data from 1 table that doesn't have a match in another table. You might be able to have it work with existing querries... I don't know- haven't tried it for that.

On the Querries object tab of the Database window click New -> Find Unmatched Query Wizard.

HTH:thumb

[stanl]

A more practical way might be to base your query in the exisiting tables. Assuming you have tables similar to

Users - each user has unique UserID
Courses - each course has a unique CourseID
Attendance - holds as foreign keys UserID,CourseID

you can then select from Users [by their UserID] WHERE their CourseID in Attendance does not exist in Courses.

If your backend data is structured with this type of relationship, an Exact query can be configured to get what you want. Stan

[Mavyak]

SELECT QUERY2.*
FROM QUERY2 LEFT JOIN QUERY1 ON QUERY2.UNIQUE_FIELD = QUERY1.UNIQUE_FIELD
WHERE QUERY1.UNIQUE_FIELD IS NULL

That will give you all the records that exist in QUERY2 but not in QUERY1

[shamsam1]

as ur using left join it will retun rows in left row

wel u can use innerjoin else

http://www.w3schools.com/Sql/sql_join.asp got hough the tutorial u will get what u neeed

[asingh]

SELECT QUERY2.*
FROM QUERY2 LEFT JOIN QUERY1 ON QUERY2.UNIQUE_FIELD = QUERY1.UNIQUE_FIELD
WHERE QUERY1.UNIQUE_FIELD IS NULL

That will give you all the records that exist in QUERY2 but not in QUERY1

Use this..it is a mismatch query. Using a left outer join. It will return all those fields which do not exist in Query 2. Keep QUERY1.UNIQUE_FIELD unchecked in the user interface, else all blanks will show up..!