If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cartesian Help Please
I have two queries built into one. From my [clock_table] the other from my
[time table]. This data base I have inherited. The [clock_table] which I created dervies work time from date diff. Unfortunately the [Time table] does not do the time calculation but the hours are physically put in. Eventualy the [Clock_table] will superseed the [time table] for calculating time. In the mean time we have manufacturing projects that are going to be active from one to two years. The new jobs use the [clock_table] but in the mean time I still have to deal with the legacy of the [time table]. Im trying to get total hours worked per [jobID] for a report. I did not get to the point of doing the math yet because of the cartesian problem. Here is my SQL: SELECT Clock_Table.JobID, Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate, DateDiff("n",[Startdate],[Stopdate])\60 & Format (DateDiff("n",[Startdate],[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff ("n",[Startdate],[Stopdate]) AS TotalHoursMinutes, Round([TotalHoursMinutes] /60,2) AS HundredTime, Clock_Table.Chg_Amt, ([Hundredtime]*[Chg_Amt]) AS Charge_total, [Time Table].Time FROM Clock_Table INNER JOIN [Time Table] ON Clock_Table.JobID = [Time Table]. [Job ID]; Gaetanm -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Cartesian Help Please
"Gaetanm via AccessMonster.com" u28527@uwe wrote in message news:722af8b447695@uwe... I have two queries built into one. From my [clock_table] the other from my [time table]. This data base I have inherited. The [clock_table] which I created dervies work time from date diff. Unfortunately the [Time table] does not do the time calculation but the hours are physically put in. Eventualy the [Clock_table] will superseed the [time table] for calculating time. In the mean time we have manufacturing projects that are going to be active from one to two years. The new jobs use the [clock_table] but in the mean time I still have to deal with the legacy of the [time table]. Im trying to get total hours worked per [jobID] for a report. I did not get to the point of doing the math yet because of the cartesian problem. Here is my SQL: SELECT Clock_Table.JobID, Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate, DateDiff("n",[Startdate],[Stopdate])\60 & Format (DateDiff("n",[Startdate],[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff ("n",[Startdate],[Stopdate]) AS TotalHoursMinutes, Round([TotalHoursMinutes] /60,2) AS HundredTime, Clock_Table.Chg_Amt, ([Hundredtime]*[Chg_Amt]) AS Charge_total, [Time Table].Time FROM Clock_Table INNER JOIN [Time Table] ON Clock_Table.JobID = [Time Table]. [Job ID]; You weren't very clear in your post, but it seems like what you actually want is not records in the Clock table and Time table that refer to the exact same thing, since obviously each is tracking different things. Instead, you would probably want to use a UNION query to get a set of records of the time recorded in the clock table for one Job ID and then also get the records for that job ID in the Time table. At that point, you'd use another query to add up the time in all the records to arrive at the total time. HTH; Amy |
#3
|
|||
|
|||
Cartesian Help Please
Amy Blankenship wrote:
I have two queries built into one. From my [clock_table] the other from my [time table]. [quoted text clipped - 31 lines] Table]. [Job ID]; You weren't very clear in your post, but it seems like what you actually want is not records in the Clock table and Time table that refer to the exact same thing, since obviously each is tracking different things. Instead, you would probably want to use a UNION query to get a set of records of the time recorded in the clock table for one Job ID and then also get the records for that job ID in the Time table. At that point, you'd use another query to add up the time in all the records to arrive at the total time. HTH; Amy Amy Thanks for the Quick Reply I did use a UNION and that part is working great!! I have added a bit to it. I"m trying to filter from the [Job Table] [Finish Date] = NULL This is my SQL: SELECT [Test_Time Table_for_union].[Job ID], [Test_Time Table_for_union]. Date, [Test_Time Table_for_union].Time, [Test_Time Table_for_union].[Chg Amt] FROM [Test_Time Table_for_union] UNION SELECT Work_hours.JobID, Work_hours.StopDate, Work_hours.HundredTime, Work_hours.Chg_Amt FROM Work_Hours UNION SELECT [Job SHEET].[Job Id],[Job SHEET].[Finish Date],[Job SHEET].[Time] ,[Job SHEET].[CHG_AMT] FROM [Job Sheet] WHERE [Finish Date] = Null Since I'm new at doing UNIONS I have no Idea why it wont work. Can you help (Again) Thanks Gaetanm -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200705/1 |
#4
|
|||
|
|||
Cartesian Help Please
Perhaps the following is what you want
SELECT [Test_Time Table_for_union].[Job ID] , [Test_Time Table_for_union].Date , [Test_Time Table_for_union].Time , [Test_Time Table_for_union].[Chg Amt] FROM [Test_Time Table_for_union] UNION SELECT Work_hours.JobID , Work_hours.StopDate , Work_hours.HundredTime , Work_hours.Chg_Amt FROM Work_Hours UNION SELECT [Job SHEET].[Job Id] ,[Job SHEET].[Finish Date] ,[Job SHEET].[Time] ,[Job SHEET].[CHG_AMT] FROM [Job Sheet] WHERE [Finish Date] IS Null '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Gaetanm via AccessMonster.com wrote: Amy Blankenship wrote: I have two queries built into one. From my [clock_table] the other from my [time table]. [quoted text clipped - 31 lines] Table]. [Job ID]; You weren't very clear in your post, but it seems like what you actually want is not records in the Clock table and Time table that refer to the exact same thing, since obviously each is tracking different things. Instead, you would probably want to use a UNION query to get a set of records of the time recorded in the clock table for one Job ID and then also get the records for that job ID in the Time table. At that point, you'd use another query to add up the time in all the records to arrive at the total time. HTH; Amy Amy Thanks for the Quick Reply I did use a UNION and that part is working great!! I have added a bit to it. I"m trying to filter from the [Job Table] [Finish Date] = NULL This is my SQL: SELECT [Test_Time Table_for_union].[Job ID], [Test_Time Table_for_union]. Date, [Test_Time Table_for_union].Time, [Test_Time Table_for_union].[Chg Amt] FROM [Test_Time Table_for_union] UNION SELECT Work_hours.JobID, Work_hours.StopDate, Work_hours.HundredTime, Work_hours.Chg_Amt FROM Work_Hours UNION SELECT [Job SHEET].[Job Id],[Job SHEET].[Finish Date],[Job SHEET].[Time] ,[Job SHEET].[CHG_AMT] FROM [Job Sheet] WHERE [Finish Date] = Null Since I'm new at doing UNIONS I have no Idea why it wont work. Can you help (Again) Thanks Gaetanm |
#5
|
|||
|
|||
Cartesian Help Please
"Gaetanm via AccessMonster.com" u28527@uwe wrote in message news:722ec0e6c698e@uwe... Amy Blankenship wrote: I have two queries built into one. From my [clock_table] the other from my [time table]. [quoted text clipped - 31 lines] Table]. [Job ID]; You weren't very clear in your post, but it seems like what you actually want is not records in the Clock table and Time table that refer to the exact same thing, since obviously each is tracking different things. Instead, you would probably want to use a UNION query to get a set of records of the time recorded in the clock table for one Job ID and then also get the records for that job ID in the Time table. At that point, you'd use another query to add up the time in all the records to arrive at the total time. HTH; Amy Amy Thanks for the Quick Reply I did use a UNION and that part is working great!! I have added a bit to it. I"m trying to filter from the [Job Table] [Finish Date] = NULL This is my SQL: SELECT [Test_Time Table_for_union].[Job ID], [Test_Time Table_for_union]. Date, [Test_Time Table_for_union].Time, [Test_Time Table_for_union].[Chg Amt] FROM [Test_Time Table_for_union] UNION SELECT Work_hours.JobID, Work_hours.StopDate, Work_hours.HundredTime, Work_hours.Chg_Amt FROM Work_Hours UNION SELECT [Job SHEET].[Job Id],[Job SHEET].[Finish Date],[Job SHEET].[Time] ,[Job SHEET].[CHG_AMT] FROM [Job Sheet] WHERE [Finish Date] = Null If you're trying to filter out the null finish dates, then you want WHERE [Job Sheet].[Finish Date] IS NOT NULL. One thing I've found helps with UNION queries is to make the individual SELECT queries in the query grid until they return what I want, then I copy and paste the SELECT statements all together into the UNION. The main thing that can cause a glitch at that point is having data types that don't match or differing numbers of columns. HTH; Amy |
#6
|
|||
|
|||
Cartesian Help Please
Amy & John
Sorry for getting back to you so late. I just got back into the project and your help has been right on the money. I had some column definitions that were off and with the SQL example this whole union thing started to make sence. Thank you again Gaetan Amy Blankenship wrote: I have two queries built into one. From my [clock_table] the other from my [quoted text clipped - 41 lines] FROM [Job Sheet] WHERE [Finish Date] = Null If you're trying to filter out the null finish dates, then you want WHERE [Job Sheet].[Finish Date] IS NOT NULL. One thing I've found helps with UNION queries is to make the individual SELECT queries in the query grid until they return what I want, then I copy and paste the SELECT statements all together into the UNION. The main thing that can cause a glitch at that point is having data types that don't match or differing numbers of columns. HTH; Amy -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|