help with count (maybe) in query

G

Guest

I'm trying to create a query that will return the number of times the first 4
digits of a nine digiti number appear on a certain day such that the first
column will contain unique day values defined by StartDate and EndDate and
the remaining columns will contain the respective item counts from those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS [Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And [End
Date]));

All the data is in the same table (Missent Table). Using the above I get an
error message and using the following I get the total of ALL categories (in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And [End
Date]));

If I've omitted any requisite information, please let me know. Thank you in
advance for the assistance.
 
J

Jeff Boyce

A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


Jeff Boyce said:
A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

asaylor said:
I'm trying to create a query that will return the number of times the
first 4
digits of a nine digiti number appear on a certain day such that the
first
column will contain unique day values defined by StartDate and EndDate and
the remaining columns will contain the respective item counts from those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And [End
Date]));

All the data is in the same table (Missent Table). Using the above I get
an
error message and using the following I get the total of ALL categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And [End
Date]));

If I've omitted any requisite information, please let me know. Thank you
in
advance for the assistance.
Click to expand...
Click to expand...
 
D

Duane Hookom

Thanks for the "picture".
It looks like a crosstab query to me:

TRANSFORM Val(Nz(Count([RT]),0)) AS Expr2
SELECT [Missent Table].Date
FROM [Missent Table]
GROUP BY [Missent Table].Date
PIVOT Left([RT],4);

You might need to join this query to a query that contains all dates.
--
Duane Hookom
MS Access MVP

asaylor said:
Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


Jeff Boyce said:
A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

asaylor said:
I'm trying to create a query that will return the number of times the
first 4
digits of a nine digiti number appear on a certain day such that the
first
column will contain unique day values defined by StartDate and EndDate
and
the remaining columns will contain the respective item counts from
those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

All the data is in the same table (Missent Table). Using the above I
get
an
error message and using the following I get the total of ALL categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

If I've omitted any requisite information, please let me know. Thank
you
in
advance for the assistance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Duane,

Thank you for the reply. I used your query and it worked. However, I need
the report to be restricted by start and stop dates. Additionally, some
fields include combinations of the first 4 digits of the RT field, so I need
to define each column according to which first 4 digits of the RT field it
will contain. I made a similar query:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And [EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT (Left([RT],4)=1111);

but the 1 column I defined appears as "-1" when I run the query. Any
suggestions or am I heading in the wrong direction with this?

Duane Hookom said:
Thanks for the "picture".
It looks like a crosstab query to me:

TRANSFORM Val(Nz(Count([RT]),0)) AS Expr2
SELECT [Missent Table].Date
FROM [Missent Table]
GROUP BY [Missent Table].Date
PIVOT Left([RT],4);

You might need to join this query to a query that contains all dates.
--
Duane Hookom
MS Access MVP

asaylor said:
Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


Jeff Boyce said:
A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create a query that will return the number of times the
first 4
digits of a nine digiti number appear on a certain day such that the
first
column will contain unique day values defined by StartDate and EndDate
and
the remaining columns will contain the respective item counts from
those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

All the data is in the same table (Missent Table). Using the above I
get
an
error message and using the following I get the total of ALL categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

If I've omitted any requisite information, please let me know. Thank
you
in
advance for the assistance.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If you want to filter by the first 4 characters in RT, you can either add to
the WHERE clause or specify the Column Headings like:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT Left([RT],4) IN ("1111");

--
Duane Hookom
MS Access MVP


asaylor said:
Duane,

Thank you for the reply. I used your query and it worked. However, I
need
the report to be restricted by start and stop dates. Additionally, some
fields include combinations of the first 4 digits of the RT field, so I
need
to define each column according to which first 4 digits of the RT field it
will contain. I made a similar query:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT (Left([RT],4)=1111);

but the 1 column I defined appears as "-1" when I run the query. Any
suggestions or am I heading in the wrong direction with this?

Duane Hookom said:
Thanks for the "picture".
It looks like a crosstab query to me:

TRANSFORM Val(Nz(Count([RT]),0)) AS Expr2
SELECT [Missent Table].Date
FROM [Missent Table]
GROUP BY [Missent Table].Date
PIVOT Left([RT],4);

You might need to join this query to a query that contains all dates.
--
Duane Hookom
MS Access MVP

asaylor said:
Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


:

A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create a query that will return the number of times
the
first 4
digits of a nine digiti number appear on a certain day such that
the
first
column will contain unique day values defined by StartDate and
EndDate
and
the remaining columns will contain the respective item counts from
those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

All the data is in the same table (Missent Table). Using the above
I
get
an
error message and using the following I get the total of ALL
categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

If I've omitted any requisite information, please let me know.
Thank
you
in
advance for the assistance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you very much, your query works for "1111". A couple of syntax
questions - if I wanted the titled of the column to be something other than
"1111", how would I define it? Also, how do I add to the WHERE statement,
including some columns that will contain more than one 4 digit condition?

Duane Hookom said:
If you want to filter by the first 4 characters in RT, you can either add to
the WHERE clause or specify the Column Headings like:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT Left([RT],4) IN ("1111");

--
Duane Hookom
MS Access MVP


asaylor said:
Duane,

Thank you for the reply. I used your query and it worked. However, I
need
the report to be restricted by start and stop dates. Additionally, some
fields include combinations of the first 4 digits of the RT field, so I
need
to define each column according to which first 4 digits of the RT field it
will contain. I made a similar query:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT (Left([RT],4)=1111);

but the 1 column I defined appears as "-1" when I run the query. Any
suggestions or am I heading in the wrong direction with this?

Duane Hookom said:
Thanks for the "picture".
It looks like a crosstab query to me:

TRANSFORM Val(Nz(Count([RT]),0)) AS Expr2
SELECT [Missent Table].Date
FROM [Missent Table]
GROUP BY [Missent Table].Date
PIVOT Left([RT],4);

You might need to join this query to a query that contains all dates.
--
Duane Hookom
MS Access MVP

Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


:

A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create a query that will return the number of times
the
first 4
digits of a nine digiti number appear on a certain day such that
the
first
column will contain unique day values defined by StartDate and
EndDate
and
the remaining columns will contain the respective item counts from
those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111) AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

