Urgent! - Help! Display 0 values in a query or report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query which shows 2 fields. It counts the how many of each status
has occured in the database. eg. Withdrawn 2, Issued permit 53, Sent back 5
etc. What I need is to have all of the statuses showing in a report (even
zero values) There are approximately 10 different statuses and I need them
to appear the same on all reports.
 
Urgent! Send table structures, relationships, and report recordsource SQL
view!
 
Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between #4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));
 
Do you have a table that stores all the status values? This could be added
to your query and joined to [Current Database] with a join that includes all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current Database".

--
Duane Hookom
MS Access MVP
--

JoHickey said:
Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

Duane Hookom said:
Urgent! Send table structures, relationships, and report recordsource SQL
view!
Click to expand...
Click to expand...
 
Sorry, I do not have a table that stores all the status values. They are
only listed in the [Current Database] table. I know what you mean by it
being rather RedFlagish. I didn't create this database....I inherited it. I
have been trying to create an Iif statement, but to no avail. Would that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

Duane Hookom said:
Do you have a table that stores all the status values? This could be added
to your query and joined to [Current Database] with a join that includes all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current Database".

--
Duane Hookom
MS Access MVP
--

JoHickey said:
Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

Duane Hookom said:
Urgent! Send table structures, relationships, and report recordsource SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of each
status
has occured in the database. eg. Withdrawn 2, Issued permit 53, Sent
back
5
etc. What I need is to have all of the statuses showing in a report
(even
zero values) There are approximately 10 different statuses and I need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
Since you have received a couple answers regarding a table of all status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


JoHickey said:
Sorry, I do not have a table that stores all the status values. They are
only listed in the [Current Database] table. I know what you mean by it
being rather RedFlagish. I didn't create this database....I inherited it.
I
have been trying to create an Iif statement, but to no avail. Would that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

Duane Hookom said:
Do you have a table that stores all the status values? This could be
added
to your query and joined to [Current Database] with a join that includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current Database".

--
Duane Hookom
MS Access MVP
--

JoHickey said:
Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of each
status
has occured in the database. eg. Withdrawn 2, Issued permit 53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a report
(even
zero values) There are approximately 10 different statuses and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all the
status values". Still can't find a way to include 0 values.


Duane Hookom said:
Since you have received a couple answers regarding a table of all status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


JoHickey said:
Sorry, I do not have a table that stores all the status values. They are
only listed in the [Current Database] table. I know what you mean by it
being rather RedFlagish. I didn't create this database....I inherited it.
I
have been trying to create an Iif statement, but to no avail. Would that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

Duane Hookom said:
Do you have a table that stores all the status values? This could be
added
to your query and joined to [Current Database] with a join that includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of each
status
has occured in the database. eg. Withdrawn 2, Issued permit 53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a report
(even
zero values) There are approximately 10 different statuses and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
What is the sql of the query that you have used to join the new status
table?

--
Duane Hookom
MS Access MVP
--

JoHickey said:
I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all the
status values". Still can't find a way to include 0 values.


Duane Hookom said:
Since you have received a couple answers regarding a table of all status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


JoHickey said:
Sorry, I do not have a table that stores all the status values. They
are
only listed in the [Current Database] table. I know what you mean by
it
being rather RedFlagish. I didn't create this database....I inherited
it.
I
have been trying to create an Iif statement, but to no avail. Would
that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

:

