Totaling number of oders by state and Order code ID

  • Thread starter Thread starter RickLM
  • Start date Start date
R

RickLM

I have a very basic understanding of “coding†and am now way out of my league.
I would like to be able to run Query(s) to get totals for “open†or “closedâ€
or “all†ORDERS by Code3 and State with only following headers.

Open or Closed or All Code3 State
ID# Total of ID#/State

The closest I have been able to come has been with ListofOrders_Crosstab2
below:
TRANSFORM Count(ListofORDERS.Code3) AS CountOfCode3
SELECT ListofORDERS.Code3, Count(ListofORDERS.Code3) AS CountOfCode31
FROM ListofORDERS
GROUP BY ListofORDERS.Code3
PIVOT ListofORDERS.State;
Thanks for any help
 
If I understand correctly, you want to set Row Headings of Code3 and State
and Count(*). Set the Column Heading to [Field Storing Open Or Closed], and
Count and field as the Value.
 
Duane thank you for the reply. Like I said I have limited programing talent.
I am not sure what your reply actualy meant but thx anyways.
I played around for a few hrs and I was able to figure out following code:
SELECT ListofPossibleEmployers.State, ListofPossibleEmployers.Code,
Count(ListofPossibleEmployers.Code3) AS CountOfCode3,
ListofPossibleEmployers.Status
FROM ListofPossibleEmployers
WHERE (((ListofPossibleEmployers.Fax) Is Not Null))
GROUP BY ListofPossibleEmployers.State, ListofPossibleEmployers.Code,
ListofPossibleEmployers.Status, ListofPossibleEmployers.Code3,
ListofPossibleEmployers.ContactDate
HAVING (((ListofPossibleEmployers.State) Is Not Null) AND
((ListofPossibleEmployers.Code) Is Not Null) AND
((ListofPossibleEmployers.Status)="open") AND
((ListofPossibleEmployers.ContactDate) Is Null))
ORDER BY ListofPossibleEmployers.State, ListofPossibleEmployers.Code3,
ListofPossibleEmployers.Code;
Thx again for the reply.
--
RickLM


Duane Hookom said:
If I understand correctly, you want to set Row Headings of Code3 and State
and Count(*). Set the Column Heading to [Field Storing Open Or Closed], and
Count and field as the Value.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


RickLM said:
I have a very basic understanding of “coding†and am now way out of my league.
I would like to be able to run Query(s) to get totals for “open†or “closedâ€
or “all†ORDERS by Code3 and State with only following headers.

Open or Closed or All Code3 State
ID# Total of ID#/State

The closest I have been able to come has been with ListofOrders_Crosstab2
below:
TRANSFORM Count(ListofORDERS.Code3) AS CountOfCode3
SELECT ListofORDERS.Code3, Count(ListofORDERS.Code3) AS CountOfCode31
FROM ListofORDERS
GROUP BY ListofORDERS.Code3
PIVOT ListofORDERS.State;
Thanks for any help
Click to expand...
Click to expand...
 
Back
Top