Help with criteria - counting based on text field

G

Guest

Hello,

I'm not very savvy with SQL and I'm having a lot of trouble with this one.

I have a database with work orders for several machines. A work order may be
of several categories, one of which is "Safety".
The field name = [WOType] and is a text field.
For each machine I need to count the number of open work orders of type =
"Safety" and the total number of orders that are NOT = "Safety". I can't do
it without getting type mismatch errors.

I want the output to be like this:

Machine Safety WO Other WO
123 6 14
234 4 18
345 2 5
456 9 20
......

There are only seven machines so a form or datasheet output would be fine.
Please help!
 
G

Guest

Try this
Table workOrders has fields machineNo and WOtype

Query called reclassWO reclassifies the WO type into safety and other

SELECT workOrders.machineNo, workOrders.WOtype,
IIf(workOrders!WOtype="Safety","Safety WO","Other WO") AS safety_WO
FROM workOrders;

Crosstab on reclassWO gives you what you want.

TRANSFORM Count(reclassWO.WOtype) AS CountOfWOtype
SELECT reclassWO.machineNo, Count(reclassWO.WOtype) AS [Total WO]
FROM reclassWO
GROUP BY reclassWO.machineNo
PIVOT reclassWO.safety_WO;
 
G

Guest

Thanks. This works great. Now I am trying to filter the records by a user
selected date. I have a form based on the crosstab query with an unbound
field named [EndDate] that defaults to the current date but can be changed by
the user. The intent is to see how many work orders were open per machine on
a given day (to track completion rates). I added an event procedure to the
EndDate field that I copied below. The problem is, the data doesn't change
when the date changes. It still shows what is oepn based on the default date
rather than the date that is entered. Any ideas?

The event procedure:

Private Sub EndDate_AfterUpdate()
Me.Requery
End Sub


Here is the crosstab query as it now stands:

PARAMETERS [Forms]![WorkOrderSummaryFrm]![EndDate] DateTime;
TRANSFORM Count(ReclassWOQRY.[W/O Type]) AS [CountOfW/O Type]
SELECT ReclassWOQRY.[Mach #], Count(ReclassWOQRY.[W/O Type]) AS [Total Of
W/O Type]
FROM ReclassWOQRY
WHERE (((ReclassWOQRY.Date)<[Forms]![WorkOrderSummaryFrm]![EndDate]) AND
((ReclassWOQRY.[Date Completed])>[Forms]![WorkOrderSummaryFrm]![EndDate])) OR
(((ReclassWOQRY.[Date Completed]) Is Null) AND ((ReclassWOQRY.Status)="O"))
GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

Bruce Meneghin said:
Try this
Table workOrders has fields machineNo and WOtype

Query called reclassWO reclassifies the WO type into safety and other

SELECT workOrders.machineNo, workOrders.WOtype,
IIf(workOrders!WOtype="Safety","Safety WO","Other WO") AS safety_WO
FROM workOrders;

Crosstab on reclassWO gives you what you want.

TRANSFORM Count(reclassWO.WOtype) AS CountOfWOtype
SELECT reclassWO.machineNo, Count(reclassWO.WOtype) AS [Total WO]
FROM reclassWO
GROUP BY reclassWO.machineNo
PIVOT reclassWO.safety_WO;


Rayo K said:
Hello,

I'm not very savvy with SQL and I'm having a lot of trouble with this one.

I have a database with work orders for several machines. A work order may be
of several categories, one of which is "Safety".
The field name = [WOType] and is a text field.
For each machine I need to count the number of open work orders of type =
"Safety" and the total number of orders that are NOT = "Safety". I can't do
it without getting type mismatch errors.

I want the output to be like this:

Machine Safety WO Other WO
123 6 14
234 4 18
345 2 5
456 9 20
.....

There are only seven machines so a form or datasheet output would be fine.
Please help!
Click to expand...
Click to expand...
 
G

Guest

I'm not that familiar with queries on forms. Here's a hint from VB Help on
..Requery:

"Use the Requery method to refresh the entire contents of a Recordset object
from the data source by reissuing the original command "...
"Thus, the Requery method can only refresh the current cursor. To change any
of the cursor properties and view the results, you must use the Close method
so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor."

Rayo K said:
Thanks. This works great. Now I am trying to filter the records by a user
selected date. I have a form based on the crosstab query with an unbound
field named [EndDate] that defaults to the current date but can be changed by
the user. The intent is to see how many work orders were open per machine on
a given day (to track completion rates). I added an event procedure to the
EndDate field that I copied below. The problem is, the data doesn't change
when the date changes. It still shows what is oepn based on the default date
rather than the date that is entered. Any ideas?

