Help - How to use count function.

  • Thread starter Thread starter G Lam
  • Start date Start date
G

G Lam

Hi, I have a hard time to use the count function.
I have a table like this:
ordernbr picker packer date
1111 John Tim 10/1/04
1112 Jim Tim 10/1/04
1113 Tim John 10/2/04
1114 Joe Jason 10/2/04
1115 Jason Joe 10/2/04
1116 Tim John 10/2/04
How can I make a query has a data set as following:
Name Pick pack Date
John 1 0 10/1/04
Tim 1 3 10/1/04
John 0 1 10/2/04
Tim 2 0 10/2/04
Joe 1 1 10/2/04
Jason 1 1 10/2/04

Thank you in advance.
Gary
 
G Lam said:
Hi, I have a hard time to use the count function.
I have a table like this:
ordernbr picker packer date
1111 John Tim 10/1/04
1112 Jim Tim 10/1/04
1113 Tim John 10/2/04
1114 Joe Jason 10/2/04
1115 Jason Joe 10/2/04
1116 Tim John 10/2/04
How can I make a query has a data set as following:
Name Pick pack Date
John 1 0 10/1/04
Tim 1 3 10/1/04
John 0 1 10/2/04
Tim 2 0 10/2/04
Joe 1 1 10/2/04
Jason 1 1 10/2/04

Thank you in advance.
Gary
Click to expand...

I thought I knew what you wanted, but I can't make your input data yield
your output data, so I'm not sure. Please either explain or correct
your data.
 
Thanks for the reply. What I want was to make a statistic at the end of each
week to show how many order a person had picked and packed. The name entered
in the sysetem when making the invoices.
I tried this:
I used two make table queries; the table has three fields - Date, name,
Job. (No date needed)
The first query:
SELECT PickPackTest.OrdNbr, PickPackTest.Picker AS Name, ([J]) AS Job INTO
JoinPickPack
FROM PickPackTest;
The secont query:
SELECT PickPackTest.OrdNbr, PickPackTest.Packer AS Name, J AS job INTO
JoinPickPack1
FROM PickPackTest;
Then append the JoinPickPack1 to JoinPickPack

Made a query like this:
SELECT JoinPickPack.Name, count(iif(Job = "Pick", Name,)) AS NbrOfOrdPicked,
Count(iif( Job = "Pack", Name, )) AS NbrOfOrdPacked
FROM JoinPickPack
GROUP BY name;
The problem is that when I do the make table queries, A2K asks input for
field J. I have to enter "Pick" and "Pack" respectively for the queries to
proceed. Also there are too many stepes.
Is there a way to simplify it?
Thank you.
Gary
 
G Lam said:
Thanks for the reply. What I want was to make a statistic at the end
of each week to show how many order a person had picked and packed.
The name entered in the sysetem when making the invoices.
I tried this:
I used two make table queries; the table has three fields - Date,
name, Job. (No date needed)
The first query:
SELECT PickPackTest.OrdNbr, PickPackTest.Picker AS Name, ([J]) AS Job
INTO JoinPickPack
FROM PickPackTest;
The secont query:
SELECT PickPackTest.OrdNbr, PickPackTest.Packer AS Name, J AS job INTO
JoinPickPack1
FROM PickPackTest;
Then append the JoinPickPack1 to JoinPickPack

Made a query like this:
SELECT JoinPickPack.Name, count(iif(Job = "Pick", Name,)) AS
NbrOfOrdPicked, Count(iif( Job = "Pack", Name, )) AS NbrOfOrdPacked
FROM JoinPickPack
GROUP BY name;
The problem is that when I do the make table queries, A2K asks input
for field J. I have to enter "Pick" and "Pack" respectively for the
queries to proceed. Also there are too many stepes.
Is there a way to simplify it?
Thank you.
Gary

Dirk Goldgar said:
I thought I knew what you wanted, but I can't make your input data
yield your output data, so I'm not sure. Please either explain or
correct
your data.
Click to expand...
Click to expand...

I think this query gives you what you're after, though as I said, your
example output data doesn't match your input:

SELECT
OrderDate,
Person,
Sum(PickAndPack.Pick) AS Pick,
Sum(PickAndPack.Pack) AS Pack
FROM
[
SELECT
OrderDate,
Picker As Person,
1 AS Pick,
0 AS Pack
FROM OrderData
UNION ALL
SELECT
OrderDate,
Packer As Person,
0 AS Pick,
1 AS Pack
FROM OrderData
]. AS PickAndPack
GROUP BY OrderDate, Person;
 
Dirk,
That was exactly what I want.
How does the "Union All" worked here?
Thank you.
Gary
 
G Lam said:
Dirk,
That was exactly what I want.
How does the "Union All" worked here?
Thank you.
Gary
I think this query gives you what you're after, though as I said,
your example output data doesn't match your input:

SELECT
OrderDate,
Person,
Sum(PickAndPack.Pick) AS Pick,
Sum(PickAndPack.Pack) AS Pack
FROM
[
SELECT
OrderDate,
Picker As Person,
1 AS Pick,
0 AS Pack
FROM OrderData
UNION ALL
SELECT
OrderDate,
Packer As Person,
0 AS Pick,
1 AS Pack
FROM OrderData
]. AS PickAndPack
GROUP BY OrderDate, Person;
Click to expand...
Click to expand...

The UNION ALL clause takes the two sets of records returned by the two
SELECT statements it comes between and combines them into a single set.
The ALL keyword ensures that it does this without eliminating any
records that are duplicated between the two record sets. Then, treating
that combined set of records as the virtual table PickAndPack, we just
group the records by OrderDate and Person, and Sum the Pick and Pack
fields.
 
Back
Top