Keith Williamson
Kwilliamson at RTKL.com
Tue Dec 12 16:46:15 CST 2006
LOL. I don't get OT at work. But when I get home (around 7:00, or so), I'll check in on the action. :) Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Tuesday, December 12, 2006 5:35 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Select Within Query They don't call me 'code boy' for nothing! ;) Warning, using subqueries like this could be supporting the Democrat Pary. <evil grin>. But if it works I could use some help on a certain thread in OT. William apparently is AWOL. ;) Drew -----Original Message----- From: Keith Williamson [mailto:Kwilliamson at rtkl.com] Sent: Tuesday, December 12, 2006 4:33 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Select Within Query Wow!!!! You must be the subquery KING!!! :)) Haven't tried it yet....but what you wrote LOOKS impressive. LOL!! I'll give it a whirl. Thanks, Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Tuesday, December 12, 2006 4:55 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Select Within Query LOL. Ok, holler offlist if you need help (I said Offline in my first reply, that would be a bit difficult! <grin>). Subqueries can be tricky, but the key is how you use them. For example, I think you are trying to get a field from one table that doesn't have a match with a field in another table. Ie, you have date fields in the timecard table, and date fields in the employeedata table. So, what you need can be accomplished two ways. Let's take the following data structure: tblTimeSheet (Fields (EmployeeID, StartTime, EndTime)) tblEmployeeData (Fields (EmployeeID, Wage, TimeStamp)) The goal is to get the proper wage data for the tblTimeSheet data. The first way to do this is to use a subquery inside of a subquery. The 'inner' subquery finds the correct 'TimeStamp' by getting the Max of TimeStamp where TimeStamp is less then or equal to StartTime. The outer subquery uses the inner subquery in the WHERE clause to match TimeStamps, but the SELECT Clause only returns the Wage field. Here would be the SQL: SELECT EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS TimeWorked, (SELECT Wage FROM tblEmployeeData AS T2 WHERE TimeStamp=(SELECT Max(TimeStamp) FROM tblEmployeeData WHERE TimeStamp<=T1.StartTime AND EmployeeID=T2.EmployeeID) AND EmployeeID=T1.EmployeeID) AS CurrentWage FROM tblTimeSheet AS T1; Note that the actual query assigns tblTimeSheet as T1, the 'outer' subquery assigns tblEmployeeData as T2. This is so each subquery can match the appropriate fields. The outer subquery is only returning the Wage field, so you get one value. This works, however, what if you want to return more then one field from tblEmployeeData. In our example, we don't have more fields to match, but we can create an inner join with one subquery in the WHERE clause of the main query. That's really all a join is anyways. When we say tblTable1 INNER JOIN tblTable2 ON tblTable1.SomeField=tblTable2.SomeField, we are really saying SELECT .... FROM .... WHERE tblTable1.SomeField=tblTable2.SomeField. So, the second method would be this: SELECT T1.EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS TimeWorked, Wage FROM tblTimeSheet AS T1, tblEmployeeData WHERE T1.EmployeeID=tblEmployeeData.EmployeeID AND TimeStamp=(SELECT Max(TimeStamp) FROM tblEmployeeData WHERE EmployeeID=T1.EmployeeID AND TimeStamp<=T1.StartTime) But with the above SQL, we actually have a join we are using in the WHERE clause, so you could also drop that from the WHERE and put it in the FROM, like this: SELECT T1.EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS TimeWorked, Wage FROM tblTimeSheet AS T1 INNER JOIN tblEmployeeData ON T1.EmployeeID=tblEmployeeData.EmployeeID WHERE TimeStamp=(SELECT Max(TimeStamp) FROM tblEmployeeData WHERE EmployeeID=T1.EmployeeID AND TimeStamp<=T1.StartTime) Either of these two would allow you to put other fields from tblEmployeeData in the SELECT line, and have the applicable data appear. Hope this helps, Drew -----Original Message----- From: Keith Williamson [mailto:Kwilliamson at rtkl.com] Sent: Tuesday, December 12, 2006 2:33 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Select Within Query Yeah...I was trying to handle it with subqueries....but it kept coming back that the subquery can't return more than one result....which I couldn't figure out. If we can't make any headway (I've already got two different people taking stabs at it), I'll certainly enlist your help. :) Hate to call in ALL my chips, at once (assuming I even have any chips....I could be bluffing about the chips). :) Thanks, Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com