Report of completion status of records in bundle

[Jean-Francois Gauthier]

I want to have a report that shows a bundle number, and the total amount of
records in the bundle, and then showing the completed records in the bundle
and the incomplete records in the bundle. Is there a way to do this?

I tried to make a query for all the records which does a count of the
records per bundle. This works. I then made another query for all records
in a bundle with complete status. This works. Then made a query for all
the records in a bundle that are incomplete. Also works.

When I join them together, I get duplicate information rather then just one
line per bundle showing the total of records, then complete records and
incomplete.

Thanks,

 

[John W. Vinson]

I want to have a report that shows a bundle number, and the total amount of
records in the bundle, and then showing the completed records in the bundle
and the incomplete records in the bundle. Is there a way to do this?

I'd suggest a Report with two Subreports - one for the complete, one for the
incomplete.

Since the individual records in the bundle aren't associated with one another,
trying to join the two queries won't give you the desired results.

John W. Vinson [MVP]

 

[Jean-Francois Gauthier]

Hi John,

Thanks for the reply. So if I get what you are saying, there is absolutely
no way to have the information aligned together in a report such as in an
excel format type?

Meaning:

Fields:

Bundle# TotalnumberofDocs DocsCompleted Docsincomplete

thanks,

Jean-Francois

 

[John W. Vinson]

Thanks for the reply. So if I get what you are saying, there is absolutely
no way to have the information aligned together in a report such as in an
excel format type?

Meaning:

Fields:

Bundle# TotalnumberofDocs DocsCompleted Docsincomplete

Well... maybe. I'm not sure of the structure of your table, but - assuming
that the table upon which this query is based contains a Bundle# field, a Doc
field (many docs per bundle), and a field that indicates completion - you
don't say, so I'm guessing here! - then you can use a Totals query. Include
the Bundle# field, the Doc field, and two calculated fields:

IsComplete: IIF([complete] = True, 1, 0)
IsIncomplete: IIF([complete] = False, 1, 0)
where [complete ] is the field or expression which indicates that a given doc
is complete. Make it a Totals query; group by Bundle#, Count the Docs field,
and Sum the IsComplete and IsIncomplete fields.

John W. Vinson [MVP]

 

[Jean-Francois Gauthier]

Hi John,

Thanks, that helps a lot and I will try that.

In the meantime I was able to get what I needed from using subreports and
doing counts from that as you suggested earlier....it is just harder
afterwards to get summary info such at totals of all documents, totals of
completes, totals of incompletes...but I will get to that....I will try your
solution though in the meantime.


Thanks again for your help.

Thanks for the reply. So if I get what you are saying, there is absolutely
no way to have the information aligned together in a report such as in an
excel format type?

Meaning:

Fields:

Bundle# TotalnumberofDocs DocsCompleted Docsincomplete

Well... maybe. I'm not sure of the structure of your table, but - assuming
that the table upon which this query is based contains a Bundle# field, a Doc
field (many docs per bundle), and a field that indicates completion - you
don't say, so I'm guessing here! - then you can use a Totals query. Include
the Bundle# field, the Doc field, and two calculated fields:

IsComplete: IIF([complete] = True, 1, 0)
IsIncomplete: IIF([complete] = False, 1, 0)
where [complete ] is the field or expression which indicates that a given doc
is complete. Make it a Totals query; group by Bundle#, Count the Docs field,
and Sum the IsComplete and IsIncomplete fields.

John W. Vinson [MVP]