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

"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

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

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

"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

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