Query Difficulty

[jcc]

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have matches in
Field 3 (i.e. if multiple projects require a channel I get a list of all the
projects that have a match, which department they belong to, and on which
channel they match).

However, I want to be able to modify the query by Department so I can create
a more useful report. As an example, I want to be able to create a query for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same channel
then I don't need it, but if projects in Departments A, C & D all use the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view of the
matching channels (i.e. criteria where ="Department A" only showed me the
Department A projects, and not the projects from Departments C & D which used
the same distribution channel.)

Hope this was clear.

 

[TedMi]

A field which a list of umpteen items is NOT how Access is meant to be used.
You need to read up on normalization of data. It appears that your problem
domain has three entities: Projects, Departments, Distribution channels. You
need a table for each of these. Then you need intersection tables that
relate projects to departments and to channels.
-TedMi

 

[jcc]

Hi Ted:

Perhaps I was not sufficiently clear. I DO have a table for each of the
three entities - I have a table for projects, one for departments and one for
distribution channels. I have these all linked together in an intersection
table called "Link".

The query I am running is on the "Link" table. I did not think the other
information would be relevant, so I left it out - sorry about that.

So to reiterate - I want to return results of "Projects" which have a match
in "Channel", but I want to be able to do this for each "Department" so a
department head does not receive matches that are not relevant to him/her.

 

[Jeff Boyce]

If you're headed for a report, why not just include [Department] as one of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jcc]

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?

If you're headed for a report, why not just include [Department] as one of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of all
the
projects that have a match, which department they belong to, and on which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same channel
then I don't need it, but if projects in Departments A, C & D all use the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view of
the
matching channels (i.e. criteria where ="Department A" only showed me the
Department A projects, and not the projects from Departments C & D which
used
the same distribution channel.)

Hope this was clear.

 

[Jeff Boyce]

I think I have a better idea of what you're trying to do now.

So what if you "grouped by" the Channel, showing Departments?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?

If you're headed for a report, why not just include [Department] as one
of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have
matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of
all
the
projects that have a match, which department they belong to, and on
which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a
query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same
channel
then I don't need it, but if projects in Departments A, C & D all use
the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view
of
the
matching channels (i.e. criteria where ="Department A" only showed me
the
Department A projects, and not the projects from Departments C & D
which
used
the same distribution channel.)

Hope this was clear.

 

[John Spencer]

It sounds as if you need something like the following query.

SELECT *
FROM SomeTable
WHERE Channel in
(SELECT Channel
FROM SomeTable
WHERE Department = "Alpha")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I think I have a better idea of what you're trying to do now.

So what if you "grouped by" the Channel, showing Departments?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?

If you're headed for a report, why not just include [Department] as one
of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have
matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of
all
the
projects that have a match, which department they belong to, and on
which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a
query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same
channel
then I don't need it, but if projects in Departments A, C & D all use
the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view
of
the
matching channels (i.e. criteria where ="Department A" only showed me
the
Department A projects, and not the projects from Departments C & D
which
used
the same distribution channel.)

Hope this was clear.

 

[vanderghast]

Here a possible solution. You bring the table TWICE in the query designer,
one of it will get an _1 append to its name.

Join Table.Channel to Table_1.Channel.

Change the query to a Total query.

Bring Table.Department in the grid, keep the proposed GROUP BY
Bring Table_1.Projet in the grid, keep the proposed GROUP BY


That's all. (That SHOULD be all, if I understand correctly)

Vanderghast, Access MVP

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?

If you're headed for a report, why not just include [Department] as one
of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have
matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of
all
the
projects that have a match, which department they belong to, and on
which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a
query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same
channel
then I don't need it, but if projects in Departments A, C & D all use
the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view
of
the
matching channels (i.e. criteria where ="Department A" only showed me
the
Department A projects, and not the projects from Departments C & D
which
used
the same distribution channel.)

Hope this was clear.

 

[jcc]

Thank you for the suggestions, guys. I was away for a few days, but will be
revisiting this shortly. I will let you know how it goes!

Here a possible solution. You bring the table TWICE in the query designer,
one of it will get an _1 append to its name.

Join Table.Channel to Table_1.Channel.

Change the query to a Total query.

Bring Table.Department in the grid, keep the proposed GROUP BY
Bring Table_1.Projet in the grid, keep the proposed GROUP BY


That's all. (That SHOULD be all, if I understand correctly)

Vanderghast, Access MVP

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?

If you're headed for a report, why not just include [Department] as one
of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have
matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of
all
the
projects that have a match, which department they belong to, and on
which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a
query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same
channel
then I don't need it, but if projects in Departments A, C & D all use
the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view
of
the
matching channels (i.e. criteria where ="Department A" only showed me
the
Department A projects, and not the projects from Departments C & D
which
used
the same distribution channel.)

Hope this was clear.

 

[jcc]

Thanks for the suggestions, guys.

I was away for a few days and will be revisiting this shortly. I will see
if I am able to successfully implement your ideas. I will keep you posted.

It sounds as if you need something like the following query.

SELECT *
FROM SomeTable
WHERE Channel in
(SELECT Channel
FROM SomeTable
WHERE Department = "Alpha")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I think I have a better idea of what you're trying to do now.

So what if you "grouped by" the Channel, showing Departments?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the suggestion, Jeff.

This is what I tried, but the results yielded all matches for which a
project in Department A was involved. What I really want is probably best
demonstrated in an example:

Record|Project|Department|Channel

1| alpha|A| retail
2| beta| A| online
3| gamma| B| retail
4| delta| C| direct mail

I want a report for Department A which will return records 1 & 3, so that
department A understands their channel overlaps with another project, and
they are told which one it is.

The report which I believe you are suggesting, and which I tried, will
return only record 1 (i.e. the departmental project, but without the
corresponding project with which it overlaps.)

Can this be done?


:

If you're headed for a report, why not just include [Department] as one
of
the query output fields, then use the Grouping/Sorting in your report
definition to get a different 'section' for each Department?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is a brief description of my situation:

There is one table with about 150 records and three fields:
Field 1 = Project name (primary key)
Field 2 = Department (each project belongs to a department, but a
department
may have many projects. there are 5 departments)
Field 3 = Distribution channel (a list of 12 channels)

I have successfully generated a query to return records that have
matches
in
Field 3 (i.e. if multiple projects require a channel I get a list of
all
the
projects that have a match, which department they belong to, and on
which
channel they match).

However, I want to be able to modify the query by Department so I can
create
a more useful report. As an example, I want to be able to create a
query
for
Deparment A, so that it only generates matches in Field 3 which involve
Department A. (i.e. If projects in Department C & D use the same
channel
then I don't need it, but if projects in Departments A, C & D all use
the
same channel, then I want to see all three of those projects).

I tried using "Department" as a criteria, but this eliminated my view
of
the
matching channels (i.e. criteria where ="Department A" only showed me
the
Department A projects, and not the projects from Departments C & D
which
used
the same distribution channel.)

Hope this was clear.