Percentage report

[learnasugo]

My report is pulling from two tables, but the report only seems to be looking at one table. Here is how the tables look:

CSR Table

MMSJob - PK
InDate - Date
CCSR - Text
CClient - Text

Error Table

PID - PK
ErrorDate - Date
Error - memo
MMSJob - Number

What I am trying to do is count the total records by going into the CCSR Footer and putting a text box in it with the Countrol Source looking like this =Count([CCSR]). Instead of getting the number of jobs submitted by a CSR I am getting the number of jobs that have errors for each CSR.

I am also trying to calculate number of errors that each CSR has for all of their jobs. I am doing this by putting a text box in Error.MMSJob Footer and making the text box look like this =count([Error.MMSJob]). I can't even open the report to see what that is pulling off of. I keep getting an error message that says "Invalid bracketing of name '[Error.MMSJob]'"

Can anyone tell me what the heck is going on???

learnasugo

 

[jebry]

Hi learnasugo!

It sounds like you need to change the join you have on the tables. You need to have an outer join the pulls all of the data from the CSR Table and only data that matches from the Error table.

Your bracketing problem can be cured like this:

[Error].[MMSJob]

hth


Jeff Bridgham
bridgham@purdue.edu

 

[learnasugo]

I tried the bracketing and it still wasn't working. It was not recognizing the name as that of a field name that is in the table (it was asking me to put in a value for this field).

Can you refresh my memory on what an outer join is? I remember learning it somewhere and not liking it.

learnasugo

 

[jebry]

Hi again!

An outer join is used to make sure that all the records from one of the tables appears in the query, even if there is no matching record in the other table. In this case you want to see all of the records from the CSR table even if there had been no error recorded in the Error table.

To fix the second problem, you need to go back to the query. The problem here is that you pulled MMSJob from the CSR table so it isn't seeing a field [Error].[MMSJob] because there is not one. What you can do is, in the query, add a field:

ErrorMMSJob: [Error].[MMSJob]

Then, in the report you can do a count on MMSJob and ErrorMMSJob to get the two counts you need.

If you want to post the SQL for the query I could help more.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[learnasugo]

This was taken from me and given back, supposedly fixed and good to go, it's not. They have cut me loose and expect me to fix something that I know nothing about and is over my head. I am going to try to explain what they did. Please bear with my rambling.

I guess I will start with the tables. The CSR table stayed the same, but two fields were added to the error table. They are Whites and Lives. A new table was also added.

tblCSR

CSRName - Text
ID - PK

Then a query was created that queried for all records and pulled from the CSR and Error tables the following fields:

QryAllRecords

InDate - CSR
MMSJob - CSR
CSR - CSR
Client - CSR
Error - Error

Then a groupby query was created that pulled from QryAllRecords that counted total jobs (This counts all jobs by CSR and Client. I only want to know the total number of jobs submitted by the CSR, not broken out into clients.)

QryCountTotalJobs

CSR - Groupby
InDate - Groupby
Client - Groupby
MMSJob - Count

Another groupby query was created that also pulled from QryAllRecords. This query is an error count query. (I'm starting to get lost here so I can only give you info, not definitions).

QryErrorCount

CSR - Groupby
InDate - Groupby
Error - Count
Error - Where - Criteria = IsNotNull

The query that I am currently having problems with was created next. It pulled from QryCountTotalJobs and QryErrorCount.

QryPercentage

CSR
Client
InDate
CountofMMSJob
JobsErrored IIf(IsNull([CountOfError]),0,[CountOfError]
PctInError ([Jobs Errored]\[CountofMMSJob])

When I run the QryPercentage I get something like this:

CSR Client Total Jobs Jobs Errored PCT InError

Sean Cap 3 4 100.00%
Sean Centrex 1 4 400.00%

Does anyone have a clue as to what is going on???? Please help me!!

learnasugo