Database deadline

[Guest]

'I have a database that was created a long time ago by someone else, there is
a form in the database that I created a combo box for that I wanted to use to
run a report based on whatever date criteria was selected and the employee
id. I went through this up and down and can not figure out how to make the
combo box pull data from the tables/queries I am selecting. I used the
wizard, you name it, I followed all the steps and instead of populating the
selected employee's data, it populates all the employee's data at the same
time. I am new to this can someone help me, I have a serious deadline to
meet.

 

[John W. Vinson]

'I have a database that was created a long time ago by someone else, there is
a form in the database that I created a combo box for that I wanted to use to
run a report based on whatever date criteria was selected and the employee
id. I went through this up and down and can not figure out how to make the
combo box pull data from the tables/queries I am selecting. I used the
wizard, you name it, I followed all the steps and instead of populating the
selected employee's data, it populates all the employee's data at the same
time. I am new to this can someone help me, I have a serious deadline to
meet.

A combo box used to *select* a record (rather than to update a record) should
- must - be UNBOUND. View the combo box's Properties; the Control Source
should be blank (not the EmployeeID). Similarly, if you're entering a date in
a textbox, that textbox must be unbound.

The Report would be based on a query referencing the controls - e.g.

=[Forms]![NameOfForm]![NameOfCombo]

as a crieterion on the employee ID field.

John W. Vinson [MVP]

 

[Guest]

Thank you so much this definitely help. I just have one small question, In
addition to being able to select individuals how can I add a All criteria.
When I need the report to show total numbers. Please advise and thank you
again.

'I have a database that was created a long time ago by someone else, there is
a form in the database that I created a combo box for that I wanted to use to
run a report based on whatever date criteria was selected and the employee
id. I went through this up and down and can not figure out how to make the
combo box pull data from the tables/queries I am selecting. I used the
wizard, you name it, I followed all the steps and instead of populating the
selected employee's data, it populates all the employee's data at the same
time. I am new to this can someone help me, I have a serious deadline to
meet.

A combo box used to *select* a record (rather than to update a record) should
- must - be UNBOUND. View the combo box's Properties; the Control Source
should be blank (not the EmployeeID). Similarly, if you're entering a date in
a textbox, that textbox must be unbound.

The Report would be based on a query referencing the controls - e.g.

=[Forms]![NameOfForm]![NameOfCombo]

as a crieterion on the employee ID field.

John W. Vinson [MVP]

 

[Guest]

Not sure if the previous response got to you. This definitely help and thank
you so very much for your assistance. One last question, I am at a lost.
Now that I can run these reports individually, I need also to be able to run
the same report for the total numbers. I need to do this without erasing the
ability to run individuals. How do I add an Run All to this. Please advise.

'I have a database that was created a long time ago by someone else, there is
a form in the database that I created a combo box for that I wanted to use to
run a report based on whatever date criteria was selected and the employee
id. I went through this up and down and can not figure out how to make the
combo box pull data from the tables/queries I am selecting. I used the
wizard, you name it, I followed all the steps and instead of populating the
selected employee's data, it populates all the employee's data at the same
time. I am new to this can someone help me, I have a serious deadline to
meet.

A combo box used to *select* a record (rather than to update a record) should
- must - be UNBOUND. View the combo box's Properties; the Control Source
should be blank (not the EmployeeID). Similarly, if you're entering a date in
a textbox, that textbox must be unbound.

The Report would be based on a query referencing the controls - e.g.

=[Forms]![NameOfForm]![NameOfCombo]

as a crieterion on the employee ID field.

John W. Vinson [MVP]

 

[John W. Vinson]

Not sure if the previous response got to you. This definitely help and thank
you so very much for your assistance. One last question, I am at a lost.
Now that I can run these reports individually, I need also to be able to run
the same report for the total numbers. I need to do this without erasing the
ability to run individuals. How do I add an Run All to this. Please advise.

Use a criterion such as

=[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo] IS
NULL

and just leave the combo box blank to see all results.

Put a total textbox in the Report footer with a control source

=Sum([fieldname])

to sum that field up.

John W. Vinson [MVP]

 

[Guest]

Thank you again. I did as you suggested but I continue to run into a snag.
For starters I put in the criteria as you suggested =[Forms]![Quality
Intro]![Combo119] and on the or line I placed =[Forms]![Quality
Intro]![Combo119] Is Null When I run the report it continues to bring up an
individual as though I had selected that individual, when I go back to the
query design, the or criteria has moved to the end of the query (it auto
inserted itself at the end). Is there something I am missing. Please
advise. Also I tried several variations of using Is Null e.g. IS NULL,
IsNull, ISNULL, but nothing seems to work correctly. Finally I used the
=Sum([EmployeeID]), but it continues to pull down error. Sorry to be a pain,
I really have a deadline to meet on this. Thank you for all your help.

