FW: [MS_AccessPros] Need help with Subtotal Query

This is being forwarded from James. I would question the data type of RE_UIC. Is it text?
Are Reset_Plan_Viewer and Reset_Events both tables?

Duane Hookom
MS Access MVP


From: j1eggert@yahoo.com
Subject: Re: [MS_AccessPros] Need help with Subtotal Query

Unfortunately, I already tried to use a report, but the amount of columns exceed the limit in Access. The logistics department wants to view everything.
SELECT Max(Reset_Plan_Viewer.BCT_Name) AS MaxOfBCT_Name, Max(Reset_Plan_Viewer.ARMT_Plan_ID) AS MaxOfARMT_Plan_ID, Max(Reset_Plan_Viewer.ARMT_Plan_Name) AS MaxOfARMT_Plan_Name, Max(Reset_Plan_Viewer.Plan_Status) AS MaxOfPlan_Status, Max(Reset_Plan_Viewer.Date_Executed) AS MaxOfDate_Executed, Max(Reset_Plan_Viewer.Reset_Type) AS MaxOfReset_Type, Max(Reset_Plan_Viewer.Plan_Claimed_Unclaimed) AS MaxOfPlan_Claimed_Unclaimed, Reset_Events.RE_UIC, Reset_Plan_Viewer.LIN, Max(Reset_Plan_Viewer.NIIN) AS MaxOfNIIN, Max(Reset_Plan_Viewer.NIIN_Description) AS MaxOfNIIN_Description, Min(Reset_Plan_Viewer.SOS) AS MinOfSOS, Max(Reset_Plan_Viewer.Disposition_Provider) AS MaxOfDisposition_Provider, Max(Reset_Events.RE_LongUnitDesc) AS MaxOfRE_LongUnitDesc, Min(Reset_Events.RE_Mobilize_Date) AS MinOfRE_Mobilize_Date, Min(Reset_Events.RE_PreDeployTrainDate) AS
MinOfRE_PreDeployTrainDate, Min(Reset_Events.RE_LBE_Induct_Date) AS MinOfRE_LBE_Induct_Date, Min(Reset_Events.RE_GOT_Date) AS MinOfRE_GOT_Date, Min(Reset_Events.RE_Employ_AutoPlanDate) AS MinOfRE_Employ_AutoPlanDate, Min(Reset_Events.RE_ARDT_Date) AS MinOfRE_ARDT_Date, Min(Reset_Events.RE_CBH_Date) AS MinOfRE_CBH_Date, Min(Reset_Events.RE_ResetBeginDate) AS MinOfRE_ResetBeginDate, Min(Reset_Events.RE_ReadyDate) AS MinOfRE_ReadyDate, Reset_Events.RE_ResetEndDate, Sum(Reset_Plan_Viewer.On_Hand) AS SumOfOn_Hand, Sum(Reset_Plan_Viewer.In_Plan) AS SumOfIn_Plan, Sum(Reset_Plan_Viewer.Qty_Executed) AS SumOfQty_Executed, Sum(Reset_Plan_Viewer.Qty_Disposition_Provided) AS SumOfQty_Disposition_Provided, Sum(Reset_Plan_Viewer.Turned_In_Qty_PBUSE) AS SumOfTurned_In_Qty_PBUSE, Sum(Reset_Plan_Viewer.Turned_In_Qty_AWRDS) AS SumOfTurned_In_Qty_AWRDS, Sum(Reset_Plan_Viewer.Susp_Rej) AS SumOfSusp_Rej
FROM Reset_Plan_Viewer INNER JOIN Reset_Events ON Reset_Plan_Viewer.ARMT_Plan_Name = Reset_Events.RE_UIC
GROUP BY Reset_Events.RE_UIC, Reset_Plan_Viewer.LIN, Reset_Events.RE_ResetEndDate;

Then union Query I created works fine if I don't add the other fields. When I try to add the additional fields I get an error message that the fields in the to select statements don't match.

SELECT GeneratorTotals.RE_ResetEndDate, GeneratorTotals.RE_UIC, GeneratorTotals.On_Hand AS On_Hand_Qty, GeneratorTotals.In_Plan AS In_Plan_Qty, GeneratorTotals.Qty_Executed AS Qty_Exec, GeneratorTotals.Qty_Disposition_Provided AS Qty_DispositionProvided, GeneratorTotals.Turned_In_Qty_PBUSE AS PBUSE_Qty_TurnedIn, GeneratorTotals.Turned_In_Qty_AWRDS AS AWRDS_Qty_TurnedIn, GeneratorTotals.Susp_Rej AS SuspendedRejected_Qty
FROM GeneratorTotals
UNION SELECT GeneratorTotals.RE_ResetEndDate, "Month Totals" AS GenTotals, Sum(GeneratorTotals.On_Hand) AS On_Hand_Qty, Sum(GeneratorTotals.In_Plan) AS In_Plan_Qty, Sum(GeneratorTotals.Qty_Executed) AS Qty_Exec, Sum(GeneratorTotals.Qty_Disposition_Provided) AS Qty_DispositionProvided, Sum(GeneratorTotals.Turned_In_Qty_PBUSE) AS PBUSE_Qty_TurnedIn, Sum(GeneratorTotals.Turned_In_Qty_AWRDS) AS AWRDS_Qty_TurnedIn, Sum(GeneratorTotals.Susp_Rej) AS SuspendedRejected_Qty
FROM GeneratorTotals
GROUP BY GeneratorTotals.RE_ResetEndDate;

v/r,
James Eggert






From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, October 24, 2012 2:39 PM
Subject: RE: [MS_AccessPros] Need help with Subtotal Query







Yes, you can send directly to me and I can share.

Duane


> To: mailto:MS_Access_Professionals%40yahoogroups.com
> From: mailto:no_reply%40yahoogroups.com
> Date: Wed, 24 Oct 2012 18:29:02 +0000
> Subject: Re: [MS_AccessPros] Need help with Subtotal Query
>
> Duane,
>
> I am at work and when I try to send a response to you with the SQL code the site crashes. Would it be alright if I respond through the Reply to Sender instead of Reply via web post?
>
> v/r,
> James Eggert
>
> --- In mailto:MS_Access_Professionals%40yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
> >
> > James,
> > The start of a "best approach" would be to:
> > 1) suggest why a datasheet must be used rather than a report
> > 2) provide your SQL views
> >
> > Duane Hookom
> > MS Access MVP
> >
> >
> > > To: mailto:MS_Access_Professionals%40yahoogroups.com
> > > From: mailto:no_reply%40yahoogroups.com
> > > Date: Wed, 24 Oct 2012 17:29:22 +0000
> > > Subject: [MS_AccessPros] Need help with
Subtotal Query
> > >
> > > I have a requirement to analyze data by displaying a subtotal for 7 qty fields per month based on a date field (mm/yy). The subtotal row has to display at the bottom of each month in the datasheet view. I tried a Union Query, but I have to group the data off of three different fields which causes problems with the Union Query. Not sure what would be the best approach. Any help will be greatly appreciated!
> > >
> > > v/r,
> > > James Eggert
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>


[Non-text portions of this message have been removed]








[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/