Counting deliveries

[Dean]

My users are entering details of every delivery into the warehouse. Where
there are no problems they click a "No problems" check box, deliveries with
problems have more details added.
A report is to be drawn from this by supplier and date range (entered into
the form). What I would like is to have the "total number" of deliveries by
the supplier with the "number that had problems" but only show the details
for the ones with problems.

Is this to be done in the Query or a calculation in the report and how?
I can count the total deliveries or the ones with problems in seperate
queries but not both in the same!

Thanks
Dean

 

[Ofer Cohen]

Using a Query, try

Select Count(*) As CountNoProblem
From TableNAme
Where DateFieldName Between Forms![FormName]![StartDate] And
Forms![FormName]![EndDate] And SupplierFieldName =
Forms![FormName]![Supplier]
And Nz([No problems],False) = False

Then you can refer to the new field from the report

=DlookUp("CountNoProblem","QueryName")

*********
Note: in the example I assumed that you have three text boxes in the form,
start date, end date, supplier.
Don't forget to fix all the names in the SQL I provided you with

 

[Ofer Cohen]

One more thing,
If the report already display the right data, all you need is to create a
text box in the report footer and write in the ControlSource

=Count(*)

 

[Dean]

Ofer,

Thanks I'm still having problems though.
The query works when run on it's own and displays the correct info but when
I put it into the report it displays #Error

Dean

Using a Query, try

Select Count(*) As CountNoProblem
From TableNAme
Where DateFieldName Between Forms![FormName]![StartDate] And
Forms![FormName]![EndDate] And SupplierFieldName =
Forms![FormName]![Supplier]
And Nz([No problems],False) = False

Then you can refer to the new field from the report

=DlookUp("CountNoProblem","QueryName")

*********
Note: in the example I assumed that you have three text boxes in the form,
start date, end date, supplier.
Don't forget to fix all the names in the SQL I provided you with

--
Good Luck
BS"D

My users are entering details of every delivery into the warehouse. Where
there are no problems they click a "No problems" check box, deliveries with
problems have more details added.
A report is to be drawn from this by supplier and date range (entered into
the form). What I would like is to have the "total number" of deliveries by
the supplier with the "number that had problems" but only show the details
for the ones with problems.

Is this to be done in the Query or a calculation in the report and how?
I can count the total deliveries or the ones with problems in seperate
queries but not both in the same!

Thanks
Dean

 

[Ofer Cohen]

What did you write in the text box?

Can you post:
1. QueryNAme
2. SQL of the query
3. What you typed in the text box control source, in the report

--
Good Luck
BS"D

Ofer,

Thanks I'm still having problems though.
The query works when run on it's own and displays the correct info but when
I put it into the report it displays #Error

Dean

Using a Query, try

Select Count(*) As CountNoProblem
From TableNAme
Where DateFieldName Between Forms![FormName]![StartDate] And
Forms![FormName]![EndDate] And SupplierFieldName =
Forms![FormName]![Supplier]
And Nz([No problems],False) = False

Then you can refer to the new field from the report

=DlookUp("CountNoProblem","QueryName")

*********
Note: in the example I assumed that you have three text boxes in the form,
start date, end date, supplier.
Don't forget to fix all the names in the SQL I provided you with

--
Good Luck
BS"D

My users are entering details of every delivery into the warehouse. Where
there are no problems they click a "No problems" check box, deliveries with
problems have more details added.
A report is to be drawn from this by supplier and date range (entered into
the form). What I would like is to have the "total number" of deliveries by
the supplier with the "number that had problems" but only show the details
for the ones with problems.

Is this to be done in the Query or a calculation in the report and how?
I can count the total deliveries or the ones with problems in seperate
queries but not both in the same!

Thanks
Dean

 

[Dean]

Done it. (or someone else did it for me).
They ran another query, that just had the supplier and a count of the
supplier, this was then pulled into a third query that had all the fields
from both queries and the report was run from this (the third query).

Thanks for your help
Dean

What did you write in the text box?

Can you post:
1. QueryNAme
2. SQL of the query
3. What you typed in the text box control source, in the report

--
Good Luck
BS"D

Ofer,

Thanks I'm still having problems though.
The query works when run on it's own and displays the correct info but when
I put it into the report it displays #Error

Dean

Using a Query, try

Select Count(*) As CountNoProblem
From TableNAme
Where DateFieldName Between Forms![FormName]![StartDate] And
Forms![FormName]![EndDate] And SupplierFieldName =
Forms![FormName]![Supplier]
And Nz([No problems],False) = False

Then you can refer to the new field from the report

=DlookUp("CountNoProblem","QueryName")

*********
Note: in the example I assumed that you have three text boxes in the form,
start date, end date, supplier.
Don't forget to fix all the names in the SQL I provided you with

--
Good Luck
BS"D


:

My users are entering details of every delivery into the warehouse. Where
there are no problems they click a "No problems" check box, deliveries with
problems have more details added.
A report is to be drawn from this by supplier and date range (entered into
the form). What I would like is to have the "total number" of deliveries by
the supplier with the "number that had problems" but only show the details
for the ones with problems.

Is this to be done in the Query or a calculation in the report and how?
I can count the total deliveries or the ones with problems in seperate
queries but not both in the same!

Thanks
Dean