The event procedure:

Private Sub EndDate_AfterUpdate()
Me.Requery
End Sub


Here is the crosstab query as it now stands:

PARAMETERS [Forms]![WorkOrderSummaryFrm]![EndDate] DateTime;
TRANSFORM Count(ReclassWOQRY.[W/O Type]) AS [CountOfW/O Type]
SELECT ReclassWOQRY.[Mach #], Count(ReclassWOQRY.[W/O Type]) AS [Total Of
W/O Type]
FROM ReclassWOQRY
WHERE (((ReclassWOQRY.Date)<[Forms]![WorkOrderSummaryFrm]![EndDate]) AND
((ReclassWOQRY.[Date Completed])>[Forms]![WorkOrderSummaryFrm]![EndDate])) OR
(((ReclassWOQRY.[Date Completed]) Is Null) AND ((ReclassWOQRY.Status)="O"))
GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

Bruce Meneghin said:
Try this
Table workOrders has fields machineNo and WOtype

Query called reclassWO reclassifies the WO type into safety and other

SELECT workOrders.machineNo, workOrders.WOtype,
IIf(workOrders!WOtype="Safety","Safety WO","Other WO") AS safety_WO
FROM workOrders;

Crosstab on reclassWO gives you what you want.

TRANSFORM Count(reclassWO.WOtype) AS CountOfWOtype
SELECT reclassWO.machineNo, Count(reclassWO.WOtype) AS [Total WO]
FROM reclassWO
GROUP BY reclassWO.machineNo
PIVOT reclassWO.safety_WO;


Rayo K said:
Hello,

I'm not very savvy with SQL and I'm having a lot of trouble with this one.

I have a database with work orders for several machines. A work order may be
of several categories, one of which is "Safety".
The field name = [WOType] and is a text field.
For each machine I need to count the number of open work orders of type =
"Safety" and the total number of orders that are NOT = "Safety". I can't do
it without getting type mismatch errors.

I want the output to be like this:

Machine Safety WO Other WO
123 6 14
234 4 18
345 2 5
456 9 20
.....

There are only seven machines so a form or datasheet output would be fine.
Please help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That doesn't seem to help. The underlying query has a filter based on a
parameter that comes from the form's text box. If I change that value, then
shouldn't "Requery" run the query again with the new value?

Bruce Meneghin said:
I'm not that familiar with queries on forms. Here's a hint from VB Help on
.Requery:

"Use the Requery method to refresh the entire contents of a Recordset object
from the data source by reissuing the original command "...
"Thus, the Requery method can only refresh the current cursor. To change any
of the cursor properties and view the results, you must use the Close method
so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor."

Rayo K said:
Thanks. This works great. Now I am trying to filter the records by a user
selected date. I have a form based on the crosstab query with an unbound
field named [EndDate] that defaults to the current date but can be changed by
the user. The intent is to see how many work orders were open per machine on
a given day (to track completion rates). I added an event procedure to the
EndDate field that I copied below. The problem is, the data doesn't change
when the date changes. It still shows what is oepn based on the default date
rather than the date that is entered. Any ideas?

The event procedure:

Private Sub EndDate_AfterUpdate()
Me.Requery
End Sub


Here is the crosstab query as it now stands:

PARAMETERS [Forms]![WorkOrderSummaryFrm]![EndDate] DateTime;
TRANSFORM Count(ReclassWOQRY.[W/O Type]) AS [CountOfW/O Type]
SELECT ReclassWOQRY.[Mach #], Count(ReclassWOQRY.[W/O Type]) AS [Total Of
W/O Type]
FROM ReclassWOQRY
WHERE (((ReclassWOQRY.Date)<[Forms]![WorkOrderSummaryFrm]![EndDate]) AND
((ReclassWOQRY.[Date Completed])>[Forms]![WorkOrderSummaryFrm]![EndDate])) OR
(((ReclassWOQRY.[Date Completed]) Is Null) AND ((ReclassWOQRY.Status)="O"))
GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

Bruce Meneghin said:
Try this
Table workOrders has fields machineNo and WOtype

Query called reclassWO reclassifies the WO type into safety and other

SELECT workOrders.machineNo, workOrders.WOtype,
IIf(workOrders!WOtype="Safety","Safety WO","Other WO") AS safety_WO
FROM workOrders;

Crosstab on reclassWO gives you what you want.

