Show a zero

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

Guest

I have two queries. The second query is basically relating to the first
query. I want the second query to show a zero (0) if there are no matching
records. Please help...

The first query is:

SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6));

The second query is:

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, Sum([Count]*3) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
 
I want the second query to show a zero (0) if there are no matching records.
What field do you expect the zero to appear with no matching records?
 
The Points field.

KARL DEWEY said:
What field do you expect the zero to appear with no matching records?
--
KARL DEWEY
Build a little - Test a little


JJ said:
I have two queries. The second query is basically relating to the first
query. I want the second query to show a zero (0) if there are no matching
records. Please help...

The first query is:

SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6));

The second query is:

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, Sum([Count]*3) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
Click to expand...
Click to expand...
 
The problem is that if no records meet your criteria then the first query
outputs nothing.

Try this untested --
SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6))
UNION ALL SELECT TOP 1 Activity_tbl.Date, Activity_tbl.Activity, 0 AS [Count]
FROM Activity_tbl;

Then use IFF statement in the second query.

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, IFF(Sum([Count])=0, 0, Sum([Count]*3)) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
--
KARL DEWEY
Build a little - Test a little


JJ said:
The Points field.

KARL DEWEY said:
I want the second query to show a zero (0) if there are no matching records.
Click to expand...
What field do you expect the zero to appear with no matching records?
--
KARL DEWEY
Build a little - Test a little


JJ said:
I have two queries. The second query is basically relating to the first
query. I want the second query to show a zero (0) if there are no matching
records. Please help...

The first query is:

SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6));

The second query is:

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, Sum([Count]*3) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
Click to expand...
Click to expand...
Click to expand...
 
Post back your SQL for a look-see.
--
KARL DEWEY
Build a little - Test a little


JJ said:
Getting closer. First query works great, but the second query is giving me
the following error related to the IFF Function:

Undefined function <name> in expression. (Error 3085)



KARL DEWEY said:
The problem is that if no records meet your criteria then the first query
outputs nothing.

Try this untested --
SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6))
UNION ALL SELECT TOP 1 Activity_tbl.Date, Activity_tbl.Activity, 0 AS [Count]
FROM Activity_tbl;

Then use IFF statement in the second query.

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, IFF(Sum([Count])=0, 0, Sum([Count]*3)) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
--
KARL DEWEY
Build a little - Test a little


JJ said:
The Points field.

:

I want the second query to show a zero (0) if there are no matching records.
What field do you expect the zero to appear with no matching records?
--
KARL DEWEY
Build a little - Test a little


:

I have two queries. The second query is basically relating to the first
query. I want the second query to show a zero (0) if there are no matching
records. Please help...

The first query is:

SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6));

The second query is:

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, Sum([Count]*3) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
Click to expand...
Click to expand...
Click to expand...
 
Getting closer. First query works great, but the second query is giving me
the following error related to the IFF Function:

Undefined function <name> in expression. (Error 3085)



KARL DEWEY said:
The problem is that if no records meet your criteria then the first query
outputs nothing.

Try this untested --
SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6))
UNION ALL SELECT TOP 1 Activity_tbl.Date, Activity_tbl.Activity, 0 AS [Count]
FROM Activity_tbl;

Then use IFF statement in the second query.

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, IFF(Sum([Count])=0, 0, Sum([Count]*3)) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
--
KARL DEWEY
Build a little - Test a little


JJ said:
The Points field.

KARL DEWEY said:
I want the second query to show a zero (0) if there are no matching records.
What field do you expect the zero to appear with no matching records?
--
KARL DEWEY
Build a little - Test a little


:

I have two queries. The second query is basically relating to the first
query. I want the second query to show a zero (0) if there are no matching
records. Please help...

The first query is:

SELECT Activity_tbl.Date, Activity_tbl.Activity, 1 AS [Count]
FROM Activity_tbl
WHERE (((Activity_tbl.Date) Between [Forms]![Sales_Recap_frm]![Date1] And
[Forms]![Sales_Recap_frm]![Date1]-6));

The second query is:

SELECT Points_Activities_qry.Activity, Sum(Points_Activities_qry.Count) AS
SumOfCount, Sum([Count]*3) AS Points
FROM Points_Activities_qry
GROUP BY Points_Activities_qry.Activity
HAVING (((Points_Activities_qry.Activity)="Service Call"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top