Not sure if the previous response got to you. This definitely help and thank
you so very much for your assistance. One last question, I am at a lost.
Now that I can run these reports individually, I need also to be able to run
the same report for the total numbers. I need to do this without erasing the
ability to run individuals. How do I add an Run All to this. Please advise.

Use a criterion such as

=[Forms]![NameOfForm]![NameOfCombo] OR [Forms]![NameOfForm]![NameOfCombo] IS
NULL

and just leave the combo box blank to see all results.

Put a total textbox in the Report footer with a control source

=Sum([fieldname])

to sum that field up.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you again. I did as you suggested but I continue to run into a snag.
For starters I put in the criteria as you suggested =[Forms]![Quality
Intro]![Combo119] and on the or line I placed =[Forms]![Quality
Intro]![Combo119] Is Null When I run the report it continues to bring up an
individual as though I had selected that individual, when I go back to the
query design, the or criteria has moved to the end of the query (it auto
inserted itself at the end). Is there something I am missing. Please
advise. Also I tried several variations of using Is Null e.g. IS NULL,
IsNull, ISNULL, but nothing seems to work correctly. Finally I used the
=Sum([EmployeeID]), but it continues to pull down error. Sorry to be a pain,
I really have a deadline to meet on this. Thank you for all your help.

Please post the SQL view of your query. The query grid will move things around
and get wierd, but the SQL is the *real* query.

I have no idea why you would want to sum the ID's of your employees, nor what
error you got.

I realize you have a deadline - you said so in the subject line. That being
the case, please make it easier for us volunteers: post enough information to
help us solve your problem. You're getting "an error". What error? You're
running a query. What query? You didn't tell us. We're here to help... but
we're not THERE to help.


John W. Vinson [MVP]

 

[Guest]

Many apologies John, I did not mean to imply that you guys had to stop
everything you where doing to help me. I am very grateful for the
assistance. You are right, I do realize that I should not be trying to sum
the employeeID. I am actually trying to do total calculations on the Calls
Monitored. I have totaling for individuals thanks to your help. Now I would
like it for the entire call center. so should it look like this:
=[Forms]![Quality Intro]![Combo119] OR [Forms]![Quality Intro]![Combo119] IS
NULL

and for the totals =Sum([CallsMonitored]). Again I sincerely apologize if I
sounded pushy, and thank you for all your help.

Thank you again. I did as you suggested but I continue to run into a snag.
For starters I put in the criteria as you suggested =[Forms]![Quality
Intro]![Combo119] and on the or line I placed =[Forms]![Quality
Intro]![Combo119] Is Null When I run the report it continues to bring up an
individual as though I had selected that individual, when I go back to the
query design, the or criteria has moved to the end of the query (it auto
inserted itself at the end). Is there something I am missing. Please
advise. Also I tried several variations of using Is Null e.g. IS NULL,
IsNull, ISNULL, but nothing seems to work correctly. Finally I used the
=Sum([EmployeeID]), but it continues to pull down error. Sorry to be a pain,
I really have a deadline to meet on this. Thank you for all your help.

Please post the SQL view of your query. The query grid will move things around
and get wierd, but the SQL is the *real* query.

I have no idea why you would want to sum the ID's of your employees, nor what
error you got.

I realize you have a deadline - you said so in the subject line. That being
the case, please make it easier for us volunteers: post enough information to
help us solve your problem. You're getting "an error". What error? You're
running a query. What query? You didn't tell us. We're here to help... but
we're not THERE to help.


John W. Vinson [MVP]

 

[John W. Vinson]

Many apologies John, I did not mean to imply that you guys had to stop
everything you where doing to help me. I am very grateful for the
assistance. You are right, I do realize that I should not be trying to sum
the employeeID. I am actually trying to do total calculations on the Calls
Monitored. I have totaling for individuals thanks to your help. Now I would
like it for the entire call center. so should it look like this:
=[Forms]![Quality Intro]![Combo119] OR [Forms]![Quality Intro]![Combo119] IS
NULL

and for the totals =Sum([CallsMonitored]). Again I sincerely apologize if I
sounded pushy, and thank you for all your help.

Again...

Please open your Query in design view.

On the menu select View... SQL.

Copy and paste the SQL text of the query to a message here.

That will contain the information about what you're doing, so I'll have a
chance to see what you're doing wrong.

One question; do you want to see each individual total, *and also* the grand
total, on the same report? If so, you'll be better off doing it on the report
(with a group footer and a form footer for the individual and grand totals
respectively). You can't have two levels of totals in a Query without getting
into some pretty complex queries.

John W. Vinson [MVP]

 

[Guest]

