Creating a report

[cbende2]

Need		Completed
# left to complete	Document Creation	# completed
# left to complete	Forms Creation	# completed
# left to complete	Reviews Completed	# completed
# left to complete	Packages Completed	# completed
# left to complete	Mailing	# completed
# left to complete	Invoices	# completed
# left to complete	Signatures	# completed

Basically, Each cell in the middle column is a field in my table(without the spaces). And all of these fields are Date fields. I need to count the number of records for each field that are null(this would be the # left to complete), and I need to count the number of records that are not null(this would be the # completed).

How would I go about setting this up?

Any help would be greatly appreciated!

[ItsMe]

I would create a separate query using the query builder. I would use the wizard. I would opt for the "Find Unmatched Records" or something close to that description. Basically, I would want a query that had an Outer Join on the Date field and then an Is Null for the criteria of said field.

With that, I would add an aggregate function like Count(). Then figure a way to bring this query into a parent query.

[cbende2]

what do you meant outer join on the date field?

[ItsMe]

I mean an outer join vs an Inner Join

So the SQL would be LEFT or RIGHT

If you use the wizard to create an Unmatched Record query, it will generate the SQL. Then you can look at it in Design View and add your aggregate function

[cbende2]

How do i create the unmatched record query on the same table? It's not showing up for the second step of the wizard

[cbende2]

This is my SQL returned for an Unmatched query on my table and simple query of the table: SELECT PPARestatementTable.DocumentsCreated, PPARestatementTable.FormsCreated, PPARestatementTable.DateReviewed, PPARestatementTable.FinalPackageCreated, PPARestatementTable.RestatementMailed, PPARestatementTable.InvoicePlan, PPARestatementTable.[DocumentSigned&Returned], PPARestatementTable.DateSignedDocsScannedFROM PPARestatementTable LEFT JOIN [PPARestatementTable Query] ON PPARestatementTable.[PPARestatementID] = [PPARestatementTable Query].[PPARestatementID]
WHERE ((([PPARestatementTable Query].PPARestatementID) Is Null));

[ItsMe]

So you are looking for records that do not have a value in PPARestatementID? Is that your Date field?

[cbende2]

No, I need to count the number of records for each field([DocumentCreation],[FormsCreation]...etc) that are null(this would be the # left to complete), and I need to count the number of records that are not null(this would be the # completed).

[ItsMe]

I would start with a query for the records that are Null. You will need to make a JOIN for each field you want to determine as Null, FormsCreation to FormsCreation etc. Then Continue with the Is Null criteria for each field you created a JOIN for, using the AND or OR where applicable.

[cbende2]

Ok so, I'm struggling to understand. But I kind of figured out a way. I'm going to make queries that will give me the count of records. How do I call the count of a query in my report? would it be =Count([QueryName]) or what? I'm getting an enter parameter error when I use that

[ItsMe]

You can create an alias and count a single field. In a Totals query you can add the following to a new field in the grid at the bottom of the window.
MyCount: [tblName].[DocumentCreation]

If you still do not have the Unmatched Records thing down, I suggest using the wizard again and read the prompts. Afterwards, add additional JOINS, as I suggested in post #9, for the remaining fields. Your additional JOINS should mimic the singular JOIN and Criteria created by the Wizard.

[cbende2]

Ok, I created a query, and it returns 350 records.

What will be the SQL for a text box that displays the number of records (350) from that query?

EDIT: Never mind, I figured it out. I'm just creating queries with the required criteria, and getting the count of the queries and adding those counts to my report. I now have about 15 queries, but it was really easy to do. I probably didn't do the most elegant way.

[ItsMe]

I doubt you will require 15 query objects to accomplish your task. If you feel like revisiting this at a later date, I may have more time, at that point, to provide you with some examples.