date

[Steven P via AccessMonster.com]

Hello,
Is there a criteria I can enter for the date column to eliminate all dates
from showing? This query is used for a chart in a report, the chart is
showing multiple bars for the same machine instead of summing the totals. I
have determined that the date reference is the culprit. I need the date, as
the operator will specify beginning and ending time frame for the report
results.
Thanks,
Steve

 

[Tom Ellison]

Dear Steven:

You may be meaning you do not wish to display the data, but still filter on
it. If so, just uncheck the "show" check box in the query design.

If you want to select only rows that do not have a date value assigned,
please explain.

If you need further help, please copy and paste the SQL View of your query
in here. This is a text version of your query we can read, modify, and send
back to you. That makes it work well in a newsgroup.

Tom Ellison

 

[Steven P via AccessMonster.com]

Tom,
Thanks. Atached is the SQL. Here is the problem- The dates the data is
entered are different therefore I have multiple query results for the same
machine. When I graph the results for each machine I get several columns for
a machine instead of just the one graph I need.
SELECT INPUTS.MACHINE, [KYON MACHINE AND WORKCENTER].WORKCENTER, INPUTS.
[TODAYS DATE], INPUTS.EmployeeID, Avg(INPUTS.GOAL) AS AvgOfGOAL, Sum(INPUTS.
[AVAILABLE HOURS]) AS [SumOfAVAILABLE HOURS], Sum(INPUTS.[GOOD PIECES]) AS
[SumOfGOOD PIECES], Sum(INPUTS.[DIMENSIONAL SCRAP]) AS [SumOfDIMENSIONAL
SCRAP], Sum(INPUTS.METALLURGICAL) AS SumOfMETALLURGICAL, Sum([DIMENSIONAL
SCRAP]+[METALLURGICAL]) AS [TOTAL SCRAP], Sum([TPM HRS]+[CLEANING HRS]+
[MEETING HOURS]+[LEAN HOURS]) AS [SCHEDULED DOWNTIME], Sum([MAINTENANCE HOURS]
+[WHEEL CHANGE HOURS]+[SETUP HOURS]+[WAITING SETUP HOURS]) AS [UNSCHEDULED
DOWNTIME], Sum([TPM HRS]+[CLEANING HRS]+[MEETING HOURS]+[LEAN HOURS]+
[MAINTENANCE HOURS]+[WHEEL CHANGE HOURS]+[SETUP HOURS]+[WAITING SETUP HOURS])
AS [TOTAL DOWNTIME], Sum(([AVAILABLE HOURS])-([TPM HRS]+[CLEANING HRS]+
[MEETING HOURS]+[LEAN HOURS]+[MAINTENANCE HOURS]+[WHEEL CHANGE HOURS]+[SETUP
HOURS]+[WAITING SETUP HOURS])) AS [RUN HOURS], INPUTS.[SETUP HOURS], INPUTS.
[# SETUPS PERFORMED]
FROM INPUTS INNER JOIN [KYON MACHINE AND WORKCENTER] ON INPUTS.MACHINE =
[KYON MACHINE AND WORKCENTER].MACHINE
GROUP BY INPUTS.MACHINE, [KYON MACHINE AND WORKCENTER].WORKCENTER, INPUTS.
[TODAYS DATE], INPUTS.EmployeeID, INPUTS.[SETUP HOURS], INPUTS.[# SETUPS
PERFORMED];

Dear Steven:

You may be meaning you do not wish to display the data, but still filter on
it. If so, just uncheck the "show" check box in the query design.

If you want to select only rows that do not have a date value assigned,
please explain.

If you need further help, please copy and paste the SQL View of your query
in here. This is a text version of your query we can read, modify, and send
back to you. That makes it work well in a newsgroup.

Tom Ellison

Hello,
Is there a criteria I can enter for the date column to eliminate all dates

[quoted text clipped - 7 lines]

Thanks,
Steve

 

[Tom Ellison]

Dear Steven:

Your query cannot show you dates and not show you a separate row for each
date it finds. The solution is not to show the date at all, as it is not
relevant to the chart you wish to produce.

If INPUTS.[TODAYS DATE] is the offending date it is showing, remove that
from the SELECT clause and from GROUP BY. Be sure this query only produces
the values for the axes of your chart or graph. A graph is a two
dimensional representation of two related quantities. You have very many
quantities in your query. This does not seem at all reasonable.

Are you charting a function, a simple domain and range? On what two columns
is this based? I expect your query needs to product only these two
quantities. If the query produces other values not GROUPed, they will
fragment the results as you have experienced. While you can select only
what columns you want to graph, doing so will not remove that fragmentation.
All the columns which you Sum, Count, Average, or on which you perform any
other aggregate are not going to create this problem. But, you must have
only one Domain (usually the horizontal axis), especially as this is an
aggregate query (contains a GROUP BY).

Tom Ellison

Tom,
Thanks. Atached is the SQL. Here is the problem- The dates the data is
entered are different therefore I have multiple query results for the same
machine. When I graph the results for each machine I get several columns
for
a machine instead of just the one graph I need.
SELECT INPUTS.MACHINE, [KYON MACHINE AND WORKCENTER].WORKCENTER, INPUTS.
[TODAYS DATE], INPUTS.EmployeeID, Avg(INPUTS.GOAL) AS AvgOfGOAL,
Sum(INPUTS.
[AVAILABLE HOURS]) AS [SumOfAVAILABLE HOURS], Sum(INPUTS.[GOOD PIECES]) AS
[SumOfGOOD PIECES], Sum(INPUTS.[DIMENSIONAL SCRAP]) AS [SumOfDIMENSIONAL
SCRAP], Sum(INPUTS.METALLURGICAL) AS SumOfMETALLURGICAL, Sum([DIMENSIONAL
SCRAP]+[METALLURGICAL]) AS [TOTAL SCRAP], Sum([TPM HRS]+[CLEANING HRS]+
[MEETING HOURS]+[LEAN HOURS]) AS [SCHEDULED DOWNTIME], Sum([MAINTENANCE
HOURS]
+[WHEEL CHANGE HOURS]+[SETUP HOURS]+[WAITING SETUP HOURS]) AS [UNSCHEDULED
DOWNTIME], Sum([TPM HRS]+[CLEANING HRS]+[MEETING HOURS]+[LEAN HOURS]+
[MAINTENANCE HOURS]+[WHEEL CHANGE HOURS]+[SETUP HOURS]+[WAITING SETUP
HOURS])
AS [TOTAL DOWNTIME], Sum(([AVAILABLE HOURS])-([TPM HRS]+[CLEANING HRS]+
[MEETING HOURS]+[LEAN HOURS]+[MAINTENANCE HOURS]+[WHEEL CHANGE
HOURS]+[SETUP
HOURS]+[WAITING SETUP HOURS])) AS [RUN HOURS], INPUTS.[SETUP HOURS],
INPUTS.
[# SETUPS PERFORMED]
FROM INPUTS INNER JOIN [KYON MACHINE AND WORKCENTER] ON INPUTS.MACHINE =
[KYON MACHINE AND WORKCENTER].MACHINE
GROUP BY INPUTS.MACHINE, [KYON MACHINE AND WORKCENTER].WORKCENTER, INPUTS.
[TODAYS DATE], INPUTS.EmployeeID, INPUTS.[SETUP HOURS], INPUTS.[# SETUPS
PERFORMED];

Dear Steven:

You may be meaning you do not wish to display the data, but still filter
on
it. If so, just uncheck the "show" check box in the query design.

If you want to select only rows that do not have a date value assigned,
please explain.

If you need further help, please copy and paste the SQL View of your query
in here. This is a text version of your query we can read, modify, and
send
back to you. That makes it work well in a newsgroup.

Tom Ellison

Hello,
Is there a criteria I can enter for the date column to eliminate all
dates

[quoted text clipped - 7 lines]

Thanks,
Steve