All the data is in the same table (Missent Table). Using the above
I
get
an
error message and using the following I get the total of ALL
categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date] And
[End
Date]));

If I've omitted any requisite information, please let me know.
Thank
you
in
advance for the assistance.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You should have a lookup table with the values like "1111" and "something
other than 1111". You can then join this table in to your crosstab to change
the column headings. This would also allow you to combine more than one 4
digit condition with "something other than 1111".

--
Duane Hookom
MS Access MVP


asaylor said:
Thank you very much, your query works for "1111". A couple of syntax
questions - if I wanted the titled of the column to be something other
than
"1111", how would I define it? Also, how do I add to the WHERE statement,
including some columns that will contain more than one 4 digit condition?

Duane Hookom said:
If you want to filter by the first 4 characters in RT, you can either add
to
the WHERE clause or specify the Column Headings like:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT Left([RT],4) IN ("1111");

--
Duane Hookom
MS Access MVP


asaylor said:
Duane,

Thank you for the reply. I used your query and it worked. However, I
need
the report to be restricted by start and stop dates. Additionally,
some
fields include combinations of the first 4 digits of the RT field, so I
need
to define each column according to which first 4 digits of the RT field
it
will contain. I made a similar query:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count([Missent Table].RT) AS CountOfRT
SELECT [Missent Table].[Date Sent Out]
FROM [Missent Table]
WHERE ((([Missent Table].[Date Sent Out]) Between [StartDate] And
[EndDate]))
GROUP BY [Missent Table].[Date Sent Out]
PIVOT (Left([RT],4)=1111);

but the 1 column I defined appears as "-1" when I run the query. Any
suggestions or am I heading in the wrong direction with this?

:

Thanks for the "picture".
It looks like a crosstab query to me:

TRANSFORM Val(Nz(Count([RT]),0)) AS Expr2
SELECT [Missent Table].Date
FROM [Missent Table]
GROUP BY [Missent Table].Date
PIVOT Left([RT],4);

You might need to join this query to a query that contains all dates.
--
Duane Hookom
MS Access MVP

Data:

Date RT
10/2 123412341
10/2 123443210
10/5 987654321
10/5 543210123
10/6 123487654
10/10 987698765
10/13 543212345
10/13 543200000

Desired Output:

1234 5432 9876
10/2 2 0 0
10/3 0 0 0
10/4 0 0 0
10/5 0 1 1
10/6 1 0 0
10/10 0 0 1
10/11 0 0 0
10/12 0 0 0
10/13 0 2 0


:

A picture is worth a thousand words...

Can you provide an example of the data in the fields, and the
desired
output?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to create a query that will return the number of times
the
first 4
digits of a nine digiti number appear on a certain day such that
the
first
column will contain unique day values defined by StartDate and
EndDate
and
the remaining columns will contain the respective item counts
from
those
categories on those days. In SQL:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].RT, count(Left([Missent Table].RT,4)=1111)
AS
[Dallas
RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date]
And
[End
Date]));

All the data is in the same table (Missent Table). Using the
above
I
get
an
error message and using the following I get the total of ALL
categories
(in
every category) on a certain day:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Missent Table].[Date Sent Out], Count(Left([Missent
Table].RT,4)=1111) AS [Dallas RCPC I]
FROM [Missent Table]
GROUP BY [Missent Table].[Date Sent Out]
HAVING ((([Missent Table].[Date Sent Out]) Between [Start Date]
And
[End
Date]));

If I've omitted any requisite information, please let me know.
Thank
you
in
advance for the assistance.
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

Using automation to load a cross tab query to Excel 11
Crosstab query with multiple fields 2
Display columns without data in cross tab query 4
Parameter Query with DSum 1
crosstab with conditional SUM() 1
Error message on Where statement 6
Joining 3 Queries That Count 8
Need Help with Totals Query 24

Top