As per your request here is the SQL statement. Also, I would like to see the
grand total. At the top of the report I get Individual totals when I run
them individually. So grand totals will be all that I need. One final
question when I tried to implement the Null, it gave me individual reports on
everyone instead of one report with total calls monitored. That is the only
piece I am missing. Please see SQL statement:
SELECT DISTINCTROW [New Monitor Query].EmployeeID, [New Monitor
Query].FirstName, [New Monitor Query].LastName, First([New Monitor
Query].Caller) AS FirstOfCaller, Count([New Monitor Query].RecordNumber) AS
CountOfRecordNumber, Sum([New Monitor Query].NoGreeting) AS SumOfNoGreeting,
Sum([New Monitor Query].NoListening1) AS SumOfNoListening1, Sum([New Monitor
Query].NoListening2) AS SumOfNoListening2, Sum([New Monitor
Query].NoListening3) AS SumOfNoListening3, Sum([New Monitor
Query].NoListening4) AS SumOfNoListening4, Sum([New Monitor
Query].NoListening5) AS SumOfNoListening5, Sum([New Monitor
Query].NoListening6) AS SumOfNoListening6, Sum([New Monitor
Query].NoSolutions1) AS SumOfNoSolutions1, Sum([New Monitor
Query].NoSolutions2) AS SumOfNoSolutions2, Sum([New Monitor
Query].NoSolutions3) AS SumOfNoSolutions3, Sum([New Monitor
Query].NoSolutions4) AS SumOfNoSolutions4, Sum([New Monitor
Query].NoSolutions5) AS SumOfNoSolutions5, Sum([New Monitor
Query].[NoRequired Xfer]) AS [SumOfNoRequired Xfer], Sum([New Monitor
Query].[NoFollow Xfer proc]) AS [SumOfNoFollow Xfer proc], Sum([New Monitor
Query].NoSolutions6) AS SumOfNoSolutions6, Sum([New Monitor
Query].NoSolutions7) AS SumOfNoSolutions7, Sum([New Monitor Query].NoProf1)
AS SumOfNoProf1, Sum([New Monitor Query].NoProf2) AS SumOfNoProf2, Sum([New
Monitor Query].NoProf3) AS SumOfNoProf3, Sum([New Monitor Query].NoClosing)
AS SumOfNoClosing
FROM [New Monitor Query]
WHERE ((([New Monitor Query].MonitorDate)>=[Forms]![Quality Intro]![Start
Date?] And ([New Monitor Query].MonitorDate)<=[Forms]![Quality Intro]![End
Date?]))
GROUP BY [New Monitor Query].EmployeeID, [New Monitor Query].FirstName, [New
Monitor Query].LastName
HAVING ((([New Monitor Query].EmployeeID)=[Forms]![Quality
Intro]![Combo119])) OR ((([Forms]![Quality Intro]![Combo119]) Is Null));

Many apologies John, I did not mean to imply that you guys had to stop
everything you where doing to help me. I am very grateful for the
assistance. You are right, I do realize that I should not be trying to sum
the employeeID. I am actually trying to do total calculations on the Calls
Monitored. I have totaling for individuals thanks to your help. Now I would
like it for the entire call center. so should it look like this:
=[Forms]![Quality Intro]![Combo119] OR [Forms]![Quality Intro]![Combo119] IS
NULL

and for the totals =Sum([CallsMonitored]). Again I sincerely apologize if I
sounded pushy, and thank you for all your help.

Again...

Please open your Query in design view.

On the menu select View... SQL.

Copy and paste the SQL text of the query to a message here.

That will contain the information about what you're doing, so I'll have a
chance to see what you're doing wrong.

One question; do you want to see each individual total, *and also* the grand
total, on the same report? If so, you'll be better off doing it on the report
(with a group footer and a form footer for the individual and grand totals
respectively). You can't have two levels of totals in a Query without getting
into some pretty complex queries.

John W. Vinson [MVP]

 

[John W. Vinson]

As per your request here is the SQL statement. Also, I would like to see the
grand total. At the top of the report I get Individual totals when I run
them individually. So grand totals will be all that I need. One final
question when I tried to implement the Null, it gave me individual reports on
everyone instead of one report with total calls monitored. That is the only
piece I am missing. Please see SQL statement:

If you want to see the individual totals and the grand total on a Report...

*DO THE TOTAL ON THE REPORT*.

It's much, much easier than trying to have subtotals and grand totals both in
a query.

If you have a field in the query named SumOfNoSolutions7 you can put a textbox
on the Report Footer with a control source

=Sum([SumOfNoSolutions7])

to total all the values in the report.

Your WHERE clause, to get a *choice* between either seeing a single value or
seeing all values, needs to be something like this (untested, there's a lot of
parentheses here and I may have gotten some wrong):

WHERE
[New Monitor Query].MonitorDate>=[Forms]![Quality Intro]![Start Date?]
And
[New Monitor Query].MonitorDate)<=[Forms]![Quality Intro]![End Date?]
And
([New Monitor Query].EmployeeID=[Forms]![Quality Intro]![Combo119]
OR [Forms]![Quality Intro]![Combo119] Is Null)

Remove the HAVING clause from the query and try this in place of your existing
WHERE clause.

John W. Vinson [MVP]