Results 1 to 2 of 2
Thread: Strange Query (Access 2K)
2002-09-27, 15:50 #1
- Join Date
- Apr 2002
- Arkansas, USA
- Thanked 0 Times in 0 Posts
Strange Query (Access 2K)
I had a co-worker send this to me. For some reason, I do not see what they are trying to accomplish. Here is the test that ws forwarded to me along with the database.
Using the attached simple "recotest" I can find the unmatched policies just fine. But when I do the Query for "policies = but amts unmatched" it produces nonsense when the result should be "ho pols: C for $40 and D for $50" and "Mich pols C for $50 and D for $60".
I am lost when trying to figure out what this person is trying to do with the queries.
Please help if you can.
Thanks in advance for any assistance.
2002-09-27, 22:50 #2
- Join Date
- Dec 2001
- Melbourne, Australia
- Thanked 27 Times in 27 Posts
Re: Strange Query (Access 2K)
I don't know what he was trying to do either, but if I was you I would recreate the queries, at least you know that they will be working. Otherwise you have to define what the queries should be doing and change them accordingly.
When you say you ran the query "policies = but amts unmatched" I presumed you meant the query "ho pols = mich pols but amts <>".
This query has query "ho pols =mich pols" as it's source. This query's SQL is as follows:
SELECT [ho table].[ho pols], [ho table].[ho amts], [mich table].[mich pol], [mich table].[mich amt]
FROM [ho table] INNER JOIN [mich table] ON [ho table].[ho pols]=[mich table].[mich pol]
WHERE ((("ho pols")<>"mich pol"))
ORDER BY [ho table].[ho pols];
As you can see the WHERE clause will always be true because you are comparing 2 independant text fields, not DB record fields.