Do you have a table that stores all the status values? This could be
added
to your query and joined to [Current Database] with a join that
includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of
each
status
has occured in the database. eg. Withdrawn 2, Issued permit 53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a
report
(even
zero values) There are approximately 10 different statuses and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
SELECT Count([Current Database].F1) AS CountOfF1, [Current Database].Status
AS Status
FROM [Current Database] RIGHT JOIN [EAAB Status] ON [Current
Database].Status = [EAAB Status].StatusEAAB
WHERE ((([Current Database].[Date Application Received]) Between #4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));


Duane Hookom said:
What is the sql of the query that you have used to join the new status
table?

--
Duane Hookom
MS Access MVP
--

JoHickey said:
I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all the
status values". Still can't find a way to include 0 values.


Duane Hookom said:
Since you have received a couple answers regarding a table of all status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


Sorry, I do not have a table that stores all the status values. They
are
only listed in the [Current Database] table. I know what you mean by
it
being rather RedFlagish. I didn't create this database....I inherited
it.
I
have been trying to create an Iif statement, but to no avail. Would
that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

:

Do you have a table that stores all the status values? This could be
added
to your query and joined to [Current Database] with a join that
includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of
each
status
has occured in the database. eg. Withdrawn 2, Issued permit 53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a
report
(even
zero values) There are approximately 10 different statuses and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
The easiest way is to first create the query without EAAB Status. Save the
query so that it returns the proper records. Then create a query based on
the saved query and EAAB Status. Add a join line between the status fields
and set the join to display all records from EAAB Status. Display the
StatusEAAB field in the query grid.

Try this sql for your first query:

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received]) Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;


--
Duane Hookom
MS Access MVP
--

JoHickey said:
SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database] RIGHT JOIN [EAAB Status] ON [Current
Database].Status = [EAAB Status].StatusEAAB
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));


Duane Hookom said:
What is the sql of the query that you have used to join the new status
table?

--
Duane Hookom
MS Access MVP
--

JoHickey said:
I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all
the
status values". Still can't find a way to include 0 values.


:

Since you have received a couple answers regarding a table of all
status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


Sorry, I do not have a table that stores all the status values.
They
are
only listed in the [Current Database] table. I know what you mean
by
it
being rather RedFlagish. I didn't create this database....I
inherited
it.
I
have been trying to create an Iif statement, but to no avail. Would
that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

:

Do you have a table that stores all the status values? This could
be
added
to your query and joined to [Current Database] with a join that
includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of
each
status
has occured in the database. eg. Withdrawn 2, Issued permit
53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a
report
(even
zero values) There are approximately 10 different statuses
and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane! I'll try that first thing in the morning.

Duane Hookom said:
The easiest way is to first create the query without EAAB Status. Save the
query so that it returns the proper records. Then create a query based on
the saved query and EAAB Status. Add a join line between the status fields
and set the join to display all records from EAAB Status. Display the
StatusEAAB field in the query grid.

Try this sql for your first query:

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received]) Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;


--
Duane Hookom
MS Access MVP
--

JoHickey said:
SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database] RIGHT JOIN [EAAB Status] ON [Current
Database].Status = [EAAB Status].StatusEAAB
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));


Duane Hookom said:
What is the sql of the query that you have used to join the new status
table?

--
Duane Hookom
MS Access MVP
--

I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all
the
status values". Still can't find a way to include 0 values.


:

Since you have received a couple answers regarding a table of all
status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


Sorry, I do not have a table that stores all the status values.
They
are
only listed in the [Current Database] table. I know what you mean
by
it
being rather RedFlagish. I didn't create this database....I
inherited
it.
I
have been trying to create an Iif statement, but to no avail. Would
that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

:

Do you have a table that stores all the status values? This could
be
added
to your query and joined to [Current Database] with a join that
includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of
each
status
has occured in the database. eg. Withdrawn 2, Issued permit
53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a
report
(even
zero values) There are approximately 10 different statuses
and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
Sorry to be such a bother, but I tried what you requested.

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received] Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;

It gave me this error message: The LEVEL clause includes a reserved word or
argument that is misspelled or missing, or the punctuation is incorrect.

JoHickey said:
Thanks Duane! I'll try that first thing in the morning.

Duane Hookom said:
The easiest way is to first create the query without EAAB Status. Save the
query so that it returns the proper records. Then create a query based on
the saved query and EAAB Status. Add a join line between the status fields
and set the join to display all records from EAAB Status. Display the
StatusEAAB field in the query grid.

Try this sql for your first query:

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received]) Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;


--
Duane Hookom
MS Access MVP
--

JoHickey said:
SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database] RIGHT JOIN [EAAB Status] ON [Current
Database].Status = [EAAB Status].StatusEAAB
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));


:

What is the sql of the query that you have used to join the new status
table?

--
Duane Hookom
MS Access MVP
--

