Trying to generate a report on missing information

[lance.schaeffer]

I have spent way too many hours on this, and would appreciate any help
you can give.

I have Four tables. We shall call them:

tblForms, tblOrgs, tblPrograms, tblFormsReceived

My end goal is to have a report which will output the name of every
Organization's form's NOT received, in a give "program"

The tables are structured as follows:

tblOrgs houses information about individual Organizations.
tblPrograms houses information about programs org's participate in.

tblOrgs is the many side of tblPrograms.

tblForms is a list of all the Forms available to be turned in

tblFormsReceived tracks the forms received from organizations, for a
given program.

Thus, if Org1, Participating in Program1, turns in Form1, then a record
exists in tblFormsReceived holding each of these pieces of information,
as well as the date and any notes.

I have tried various approaches, none of which I've gotten right.

so basically, I need to find way to output all forms that exist in
tblForm, that don't exist in tblFormReceived, given that the Org exists
in tblPrograms, and then have this work for EACH Org in tblPrograms for
the specified Record.

Sorry for the length, and the fact that it probably sounds like
jibberish. I am more than happy to clarify anything.

thanks,

Lance

 

[Marshall Barton]

I have spent way too many hours on this, and would appreciate any help
you can give.

I have Four tables. We shall call them:

tblForms, tblOrgs, tblPrograms, tblFormsReceived

My end goal is to have a report which will output the name of every
Organization's form's NOT received, in a give "program"

The tables are structured as follows:

tblOrgs houses information about individual Organizations.
tblPrograms houses information about programs org's participate in.

tblOrgs is the many side of tblPrograms.

tblForms is a list of all the Forms available to be turned in

tblFormsReceived tracks the forms received from organizations, for a
given program.

Thus, if Org1, Participating in Program1, turns in Form1, then a record
exists in tblFormsReceived holding each of these pieces of information,
as well as the date and any notes.

I have tried various approaches, none of which I've gotten right.

so basically, I need to find way to output all forms that exist in
tblForm, that don't exist in tblFormReceived, given that the Org exists
in tblPrograms, and then have this work for EACH Org in tblPrograms for
the specified Record.

The key to creating a report is to first create a query that
retrieves the needed data from the tables. Unfortunately, I
don't think you provided the critical information needed to
create the query - each table's primary and foreign keys.

The general idea is to use an outer join from the Forms
table to the other tables. This will allow you to use a
where clause that only accepts records with a Null in the
FormsReceived table,

 

[lance.schaeffer]

Marsh,

Thanks for your reply.

When I tried doing this, the problem was that in a situation where I am
looking at "forms not recieved" for multiple organizations (OrgID = 1,2
for example), I would only get a list of "forms not received" overall,
not for each instance of the Organization. Thus, if Org 1 turned in
form 1, and org 2 turned in form 2, then I would only get back forms
3,4 missing (assuming there are only 4 forms to be turned in).

I am able to get the outer join to work properly when the query is only
on a single Org, but the instant there are two+ orgs, it doesn't work
properly.

Perhaps there is a way to get the query to redo-itself in each instance
of a subform for each organization? This is the only thing I can think
of.

 

[Marshall Barton]

When I tried doing this, the problem was that in a situation where I am
looking at "forms not recieved" for multiple organizations (OrgID = 1,2
for example), I would only get a list of "forms not received" overall,
not for each instance of the Organization. Thus, if Org 1 turned in
form 1, and org 2 turned in form 2, then I would only get back forms
3,4 missing (assuming there are only 4 forms to be turned in).

I am able to get the outer join to work properly when the query is only
on a single Org, but the instant there are two+ orgs, it doesn't work
properly.

Perhaps there is a way to get the query to redo-itself in each instance
of a subform for each organization? This is the only thing I can think
of.

 

[Marshall Barton]

When I tried doing this, the problem was that in a situation where I am
looking at "forms not recieved" for multiple organizations (OrgID = 1,2
for example), I would only get a list of "forms not received" overall,
not for each instance of the Organization. Thus, if Org 1 turned in
form 1, and org 2 turned in form 2, then I would only get back forms
3,4 missing (assuming there are only 4 forms to be turned in).

I am able to get the outer join to work properly when the query is only
on a single Org, but the instant there are two+ orgs, it doesn't work
properly.

Perhaps there is a way to get the query to redo-itself in each instance
of a subform for each organization? This is the only thing I can think
of.

You probably need some additional criteria or, more likely,
the joins are not quite right. I can't tell without seeing
the query that you tried.