Unwanted Prompts

[Jamie Dickerson]

Disclaimer...I am an ACCESS NOVICE. I am completely self-taught (using
several Dummies books and Microsofts online training.

I have one table that records reject rates for hundreds of items. I have
several Queries that sort these records and a few queries that I use to
calculate fields and consolidate multiple records into one. All of my
calulcation queries prompt me to enter values for the fields that contain the
sums. If I press enter and leave it blank the query runs and caluclates as
it should. Why am I being prompted and how do I eliminate the prompts.

PS I found one way around the issue by turning the SUM queries into Make
Table Queries. But I am hoping for a better way to solve the problem. At
some point I will have to train someone to enter the data and run/print the
reports. I would like this database to be as userfriendly as possible.

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[Jamie Dickerson]

Jerry,

Here is the SQL statement. There are no primary keys set for this table.
As there are no unique records for this table. I use a few of the queries to
combine multiple entries into unique records based on Month, Quarter and
Year. As I stated this happens with all of my queries that perform
calculations. As far as relationships there is only 1 for the Reject Rates
table it is related to the Item#s table. I established this relationship so
that the Item description is automatically entered once the Item# is entered.
Here is the SQL from my most recent creation.

SELECT DISTINCTROW RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy') AS [Production Run By Year], RRbyWOSearch.[Work Order#],
Sum(RRbyWOSearch.Other) AS SumOfOther, Sum(RRbyWOSearch.[Lot Size]) AS [Sum
Of Lot Size], Sum(RRbyWOSearch.[Mold Marks]) AS [Sum Of Mold Marks],
Sum(RRbyWOSearch.Trim) AS [Sum Of Trim], Sum(RRbyWOSearch.Bubbles) AS [Sum Of
Bubbles], Sum(RRbyWOSearch.Wrinkles) AS [Sum Of Wrinkles],
Sum(RRbyWOSearch.[Foreign Matter]) AS [Sum Of Foreign Matter],
Sum(RRbyWOSearch.Shorts) AS [Sum Of Shorts], Sum(RRbyWOSearch.Flashing) AS
[Sum Of Flashing], Sum(RRbyWOSearch.[Bad Mix]) AS [Sum Of Bad Mix],
Sum(RRbyWOSearch.[Bad Material]) AS [Sum Of Bad Material],
Sum(RRbyWOSearch.[Heel Seal]) AS [Sum Of Heel Seal], Sum(RRbyWOSearch.[Edge
Seal]) AS [Sum Of Edge Seal], Sum(RRbyWOSearch.Pitting) AS [Sum Of Pitting],
Sum(RRbyWOSearch.[Tear Drops]) AS [Sum Of Tear Drops],
Sum(RRbyWOSearch.Sinks) AS [Sum Of Sinks], Sum(RRbyWOSearch.Crooked) AS [Sum
Of Crooked], Sum(RRbyWOSearch.Burns) AS [Sum Of Burns],
Sum(RRbyWOSearch.[Wrong Side]) AS [Sum Of Wrong Side],
Sum(RRbyWOSearch.Streaks) AS [Sum Of Streaks], Sum(RRbyWOSearch.Gates) AS
[Sum Of Gates], [Sum Of Mold Marks]+[Sum Of Trim]+[Sum Of Bubbles]+[Sum Of
Wrinkles]+[Sum Of Foreign Matter]+[Sum Of Shorts]+[Sum Of Flashing]+[Sum Of
Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel Seal]+[Sum Of Edge Seal]+[Sum Of
Pitting]+[Sum Of Tear Drops]+[Sum Of Sinks]+[Sum Of Crooked]+[Sum Of
Burns]+[Sum Of Wrong Side]+[Sum Of Streaks]+[Sum Of Gates]+[SumOfOther] AS
Totals, [Totals]/[Sum of Lot SIze] AS [Reject Percentage]
FROM RRbyWOSearch
GROUP BY RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy'), RRbyWOSearch.[Work Order#], [Sum Of Mold Marks]+[Sum Of
Trim]+[Sum Of Bubbles]+[Sum Of Wrinkles]+[Sum Of Foreign Matter]+[Sum Of
Shorts]+[Sum Of Flashing]+[Sum Of Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel
Seal]+[Sum Of Edge Seal]+[Sum Of Pitting]+[Sum Of Tear Drops]+[Sum Of
Sinks]+[Sum Of Crooked]+[Sum Of Burns]+[Sum Of Wrong Side]+[Sum Of
Streaks]+[Sum Of Gates]+[SumOfOther], [Totals]/[Sum of Lot SIze],
Year(RRbyWOSearch.[Production Run])
HAVING (((RRbyWOSearch.[Work Order#]) Like "WO*"));

 

[Jerry Whittle]

Ouch! My brain just exploded! ;-)

I can't see anything obvious except:

1. If you are missing a reject type, say Trim, for a record, that could be a
problem unless you have a default value of 0 set up.

2. Your table is set up wrong. You have it set up like a spreadsheet, and
not a database, with a new column for each type of reject. What you should
have is something like below:

Item# WO# ProductionRunDate NumRejects RejectType

111 WO4 1/1/2009 2 Shorts
111 WO4 1/1/2009 1 Trim
111 WO4 2/1/2009 1 Shorts

You should have a table of RejectTypes to pick from so that there aren't any
misspellings.

Then the query would look something like this:
Select [Item#],
[WO#],
Year(ProductionRunDate),
Sum(NumRejects) AS NumRejectsSum
FROM RRbyWOSearch
GROUP BY [Item#],
[WO#],
Year(ProductionRunDate)
HAVING RRbyWOSearch.[WO#]) Like "WO*";

As you can see it would be much simpler and you wouldn't have to worry about
adding a new RejectType in the future.

If you wanted to also break it down by RejectType:

Select [Item#],
[WO#],
Year(ProductionRunDate),
[RejectType],
Sum(NumRejects) AS NumRejectsSum
FROM RRbyWOSearch
GROUP BY [Item#],
[WO#],
Year(ProductionRunDate),
[RejectType]
HAVING RRbyWOSearch.[WO#]) Like "WO*";

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

Here is the SQL statement. There are no primary keys set for this table.
As there are no unique records for this table. I use a few of the queries to
combine multiple entries into unique records based on Month, Quarter and
Year. As I stated this happens with all of my queries that perform
calculations. As far as relationships there is only 1 for the Reject Rates
table it is related to the Item#s table. I established this relationship so
that the Item description is automatically entered once the Item# is entered.
Here is the SQL from my most recent creation.

SELECT DISTINCTROW RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy') AS [Production Run By Year], RRbyWOSearch.[Work Order#],
Sum(RRbyWOSearch.Other) AS SumOfOther, Sum(RRbyWOSearch.[Lot Size]) AS [Sum
Of Lot Size], Sum(RRbyWOSearch.[Mold Marks]) AS [Sum Of Mold Marks],
Sum(RRbyWOSearch.Trim) AS [Sum Of Trim], Sum(RRbyWOSearch.Bubbles) AS [Sum Of
Bubbles], Sum(RRbyWOSearch.Wrinkles) AS [Sum Of Wrinkles],
Sum(RRbyWOSearch.[Foreign Matter]) AS [Sum Of Foreign Matter],
Sum(RRbyWOSearch.Shorts) AS [Sum Of Shorts], Sum(RRbyWOSearch.Flashing) AS
[Sum Of Flashing], Sum(RRbyWOSearch.[Bad Mix]) AS [Sum Of Bad Mix],
Sum(RRbyWOSearch.[Bad Material]) AS [Sum Of Bad Material],
Sum(RRbyWOSearch.[Heel Seal]) AS [Sum Of Heel Seal], Sum(RRbyWOSearch.[Edge
Seal]) AS [Sum Of Edge Seal], Sum(RRbyWOSearch.Pitting) AS [Sum Of Pitting],
Sum(RRbyWOSearch.[Tear Drops]) AS [Sum Of Tear Drops],
Sum(RRbyWOSearch.Sinks) AS [Sum Of Sinks], Sum(RRbyWOSearch.Crooked) AS [Sum
Of Crooked], Sum(RRbyWOSearch.Burns) AS [Sum Of Burns],
Sum(RRbyWOSearch.[Wrong Side]) AS [Sum Of Wrong Side],
Sum(RRbyWOSearch.Streaks) AS [Sum Of Streaks], Sum(RRbyWOSearch.Gates) AS
[Sum Of Gates], [Sum Of Mold Marks]+[Sum Of Trim]+[Sum Of Bubbles]+[Sum Of
Wrinkles]+[Sum Of Foreign Matter]+[Sum Of Shorts]+[Sum Of Flashing]+[Sum Of
Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel Seal]+[Sum Of Edge Seal]+[Sum Of
Pitting]+[Sum Of Tear Drops]+[Sum Of Sinks]+[Sum Of Crooked]+[Sum Of
Burns]+[Sum Of Wrong Side]+[Sum Of Streaks]+[Sum Of Gates]+[SumOfOther] AS
Totals, [Totals]/[Sum of Lot SIze] AS [Reject Percentage]
FROM RRbyWOSearch
GROUP BY RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy'), RRbyWOSearch.[Work Order#], [Sum Of Mold Marks]+[Sum Of
Trim]+[Sum Of Bubbles]+[Sum Of Wrinkles]+[Sum Of Foreign Matter]+[Sum Of
Shorts]+[Sum Of Flashing]+[Sum Of Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel
Seal]+[Sum Of Edge Seal]+[Sum Of Pitting]+[Sum Of Tear Drops]+[Sum Of
Sinks]+[Sum Of Crooked]+[Sum Of Burns]+[Sum Of Wrong Side]+[Sum Of
Streaks]+[Sum Of Gates]+[SumOfOther], [Totals]/[Sum of Lot SIze],
Year(RRbyWOSearch.[Production Run])
HAVING (((RRbyWOSearch.[Work Order#]) Like "WO*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

 

[Jamie Dickerson]

Sorry for the explosion. If you like I will help clean up the brain matter.

Thanks for the help Jerry.

The table is set up like a spreadsheet because that is what I am used to
working with. I think changing the table at this point may be more trouble
than its worth. But I will keep this in mind when we start our next fiscal
quarter.

I have checked to ensure that their are no blank fields. And 0 is the
default value for all. I will continue to use the Make Table Query and base
the report off of this table as this will eliminate the prompts.

Ouch! My brain just exploded! ;-)

I can't see anything obvious except:

1. If you are missing a reject type, say Trim, for a record, that could be a
problem unless you have a default value of 0 set up.

2. Your table is set up wrong. You have it set up like a spreadsheet, and
not a database, with a new column for each type of reject. What you should
have is something like below:

Item# WO# ProductionRunDate NumRejects RejectType

111 WO4 1/1/2009 2 Shorts
111 WO4 1/1/2009 1 Trim
111 WO4 2/1/2009 1 Shorts

You should have a table of RejectTypes to pick from so that there aren't any
misspellings.

Then the query would look something like this:
Select [Item#],
[WO#],
Year(ProductionRunDate),
Sum(NumRejects) AS NumRejectsSum
FROM RRbyWOSearch
GROUP BY [Item#],
[WO#],
Year(ProductionRunDate)
HAVING RRbyWOSearch.[WO#]) Like "WO*";

As you can see it would be much simpler and you wouldn't have to worry about
adding a new RejectType in the future.

If you wanted to also break it down by RejectType:

Select [Item#],
[WO#],
Year(ProductionRunDate),
[RejectType],
Sum(NumRejects) AS NumRejectsSum
FROM RRbyWOSearch
GROUP BY [Item#],
[WO#],
Year(ProductionRunDate),
[RejectType]
HAVING RRbyWOSearch.[WO#]) Like "WO*";

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

Here is the SQL statement. There are no primary keys set for this table.
As there are no unique records for this table. I use a few of the queries to
combine multiple entries into unique records based on Month, Quarter and
Year. As I stated this happens with all of my queries that perform
calculations. As far as relationships there is only 1 for the Reject Rates
table it is related to the Item#s table. I established this relationship so
that the Item description is automatically entered once the Item# is entered.
Here is the SQL from my most recent creation.

SELECT DISTINCTROW RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy') AS [Production Run By Year], RRbyWOSearch.[Work Order#],
Sum(RRbyWOSearch.Other) AS SumOfOther, Sum(RRbyWOSearch.[Lot Size]) AS [Sum
Of Lot Size], Sum(RRbyWOSearch.[Mold Marks]) AS [Sum Of Mold Marks],
Sum(RRbyWOSearch.Trim) AS [Sum Of Trim], Sum(RRbyWOSearch.Bubbles) AS [Sum Of
Bubbles], Sum(RRbyWOSearch.Wrinkles) AS [Sum Of Wrinkles],
Sum(RRbyWOSearch.[Foreign Matter]) AS [Sum Of Foreign Matter],
Sum(RRbyWOSearch.Shorts) AS [Sum Of Shorts], Sum(RRbyWOSearch.Flashing) AS
[Sum Of Flashing], Sum(RRbyWOSearch.[Bad Mix]) AS [Sum Of Bad Mix],
Sum(RRbyWOSearch.[Bad Material]) AS [Sum Of Bad Material],
Sum(RRbyWOSearch.[Heel Seal]) AS [Sum Of Heel Seal], Sum(RRbyWOSearch.[Edge
Seal]) AS [Sum Of Edge Seal], Sum(RRbyWOSearch.Pitting) AS [Sum Of Pitting],
Sum(RRbyWOSearch.[Tear Drops]) AS [Sum Of Tear Drops],
Sum(RRbyWOSearch.Sinks) AS [Sum Of Sinks], Sum(RRbyWOSearch.Crooked) AS [Sum
Of Crooked], Sum(RRbyWOSearch.Burns) AS [Sum Of Burns],
Sum(RRbyWOSearch.[Wrong Side]) AS [Sum Of Wrong Side],
Sum(RRbyWOSearch.Streaks) AS [Sum Of Streaks], Sum(RRbyWOSearch.Gates) AS
[Sum Of Gates], [Sum Of Mold Marks]+[Sum Of Trim]+[Sum Of Bubbles]+[Sum Of
Wrinkles]+[Sum Of Foreign Matter]+[Sum Of Shorts]+[Sum Of Flashing]+[Sum Of
Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel Seal]+[Sum Of Edge Seal]+[Sum Of
Pitting]+[Sum Of Tear Drops]+[Sum Of Sinks]+[Sum Of Crooked]+[Sum Of
Burns]+[Sum Of Wrong Side]+[Sum Of Streaks]+[Sum Of Gates]+[SumOfOther] AS
Totals, [Totals]/[Sum of Lot SIze] AS [Reject Percentage]
FROM RRbyWOSearch
GROUP BY RRbyWOSearch.[Item#], Format$(RRbyWOSearch.[Production
Run],'yyyy'), RRbyWOSearch.[Work Order#], [Sum Of Mold Marks]+[Sum Of
Trim]+[Sum Of Bubbles]+[Sum Of Wrinkles]+[Sum Of Foreign Matter]+[Sum Of
Shorts]+[Sum Of Flashing]+[Sum Of Bad Mix]+[Sum Of Bad Material]+[Sum Of Heel
Seal]+[Sum Of Edge Seal]+[Sum Of Pitting]+[Sum Of Tear Drops]+[Sum Of
Sinks]+[Sum Of Crooked]+[Sum Of Burns]+[Sum Of Wrong Side]+[Sum Of
Streaks]+[Sum Of Gates]+[SumOfOther], [Totals]/[Sum of Lot SIze],
Year(RRbyWOSearch.[Production Run])
HAVING (((RRbyWOSearch.[Work Order#]) Like "WO*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Disclaimer...I am an ACCESS NOVICE. I am completely self-taught (using
several Dummies books and Microsofts online training.

I have one table that records reject rates for hundreds of items. I have
several Queries that sort these records and a few queries that I use to
calculate fields and consolidate multiple records into one. All of my
calulcation queries prompt me to enter values for the fields that contain the
sums. If I press enter and leave it blank the query runs and caluclates as
it should. Why am I being prompted and how do I eliminate the prompts.

PS I found one way around the issue by turning the SUM queries into Make
Table Queries. But I am hoping for a better way to solve the problem. At
some point I will have to train someone to enter the data and run/print the
reports. I would like this database to be as userfriendly as possible.