Sorting and calculating within different fields

[Michele E]

I have a database in which production data is entered. There is a field for
the [Rolls Produced] which is the total number of rolls of product we made
during a given shift. There is also an [Off Quality] field which gives the
total number of "bad" (not good quality) rolls that were made. I have 4 other
field which list the top number 1, 2, 3 and 4 reasons for the off quality
rolls. These fields are drop down list that the data entry person can choose
from. I aslo have 4 more fields to input the [Amount] of number of rolls
associated with each [Reason].

I need to report a summary that will give me the top reasons (and amounts)
for a given time period. I have created a query and a form (to input the
start and end date), but I do not know how to tell the report (or query) how
to sort the data by [Reason] and then add up the [Amounts] for each reason,
when there are 4 different [Reason] fields and 4 dfferent [Amount] fields.

My data looks like this:
Total Rolls Off Quality Reason 1 Amount 1 Reason 2 Amount 2
Reason 3.....
100 25 bad egdes 15 wrong size 10
98 12 wrong size 10 dirty
2

In many cases, the top reason (#1) will not always be the same, so I need to
add different field names together, based on the [Reason] for the off quality
rolls.

Can you help me?
Thanks.

 

[Jerry Whittle]

The root problem is the table design. When you find yourself going across
with similar fields, such as your 4 other fields, you are treating Access
like a spreadsheet.

Simple question: What happens when someone demands that you track 5 things
instead of 4? You have to modify your table the queries, forms, and reports
based on that table. Then they want 6 instead of 5.....

What you need is an "Off Quality" table linked to the original. It it you
would have fields like

OQ_ID (primary key)
Rolls_ID (foriegn key to the original table)
OQ_Amount (number)
OQ_Reason (text)

You might even what a lookup table or list for the OQ_Reason field that
lists the common reasons in a combo or list box.

With the above you can has as few or as many OQ's per Roll as needed. You
also won't need an [Off Quality] field in the original table as you can just
count them up in the second table.

Next you can link these tables in a query to get the data that you need. To
get the particular format like below, you'll then need a crosstab query.

Easy to design and set up? Nope!

Give you the information that you need once set up properly plus be
expandable? Yep!

 

[Daryl S]

Michele -

This problem could have been prevented with a better database design (e.g.
each reason and count having it's own line in a separate table linked to the
shift. Anyway, given what you have, you can do this. I assume you have a
table of Reasons that is used for the drop-list. You will need to change the
names of the tables and fields to match your data. This basically makes a
cartesian query, then sums the amounts from all four fields for each reason:

SELECT Reasons.Reason, sum(Iif([ShiftRolls].[Reason 1] =
Reasons.Reason,[Amount 1],0) + sum(Iif([ShiftRolls].[Reason 2] =
Reasons.Reason,[Amount 2],0) + Iif([ShiftRolls].[Reason 3] =
Reasons.Reason,[Amount 3],0) + sum(Iif([ShiftRolls].[Reason 4] =
Reasons.Reason,[Amount 42],0))
FROM Reasons, ShiftRolls
WHERE ShiftRolls.ShiftDate Between [Enter Start Date:] AND [Enter End Date:]
GROUP BY Reasons.Reason;

 

[Michele E]

Hi Daryl.

Thank you. What does your [ShiftRolls] represent? The number of off quality
rolls?
I am new to Access. Would developing a crosstab query be too difficult - do
you think?
--
Michele E

Michele -

This problem could have been prevented with a better database design (e.g.
each reason and count having it's own line in a separate table linked to the
shift. Anyway, given what you have, you can do this. I assume you have a
table of Reasons that is used for the drop-list. You will need to change the
names of the tables and fields to match your data. This basically makes a
cartesian query, then sums the amounts from all four fields for each reason:

SELECT Reasons.Reason, sum(Iif([ShiftRolls].[Reason 1] =
Reasons.Reason,[Amount 1],0) + sum(Iif([ShiftRolls].[Reason 2] =
Reasons.Reason,[Amount 2],0) + Iif([ShiftRolls].[Reason 3] =
Reasons.Reason,[Amount 3],0) + sum(Iif([ShiftRolls].[Reason 4] =
Reasons.Reason,[Amount 42],0))
FROM Reasons, ShiftRolls
WHERE ShiftRolls.ShiftDate Between [Enter Start Date:] AND [Enter End Date:]
GROUP BY Reasons.Reason;

--
Daryl S

I have a database in which production data is entered. There is a field for
the [Rolls Produced] which is the total number of rolls of product we made
during a given shift. There is also an [Off Quality] field which gives the
total number of "bad" (not good quality) rolls that were made. I have 4 other
field which list the top number 1, 2, 3 and 4 reasons for the off quality
rolls. These fields are drop down list that the data entry person can choose
from. I aslo have 4 more fields to input the [Amount] of number of rolls
associated with each [Reason].

I need to report a summary that will give me the top reasons (and amounts)
for a given time period. I have created a query and a form (to input the
start and end date), but I do not know how to tell the report (or query) how
to sort the data by [Reason] and then add up the [Amounts] for each reason,
when there are 4 different [Reason] fields and 4 dfferent [Amount] fields.

My data looks like this:
Total Rolls Off Quality Reason 1 Amount 1 Reason 2 Amount 2
Reason 3.....
100 25 bad egdes 15 wrong size 10
98 12 wrong size 10 dirty
2

In many cases, the top reason (#1) will not always be the same, so I need to
add different field names together, based on the [Reason] for the off quality
rolls.

Can you help me?
Thanks.

 

[Daryl S]

Michele -

The [ShiftRolls] should be the table where you have the number of off
quality rolls stored.

A crosstab query would be perfect if your table design was the way I
mentioned it in the previous post. They are easy to build if the table is
set up properly.

Try the query, and if you have any issues, post the SQL to what you are
working on, and we can try to help more.

--
Daryl S

Hi Daryl.

Thank you. What does your [ShiftRolls] represent? The number of off quality
rolls?
I am new to Access. Would developing a crosstab query be too difficult - do
you think?
--
Michele E

Michele -

This problem could have been prevented with a better database design (e.g.
each reason and count having it's own line in a separate table linked to the
shift. Anyway, given what you have, you can do this. I assume you have a
table of Reasons that is used for the drop-list. You will need to change the
names of the tables and fields to match your data. This basically makes a
cartesian query, then sums the amounts from all four fields for each reason:

SELECT Reasons.Reason, sum(Iif([ShiftRolls].[Reason 1] =
Reasons.Reason,[Amount 1],0) + sum(Iif([ShiftRolls].[Reason 2] =
Reasons.Reason,[Amount 2],0) + Iif([ShiftRolls].[Reason 3] =
Reasons.Reason,[Amount 3],0) + sum(Iif([ShiftRolls].[Reason 4] =
Reasons.Reason,[Amount 42],0))
FROM Reasons, ShiftRolls
WHERE ShiftRolls.ShiftDate Between [Enter Start Date:] AND [Enter End Date:]
GROUP BY Reasons.Reason;

--
Daryl S

I have a database in which production data is entered. There is a field for
the [Rolls Produced] which is the total number of rolls of product we made
during a given shift. There is also an [Off Quality] field which gives the
total number of "bad" (not good quality) rolls that were made. I have 4 other
field which list the top number 1, 2, 3 and 4 reasons for the off quality
rolls. These fields are drop down list that the data entry person can choose
from. I aslo have 4 more fields to input the [Amount] of number of rolls
associated with each [Reason].

I need to report a summary that will give me the top reasons (and amounts)
for a given time period. I have created a query and a form (to input the
start and end date), but I do not know how to tell the report (or query) how
to sort the data by [Reason] and then add up the [Amounts] for each reason,
when there are 4 different [Reason] fields and 4 dfferent [Amount] fields.

My data looks like this:
Total Rolls Off Quality Reason 1 Amount 1 Reason 2 Amount 2
Reason 3.....
100 25 bad egdes 15 wrong size 10
98 12 wrong size 10 dirty
2

In many cases, the top reason (#1) will not always be the same, so I need to
add different field names together, based on the [Reason] for the off quality
rolls.

Can you help me?
Thanks.

 

[James A. Fortune]

Nevertheless, the data can be
tortured into confessing by means of a UNION ALL operation, e.g.

Duke: Put them in the iron maiden.

Bill & Ted: Iron Maiden? Excellent!!!

-- Bill and Ted's Excellent Adventure

James A. Fortune
(e-mail address removed)

[L. A. Waddell] is a wild ride, and a wild read.

 

[Michele E]

Thank you, everyone. I ended up making another table and linking them. I
believe this should work well.

--
Michele E

Nevertheless, the data can be
tortured into confessing by means of a UNION ALL operation, e.g.

Duke: Put them in the iron maiden.

Bill & Ted: Iron Maiden? Excellent!!!

-- Bill and Ted's Excellent Adventure

James A. Fortune
(e-mail address removed)

[L. A. Waddell] is a wild ride, and a wild read.
.

 

[De Jager]

Thank you, everyone. I ended up making another table and linking them. I
believe this should work well.

--
Michele E

Nevertheless, the
data can be
tortured into confessing by means of a UNION ALL operation, e.g.

Duke: Put them in the iron maiden.

Bill & Ted: Iron Maiden? Excellent!!!

-- Bill and Ted's Excellent Adventure

James A. Fortune
(e-mail address removed)

[L. A. Waddell] is a wild ride, and a wild read.
.

 

[joelgeraldine]

kjjkjkj

Thank you, everyone. I ended up making another table and linking them. I
believe this should work well.

--
Michele E

Nevertheless, the
data can be
tortured into confessing by means of a UNION ALL operation, e.g.

Duke: Put them in the iron maiden.

Bill & Ted: Iron Maiden? Excellent!!!

-- Bill and Ted's Excellent Adventure

James A. Fortune
(e-mail address removed)

[L. A. Waddell] is a wild ride, and a wild read.
.