Combine Sub Queries To Main Query

[Guest]

I got a problem here, I need to do a update queries which use the result
from the 3 Sub Query to Join With The Main One. Is it possible?

Sub Query - OT HRS / Late HRS

SELECT TimeCard.DriverID, Sum(TimeCard.OT_HRS) AS SumOfOT_HRS,
Sum(TimeCard.LATE_HRS) AS SumOfLATE_HRS, TimeCard.DateEntered
FROM TimeCard
GROUP BY TimeCard.DriverID, TimeCard.DateEntered;

Sub Query - MC Counter

SELECT TimeCard.DriverID, Count(TimeCard.StatusID) AS CountOfStatusID,
TimeCard.DateEntered
FROM TimeCard
GROUP BY TimeCard.DriverID, TimeCard.DateEntered
HAVING ((("Where [StatusID]")="3"));

Sub Query - Shift Allowance

SELECT TimeCard.DriverID, Count(TimeCard.SCHD_START) AS CountOfSCHD_START,
TimeCard.DateEntered
FROM TimeCard
GROUP BY TimeCard.DriverID, TimeCard.DateEntered
HAVING (((Count(TimeCard.SCHD_START))>#12/30/1899 12:0:0#));

Main Query - Combine those Value to the Fields

UPDATE [Shift Allowance] LEFT JOIN ([OT And Late HRS] INNER JOIN ([MC
Counter] INNER JOIN ExportExcel ON [MC Counter].DriverID =
ExportExcel.DriverID) ON [OT And Late HRS].DriverID = ExportExcel.DriverID)
ON [Shift Allowance].CountOfSCHD_START = ExportExcel.T_ShiftA SET
ExportExcel.TOT_HRS = [OT And Late HRS].[SumOfOT_HRS], ExportExcel.TLATE_HRS
= [OT And Late HRS].[SumOfLate_HRS], ExportExcel.T_MC = [MC
Counter].[CountOfStatusID], ExportExcel.T_ShiftA = [Shift
Allowance].[CountOfSCHD_Start]
WHERE (((ExportExcel.DriverID)=[MC COunter].[DateEntered])) OR
(((ExportExcel.DriverID)=[OT And Late HRS].[DateEntered])) OR
(((ExportExcel.DriverID)=[Shift Allowance].[Dateentered]));

 

[Guest]

The result I got from the above is "Operation Must Use An Updateable Query."
Is there any problem with my SQL? Sighx...

 

[John Vinson]

The result I got from the above is "Operation Must Use An Updateable Query."
Is there any problem with my SQL? Sighx...

No Totals query, nor any query containing a Totals query as a
component, is ever updateable. It's just an Access limitation.

John W. Vinson[MVP]

 

[John Vinson]

I got a problem here, I need to do a update queries which use the result
from the 3 Sub Query to Join With The Main One. Is it possible?

As noted elsewhere in this thread, no Totals query is ever updateable.

Two suggestions:

- Don't store these totals at all. Just recalculated them as needed.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

- If you MUST store the data for some arcane reason, you can use the
DCount() or DSum() functions (instead of a subquery) to do the
counting or summing.

John W. Vinson[MVP]