I created a table of all the status values, with a join to the current
database that includes all the records from the "table that stores all
the
status values". Still can't find a way to include 0 values.


:

Since you have received a couple answers regarding a table of all
status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


Sorry, I do not have a table that stores all the status values.
They
are
only listed in the [Current Database] table. I know what you mean
by
it
being rather RedFlagish. I didn't create this database....I
inherited
it.
I
have been trying to create an Iif statement, but to no avail. Would
that
work if it was done correctly? I can't quite get the syntax down.

I appreciate the time you are taking to help me out here. Thanks.

:

Do you have a table that stores all the status values? This could
be
added
to your query and joined to [Current Database] with a join that
includes
all
the records from the "table that stores all the status values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

I have a query which shows 2 fields. It counts the how many of
each
status
has occured in the database. eg. Withdrawn 2, Issued permit
53,
Sent
back
5
etc. What I need is to have all of the statuses showing in a
report
(even
zero values) There are approximately 10 different statuses
and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
Status might be a reserved word. Try place [ ]s around it and remove the
first semi-colon which was an error in my earlier post.

SELECT Count(F1) AS CountOfF1, [Current Database].[Status]
FROM [Current Database]
WHERE [Date Application Received] Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB"
GROUP BY [Status];

--
Duane Hookom
MS Access MVP
--

JoHickey said:
Sorry to be such a bother, but I tried what you requested.

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received] Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;

It gave me this error message: The LEVEL clause includes a reserved word
or
argument that is misspelled or missing, or the punctuation is incorrect.

JoHickey said:
Thanks Duane! I'll try that first thing in the morning.

Duane Hookom said:
The easiest way is to first create the query without EAAB Status. Save
the
query so that it returns the proper records. Then create a query based
on
the saved query and EAAB Status. Add a join line between the status
fields
and set the join to display all records from EAAB Status. Display the
StatusEAAB field in the query grid.

Try this sql for your first query:

SELECT Count(F1) AS CountOfF1, [Current Database].Status
FROM [Current Database]
WHERE [Date Application Received]) Between #4/1/2005# And Date()
AND Status<>"Cancelled" AND Stage="EAAB";
GROUP BY Status;


--
Duane Hookom
MS Access MVP
--

SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database] RIGHT JOIN [EAAB Status] ON [Current
Database].Status = [EAAB Status].StatusEAAB
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));


:

What is the sql of the query that you have used to join the new
status
table?

--
Duane Hookom
MS Access MVP
--

I created a table of all the status values, with a join to the
current
database that includes all the records from the "table that stores
all
the
status values". Still can't find a way to include 0 values.


:

Since you have received a couple answers regarding a table of all
status
values, I suggest you create one.

--
Duane Hookom
MS Access MVP


Sorry, I do not have a table that stores all the status values.
They
are
only listed in the [Current Database] table. I know what you
mean
by
it
being rather RedFlagish. I didn't create this database....I
inherited
it.
I
have been trying to create an Iif statement, but to no avail.
Would
that
work if it was done correctly? I can't quite get the syntax
down.

I appreciate the time you are taking to help me out here.
Thanks.

:

Do you have a table that stores all the status values? This
could
be
added
to your query and joined to [Current Database] with a join
that
includes
all
the records from the "table that stores all the status
values".

It is somewhat RedFlagish that you have a table named "Current
Database".

--
Duane Hookom
MS Access MVP
--

message
Fields All from the Same table:
F1 - Count
Status - Group by <> "Cancelled"
Stage - Group by "EAAB"
Date Application Received - Where Between #2005/04/01# And
Date()


SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [Current Database]
WHERE ((([Current Database].[Date Application Received])
Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND
(([Current
Database].Stage)="EAAB"));

:

Urgent! Send table structures, relationships, and report
recordsource
SQL
view!

--
Duane Hookom
MS Access MVP
--

message
I have a query which shows 2 fields. It counts the how
many of
each
status
has occured in the database. eg. Withdrawn 2, Issued
permit
53,
Sent
back
5
etc. What I need is to have all of the statuses showing
in a
report
(even
zero values) There are approximately 10 different
statuses
and I
need
them
to appear the same on all reports.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top