Thanks guys,...but I tried both ways, and neither worked In Dina's, the combination statement alone produces a zero query, and then because it is ANDed with the badge segment...the total query is still zero. The only change I made was to make the JOIN an INNER JOIN because im using MS Access. Your logic made sense, and because there aren't any syntax errors im still at a loss.
When I tried Eric's I was confident too that it would work because I had done something very similar in another query where I simply UNIONed multiple statements. But again, only the badge related section of the union worked. The name combination alone produced a zero query. If you have any more ideas...i'm all ears. Thanks again! -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 11:28 AM To: SQL Subject: Re: oops! Amy, In your WHERE clause you're saying "I want to join on the badge number OR the name." SQL's confused. You can have it match badge numbers AND names, but it doesn't know what you want when you say you want to match badge numbers OR names. My advice? Move the join to the FROM clause for clarity: SELECT c.Invalid_User, v.Last_Name, v.First_Name FROM CH_Employee_ATT_feb c JOIN Valid_Employees v ON (c.ISP_Employee_Badge_Number = v.[Badge Number]) AND (c.ISP_User_First_Name + c.ISP_User_Last_Name = v.First_Name + v.Last_Name) The reason I changed your test for a name match is because SQL doesn't know you want it to look for the concatenated first and last name of each record unless you tell it to do that. Also, since your tables are joined on name, you will only need to SELECT the first and last names from one of the tables. Hope that's what you were looking for. BTW, this is untested. ~Dina ----- Original Message ----- From: "Conaway, Amy C" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 05, 2003 9:50 AM Subject: oops! > ..it would help if I pasted the code.... > > SELECT CH_Employee_ATT_feb.ISP_User_Last_Name, CH_Employee_ATT_feb.ISP_User_First_Name, CH_Employee_ATT_feb.ATT_Usage_feb.Invalid_User, Valid_Employees.Last_Name, Valid_Employees.First_Name > FROM CH_Employee_ATT_feb, Valid_Employees > WHERE CH_Employee_ATT_feb.ISP_Employee_Badge_Number = Valid_Employees.[Badge Number] OR (CH_Employee_ATT_feb.ISP_User_Last_Name = Valid_Employees.Last_Name AND CH_Employee_ATT_feb.ISP_User_First_Name = Valid_Employees.First_Name) ; > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
