Re: A vs B query?

Jlillge,

I think I would probably create another table (tbl_Charge_Codes) that
contains the fields (Branch_Site_ID, Oper_Code, and Charge_Type) where
Charge_Type contains either "Direct" or "Indirect". Then, you could join
this table to your hours table on Branch_Site_ID and Oper_Code, and then you
could include the Charge_Type field in your query, something like:

Select H.Shift, C.Charge_Type, Sum(H.[Labor Time]) as SumOfLaborTime
FROM [2005 2006 Hours] H
INNER JOIN tbl_Charge_Codes C
ON H.[Branch (Site)] = C.Branch_Site_ID
AND H.[Oper Code] = C.Oper_Code
GROUP BY H.Shift, C.Charge_Type

You could convert this to a Crosstab query and include the Charge_Type as
the Column header, Shift as a Row header, and [Labor_Time] as a Value
(summed).

BTW, you might want to consider eliminating spaces from your field names.
When you use spaces, you are forced to encapsulate your field names in
brackets [], which can make reading the SQL very tedious.

Another suggestion is that you use aliases for your table names in the
query. This will make your SQL significantly shorter, and generally more
legible.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


"jlillge" wrote:

My SQL to get me the sum of all Direct (Oper Code) hours by shift:

SELECT [2005 2006 Hours].Shift, Sum([2005 2006 Hours].[Labor Time]) AS
[SumOfLabor Time]
FROM [2005 2006 Hours]
WHERE ((([2005 2006 Hours].[Branch (Site)])="21" Or ([2005 2006
Hours].[Branch (Site)])="76") AND (([2005 2006 Hours].[Oper Code])<900 And
([2005 2006 Hours].[Oper Code]) Not Like 109 And ([2005 2006 Hours].[Oper
Code]) Not Like 129 And ([2005 2006 Hours].[Oper Code]) Not Like 219 And
([2005 2006 Hours].[Oper Code]) Not Like 549))
GROUP BY [2005 2006 Hours].Shift;

--
Jessa Lillge


"John Spencer" wrote:

It would help if you posted the SQL of your query.

Simple example with generic names

SELECT Shift
, Sum(IIF(HourType="Direct",Hours,Null)) as DirectHours
, Sum(IIF(HourType="InDirect",Hours,Null)) as InDirectHours
FROM YourTable
GROUP BY Shift

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"jlillge" <jlillge@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5BF992B2-C6D1-40A2-82A7-0D51C59FC5F0@xxxxxxxxxxxxxxxx
I'm not even sure if a query is the right way to go about finding my
answer,
so I could use some direction.

I have one table with about 10 columns and around 100,000 rows of data.
The
data covers hours worked by employees, what shift, what machine, and what
department, and whether the hours were direct or indirect to product
manufacturing.

What I want to do is create some "sums" to compare the hours that were
direct vs. indirect in each of the categories. I am able to create, for
example, a query that displays all the hours per shift that were direct
(by
telling the query to remove the codes for indirect work from the results
data). But is there a way to include another row that will display the
opposite of that data - only the indirect codes without the direct ones?
Or
do I have to have two separate queries for each sum/total? Is there any
way
to get the direct and indirect amounts to display side-by-side, such as in
a
report or something?

Thanks,
Jessa
--
Jessa Lillge



.

Relevant Pages

  • Re: A vs B query?
    ... Jessa Lillge ... You could convert this to a Crosstab query and include the Charge_Type as ... the Column header, Shift as a Row header, and as a Value ... direct vs. indirect in each of the categories. ...
    (microsoft.public.access.queries)
  • Undefined value error
    ... with the following code I can successfully query a datasource and ... display the results in html. ... successfully defined and the query works. ... $office_code = shift; ...
    (perl.dbi.users)
  • Re: A vs B query?
    ... ,NULL,) as InDirect ... You could convert this to a Crosstab query and include the Charge_Type as ... the Column header, Shift as a Row header, and as a Value ... opposite of that data - only the indirect codes without the direct ...
    (microsoft.public.access.queries)
  • Re: A vs B query?
    ... It would help if you posted the SQL of your query. ... GROUP BY Shift ... direct vs. indirect in each of the categories. ... But is there a way to include another row that will display the ...
    (microsoft.public.access.queries)
  • Re: Monthly Totals Query
    ... The shifts have to come from somewhere, so you would need a table of shifts, and a query that outer-joins this to your existing table. ... If you don't already have one, create a table of the valid shift numbers. ... Turn this into into a crosstab query. ... In the Crosstab row, choose Row Heading. ...
    (microsoft.public.access.queries)