Query headache

[Guest]

Hi,

Sorry for poor title.

I have an app with the main table called 'files'. I also have an entities
table (read: contacts), and a roles table. The roles table is a list of jobs
(roles) an entity might fill in a given file. I also have a junction table
called fileEntityRole.

What I need is a report that lists every file and, IF there is an entity
playing one specific role (DOJ), then I want his name in the record as well.
If there is no such entity listed as playing the role DOJ for a given file,
then I STILL want a record for the file but I just want the field where the
DOJ entity would be to be left blank.

So, I have 10 files in the app, the result should be 10 records....some of
which have an entry for the DOJ field and some of which have a blank for the
DOJ field.

I've tried creating a query with the junction table, the roles table, and
the entities table, and then creating a query including THAT query along with
the Files table. That yields wither one record per file/entity/role defined,
OR if I change the join I can get one record per file/entity/role defined BUT
a MINIMUM of one entry per file (which means if a file doesn't have any
entity/roles defined, I still get one record based on that file.

If I use ="DOJ" in the [RoleName] field, instead of one of the two results I
get above, I get JUST a list of file/entity/roles where the role is "DOJ"
(along with the data from the related file. So, if I have 10 files and only
3 have DOJ roles defined in them, I get three records in the recordset.

Right now, I'm just trying to figure out how to get one record per file and
then a field with the name of a person, if any, playing the DOJ role.
Eventually I'll need to be able to distinguish between two (or more) people
playing the DOJ role in the same file.....one of which will be the 'current'
holder of that role (based on start and end dates) and the others being
'former' holders. I can't get there without first figuring out how to get
just one record per file with the DOJ role holder (if there is one) first.

Any direction? TIA.

 

[Amy Blankenship]

Hi,

Sorry for poor title.

I have an app with the main table called 'files'. I also have an entities
table (read: contacts), and a roles table. The roles table is a list of
jobs
(roles) an entity might fill in a given file. I also have a junction
table
called fileEntityRole.

What I need is a report that lists every file and, IF there is an entity
playing one specific role (DOJ), then I want his name in the record as
well.
If there is no such entity listed as playing the role DOJ for a given
file,
then I STILL want a record for the file but I just want the field where
the
DOJ entity would be to be left blank.

So, I have 10 files in the app, the result should be 10 records....some of
which have an entry for the DOJ field and some of which have a blank for
the
DOJ field.

I've tried creating a query with the junction table, the roles table, and
the entities table, and then creating a query including THAT query along
with
the Files table. That yields wither one record per file/entity/role
defined,
OR if I change the join I can get one record per file/entity/role defined
BUT
a MINIMUM of one entry per file (which means if a file doesn't have any
entity/roles defined, I still get one record based on that file.

If I use ="DOJ" in the [RoleName] field, instead of one of the two results
I
get above, I get JUST a list of file/entity/roles where the role is "DOJ"
(along with the data from the related file. So, if I have 10 files and
only
3 have DOJ roles defined in them, I get three records in the recordset.

Right now, I'm just trying to figure out how to get one record per file
and
then a field with the name of a person, if any, playing the DOJ role.
Eventually I'll need to be able to distinguish between two (or more)
people
playing the DOJ role in the same file.....one of which will be the
'current'
holder of that role (based on start and end dates) and the others being
'former' holders. I can't get there without first figuring out how to get
just one record per file with the DOJ role holder (if there is one) first.

What you have is called a "frustrated outer join." Essentially, the WHERE
clause is being applied to the result of the entire joined statement, thus
limiting the records to only ones where there are results = DOJ.

So what you have to do is a left or right join to a query where the WHERE
clause has already been applied. Probably the easiest way to do this is to
first build a query that selects FileEntityRoles where [RoleName] = "DOJ".
Then create a new query. When the query builder asks you what tables you
want to use, select the Files Table, the Entities table, and, instead of
your FileEntityRoles table, select the new query. Drag the joining fields
around to establish the relationships that will make the joins function
properly, and select your fields of interest.

You can also probably do it as a subquery.

HTH;

Amy

 

[Guest]

Thanks Amy!

All I had to do was put the criteria in the first query instead of the last
one and bingo!

CW

Hi,

Sorry for poor title.

I have an app with the main table called 'files'. I also have an entities
table (read: contacts), and a roles table. The roles table is a list of
jobs
(roles) an entity might fill in a given file. I also have a junction
table
called fileEntityRole.

What I need is a report that lists every file and, IF there is an entity
playing one specific role (DOJ), then I want his name in the record as
well.
If there is no such entity listed as playing the role DOJ for a given
file,
then I STILL want a record for the file but I just want the field where
the
DOJ entity would be to be left blank.

So, I have 10 files in the app, the result should be 10 records....some of
which have an entry for the DOJ field and some of which have a blank for
the
DOJ field.

I've tried creating a query with the junction table, the roles table, and
the entities table, and then creating a query including THAT query along
with
the Files table. That yields wither one record per file/entity/role
defined,
OR if I change the join I can get one record per file/entity/role defined
BUT
a MINIMUM of one entry per file (which means if a file doesn't have any
entity/roles defined, I still get one record based on that file.

If I use ="DOJ" in the [RoleName] field, instead of one of the two results
I
get above, I get JUST a list of file/entity/roles where the role is "DOJ"
(along with the data from the related file. So, if I have 10 files and
only
3 have DOJ roles defined in them, I get three records in the recordset.

Right now, I'm just trying to figure out how to get one record per file
and
then a field with the name of a person, if any, playing the DOJ role.
Eventually I'll need to be able to distinguish between two (or more)
people
playing the DOJ role in the same file.....one of which will be the
'current'
holder of that role (based on start and end dates) and the others being
'former' holders. I can't get there without first figuring out how to get
just one record per file with the DOJ role holder (if there is one) first.

What you have is called a "frustrated outer join." Essentially, the WHERE
clause is being applied to the result of the entire joined statement, thus
limiting the records to only ones where there are results = DOJ.

So what you have to do is a left or right join to a query where the WHERE
clause has already been applied. Probably the easiest way to do this is to
first build a query that selects FileEntityRoles where [RoleName] = "DOJ".
Then create a new query. When the query builder asks you what tables you
want to use, select the Files Table, the Entities table, and, instead of
your FileEntityRoles table, select the new query. Drag the joining fields
around to establish the relationships that will make the joins function
properly, and select your fields of interest.

You can also probably do it as a subquery.

HTH;

Amy