A vs B query?

J

jlillge

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
 
J

John Spencer

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
..
 
J

jlillge

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;
 
D

Dale Fye

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 said:
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 said:
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
..
Click to expand...
Click to expand...
 
J

jlillge

Thanks Dale! I appreciate all your tips and I will put them all to use!
--
Jessa Lillge


Dale Fye said:
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 said:
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 said:
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
..

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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If you need to keep the current table structure, the following might work
depending on whether or not Oper Code defines whether something is Direct or
Indirect

SELECT Shift
, SUM (IIF( [Oper Code]<900 And [Oper Code] Not IN (109, 129,219,549)
,[Labor Time],Null) as Direct
, SUM (IIF( [Oper Code]<900 And [Oper Code] Not IN (109, 129,219,549)
,NULL,[Labor Time]) as InDirect
FROM [2005 2006 Hours]
WHERE [Branch (Site)] in ("21","76")
GROUP BY

Dale's solution is much better if you can implement it.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jlillge said:
Thanks Dale! I appreciate all your tips and I will put them all to use!
--
Jessa Lillge


Dale Fye said:
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 said:
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


:

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
..

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
Work Budget Salary vs Real Time Salary 0
Word docs emailed to me are opening blank (greyed screen) in Print Mode 0
Outlook meeting location auto prompts with a suggestion list - need to disable 0
Inverse Crosstab Query 3
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Query set-up not using cross-tab 9
How to remove a bracket that is itallicised for an entire document 1

Top