TRANSFORM Count(reclassWO.WOtype) AS CountOfWOtype
SELECT reclassWO.machineNo, Count(reclassWO.WOtype) AS [Total WO]
FROM reclassWO
GROUP BY reclassWO.machineNo
PIVOT reclassWO.safety_WO;


:

Hello,

I'm not very savvy with SQL and I'm having a lot of trouble with this one.

I have a database with work orders for several machines. A work order may be
of several categories, one of which is "Safety".
The field name = [WOType] and is a text field.
For each machine I need to count the number of open work orders of type =
"Safety" and the total number of orders that are NOT = "Safety". I can't do
it without getting type mismatch errors.

I want the output to be like this:

Machine Safety WO Other WO
123 6 14
234 4 18
345 2 5
456 9 20
.....

There are only seven machines so a form or datasheet output would be fine.
Please help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

My interpretation of the help doc is that the Requery method allows a refresh
of the results, assuming the underlying data has changed. I'm guessing the
form creates the cursor, using the default date when it opens. The Requery
just executes the same query again. I suggest you make another post asking
about a query on a form with a parameter coming from a field on the form.

Rayo K said:
That doesn't seem to help. The underlying query has a filter based on a
parameter that comes from the form's text box. If I change that value, then
shouldn't "Requery" run the query again with the new value?

Bruce Meneghin said:
I'm not that familiar with queries on forms. Here's a hint from VB Help on
.Requery:

"Use the Requery method to refresh the entire contents of a Recordset object
from the data source by reissuing the original command "...
"Thus, the Requery method can only refresh the current cursor. To change any
of the cursor properties and view the results, you must use the Close method
so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor."

Rayo K said:
Thanks. This works great. Now I am trying to filter the records by a user
selected date. I have a form based on the crosstab query with an unbound
field named [EndDate] that defaults to the current date but can be changed by
the user. The intent is to see how many work orders were open per machine on
a given day (to track completion rates). I added an event procedure to the
EndDate field that I copied below. The problem is, the data doesn't change
when the date changes. It still shows what is oepn based on the default date
rather than the date that is entered. Any ideas?

The event procedure:

Private Sub EndDate_AfterUpdate()
Me.Requery
End Sub


Here is the crosstab query as it now stands:

PARAMETERS [Forms]![WorkOrderSummaryFrm]![EndDate] DateTime;
TRANSFORM Count(ReclassWOQRY.[W/O Type]) AS [CountOfW/O Type]
SELECT ReclassWOQRY.[Mach #], Count(ReclassWOQRY.[W/O Type]) AS [Total Of
W/O Type]
FROM ReclassWOQRY
WHERE (((ReclassWOQRY.Date)<[Forms]![WorkOrderSummaryFrm]![EndDate]) AND
((ReclassWOQRY.[Date Completed])>[Forms]![WorkOrderSummaryFrm]![EndDate])) OR
(((ReclassWOQRY.[Date Completed]) Is Null) AND ((ReclassWOQRY.Status)="O"))
GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

:

Try this
Table workOrders has fields machineNo and WOtype

Query called reclassWO reclassifies the WO type into safety and other

SELECT workOrders.machineNo, workOrders.WOtype,
IIf(workOrders!WOtype="Safety","Safety WO","Other WO") AS safety_WO
FROM workOrders;

Crosstab on reclassWO gives you what you want.

TRANSFORM Count(reclassWO.WOtype) AS CountOfWOtype
SELECT reclassWO.machineNo, Count(reclassWO.WOtype) AS [Total WO]
FROM reclassWO
GROUP BY reclassWO.machineNo
PIVOT reclassWO.safety_WO;


:

Hello,

I'm not very savvy with SQL and I'm having a lot of trouble with this one.

I have a database with work orders for several machines. A work order may be
of several categories, one of which is "Safety".
The field name = [WOType] and is a text field.
For each machine I need to count the number of open work orders of type =
"Safety" and the total number of orders that are NOT = "Safety". I can't do
it without getting type mismatch errors.

I want the output to be like this:

Machine Safety WO Other WO
123 6 14
234 4 18
345 2 5
456 9 20
.....

There are only seven machines so a form or datasheet output would be fine.
Please help!
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

Remove text from a field 2
Removing text from a field, leaving a number 3
Unique Counting With Multiple Criteria 8
Summing/Counting, but in GROUPS 7
query won't append fields with spaces to text field 6
Counting Values 6
How t count by week whatever is with in the week (two criteria, ma 8
Need to populate a field in a record based on criteria 1

Top