Hmmm...seems to work for either the name or the badge number join condition, but not both. Access syntax is a little different for joins; I'll take a look...
----- Original Message ----- From: "Conaway, Amy C" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 05, 2003 11:15 AM Subject: RE: oops! > 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
