HELP NEEDED WHERE CLAUSE

[PAOLO]

Hey guys I need some help with a where clause this is the first time I have
ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery" which
states if the session has been D "Delivered" or C "Cancelled". I want to
have two fields at the page footer of my report report, one which can count
the number of sessions delivered and one that can count the ones cancelled.

I have tried to write the where clause for it but I continuosly get syntax
errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it (I am
going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE [Session
Dates].[Delivery]="D";

Thanks for your help

 

[Wayne Morgan]

If you're trying to use the query in an unbound textbox, that is the
problem, not the WHERE clause. For the unbound textbox, try the DCount()
function in the Control Source of the textbox.

Example:
=DCount("Delivery", "[Session Dates]", "Delivery = 'D'")

 

[PAOLO]

Nope it's giving a number 52 all the time regardless what department I
select and if I go into the query and select all the sessions that have D in
delivery the total is 81 so I don't really understand what that is...

If you're trying to use the query in an unbound textbox, that is the
problem, not the WHERE clause. For the unbound textbox, try the DCount()
function in the Control Source of the textbox.

Example:
=DCount("Delivery", "[Session Dates]", "Delivery = 'D'")

--
Wayne Morgan
MS Access MVP

Hey guys I need some help with a where clause this is the first time I
have
ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery" which
states if the session has been D "Delivered" or C "Cancelled". I want to
have two fields at the page footer of my report report, one which can
count
the number of sessions delivered and one that can count the ones
cancelled.

I have tried to write the where clause for it but I continuosly get syntax
errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it (I am
going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE
[Session
Dates].[Delivery]="D";

Thanks for your help

 

[Wayne Morgan]

Try this instead then. Since you really don't care about "Delivery", other
than that it contain "D", this may help.

=DCount("*", "[Session Dates]", "Delivery = 'D'")


--
Wayne Morgan
MS Access MVP

Nope it's giving a number 52 all the time regardless what department I
select and if I go into the query and select all the sessions that have D
in
delivery the total is 81 so I don't really understand what that is...







message

If you're trying to use the query in an unbound textbox, that is the
problem, not the WHERE clause. For the unbound textbox, try the DCount()
function in the Control Source of the textbox.

Example:
=DCount("Delivery", "[Session Dates]", "Delivery = 'D'")

--
Wayne Morgan
MS Access MVP

Hey guys I need some help with a where clause this is the first time I
have
ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery"
which
states if the session has been D "Delivered" or C "Cancelled". I want
to
have two fields at the page footer of my report report, one which can
count
the number of sessions delivered and one that can count the ones
cancelled.

I have tried to write the where clause for it but I continuosly get syntax
errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it (I am
going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE
[Session
Dates].[Delivery]="D";

Thanks for your help

 

[Dirk Goldgar]

Hey guys I need some help with a where clause this is the first time
I have ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery"
which states if the session has been D "Delivered" or C "Cancelled".
I want to have two fields at the page footer of my report report, one
which can count the number of sessions delivered and one that can
count the ones cancelled.

I have tried to write the where clause for it but I continuosly get
syntax errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it
(I am going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE
[Session Dates].[Delivery]="D";

Thanks for your help

If you put the text boxes in the Report Footer, instead of the Page
Footer, you should be able to use expressions like these for the
ControlSources:

=Abs(Sum([Delivery]="D"))

=Abs(Sum([Delivery]="C"))

 

[PAOLO]

The abs function worked... Can I ask what does it actually do? (so I can use
it properly for other reports)

Thank you so much

Hey guys I need some help with a where clause this is the first time
I have ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery"
which states if the session has been D "Delivered" or C "Cancelled".
I want to have two fields at the page footer of my report report, one
which can count the number of sessions delivered and one that can
count the ones cancelled.

I have tried to write the where clause for it but I continuosly get
syntax errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it
(I am going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE
[Session Dates].[Delivery]="D";

Thanks for your help

If you put the text boxes in the Report Footer, instead of the Page
Footer, you should be able to use expressions like these for the
ControlSources:

=Abs(Sum([Delivery]="D"))

=Abs(Sum([Delivery]="C"))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

ABS is Absolute Value: it converts negative numbers to the equivalent
positive number.

What you have to understand is that True is stored as -1 in Access (and
False is 0).

You used =Abs(Sum([Delivery]="D"))

For each row, [Delivery] = "D" will be either -1 (True) or 0 (False).
Therefore, if you sum those values, you'll get -n, where n is the number of
values that are True. Using ABS converts that to n.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The abs function worked... Can I ask what does it actually do? (so I can
use
it properly for other reports)

Thank you so much

Hey guys I need some help with a where clause this is the first time
I have ever written one (still va beginner with SQL)

I have a report that lists all Training sessions I booked with each
department of my company. The report has a field called "Delivery"
which states if the session has been D "Delivered" or C "Cancelled".
I want to have two fields at the page footer of my report report, one
which can count the number of sessions delivered and one that can
count the ones cancelled.

I have tried to write the where clause for it but I continuosly get
syntax errors.

What I would like to know is:

1. where do I put the fomula:
a: insert a new column in the query (one for cancelled and one for
delivered)
b: insert an unbound box in the report and type it there

Could you pleae let me know and also give me the exact syntax for it
(I am going mad!!!)

I tried this, but I must be missing something:

SELECT Count([Session Dates].[Delivery]) FROM [Session Dates] WHERE
[Session Dates].[Delivery]="D";

Thanks for your help

If you put the text boxes in the Report Footer, instead of the Page
Footer, you should be able to use expressions like these for the
ControlSources:

=Abs(Sum([Delivery]="D"))

=Abs(Sum([Delivery]="C"))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)