Either Or

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay, I have a query that needs to pull the project number from a table. This
isn't the problem. I have some people working on two projects so I have two
fields, one called Current Project and one called Second Project.

What is the best way to have a query look to see which people are on a
project in the first field and then look to see if other people are listed in
the second project field and then provide my list of all people working on
the one project.

I am on project 125020 and 126007, right now my query is only giving me the
names of the people on both projects when what I'm looking for is a Project
Query that will give me everyone on 125020 and everyone on 126007 with my
name on both lists.
 
You could do this by using a Union Query. I don't know what other fields you
may need, but the basics would be:

SELECT [Person], [CurrentProject] FROM Actual_res_export
UNION ALL
SELECT [Person], [Second Project] FROM Actual_res_Export
WHERE [Second Project] IS NOT NULL;
 
I guess, I need to be a little clearer.

The query includes employee names, phone numbers and project addresses. Each
project has both a number and a name. Some people work on more than one
project at a time. The report I'm trying to generate is a report of each
employee working on each project. I work on project 125020 and project
126007. I am trying to create a query that would make sure that my name
appears on the list for both reports along with everyone else associated with
each project. I believe that I am trying to create an OR statement but I
haven't been qble to find one that works. I've tried a suggestion in another
post but the only name that comes up with project one and project 2 is mine.
It is only pulling out the names of people on two projects.

The query
--
Lori A. Pong


Klatuu said:
You could do this by using a Union Query. I don't know what other fields you
may need, but the basics would be:

SELECT [Person], [CurrentProject] FROM Actual_res_export
UNION ALL
SELECT [Person], [Second Project] FROM Actual_res_Export
WHERE [Second Project] IS NOT NULL;

Lori said:
Okay, I have a query that needs to pull the project number from a table. This
isn't the problem. I have some people working on two projects so I have two
fields, one called Current Project and one called Second Project.

What is the best way to have a query look to see which people are on a
project in the first field and then look to see if other people are listed in
the second project field and then provide my list of all people working on
the one project.

I am on project 125020 and 126007, right now my query is only giving me the
names of the people on both projects when what I'm looking for is a Project
Query that will give me everyone on 125020 and everyone on 126007 with my
name on both lists.
Click to expand...
Click to expand...
 
Hello Lori,

To accomplish this task ideally you will have three tables, a project
table, a staff table and a junction table that join the two.

The tblProject must contain a primary key that is linked to the
junction table. The same is true of the staff table, the junction
table contains just two fields, the two foreign keys.

Using this structure it is possible to create a many to many
relationship without creating a cartesian product.

The query that you will write shows just product details and staff
member details the junction table is only there to hold it all
together.

Hope this helps.

Good luck,

Nick
I guess, I need to be a little clearer.

The query includes employee names, phone numbers and project addresses. Each
project has both a number and a name. Some people work on more than one
project at a time. The report I'm trying to generate is a report of each
employee working on each project. I work on project 125020 and project
126007. I am trying to create a query that would make sure that my name
appears on the list for both reports along with everyone else associated with
each project. I believe that I am trying to create an OR statement but I
haven't been qble to find one that works. I've tried a suggestion in another
post but the only name that comes up with project one and project 2 is mine.
It is only pulling out the names of people on two projects.

The query
--
Lori A. Pong


Klatuu said:
You could do this by using a Union Query. I don't know what other fields you
may need, but the basics would be:

SELECT [Person], [CurrentProject] FROM Actual_res_export
UNION ALL
SELECT [Person], [Second Project] FROM Actual_res_Export
WHERE [Second Project] IS NOT NULL;

Lori said:
Okay, I have a query that needs to pull the project number from a table. This
isn't the problem. I have some people working on two projects so I have two
fields, one called Current Project and one called Second Project.

What is the best way to have a query look to see which people are on a
project in the first field and then look to see if other people are listed in
the second project field and then provide my list of all people working on
the one project.

I am on project 125020 and 126007, right now my query is only giving me the
names of the people on both projects when what I'm looking for is a Project
Query that will give me everyone on 125020 and everyone on 126007 with my
name on both lists.
Click to expand...
Click to expand...
Click to expand...
 
I should have added that really you should enforce referential
entegrity on the relationships.
Hello Lori,

To accomplish this task ideally you will have three tables, a project
table, a staff table and a junction table that join the two.

The tblProject must contain a primary key that is linked to the
junction table. The same is true of the staff table, the junction
table contains just two fields, the two foreign keys.

Using this structure it is possible to create a many to many
relationship without creating a cartesian product.

The query that you will write shows just product details and staff
member details the junction table is only there to hold it all
together.

Hope this helps.

Good luck,

Nick
I guess, I need to be a little clearer.

The query includes employee names, phone numbers and project addresses. Each
project has both a number and a name. Some people work on more than one
project at a time. The report I'm trying to generate is a report of each
employee working on each project. I work on project 125020 and project
126007. I am trying to create a query that would make sure that my name
appears on the list for both reports along with everyone else associated with
each project. I believe that I am trying to create an OR statement but I
haven't been qble to find one that works. I've tried a suggestion in another
post but the only name that comes up with project one and project 2 is mine.
It is only pulling out the names of people on two projects.

The query
--
Lori A. Pong


Klatuu said:
You could do this by using a Union Query. I don't know what other fields you
may need, but the basics would be:

SELECT [Person], [CurrentProject] FROM Actual_res_export
UNION ALL
SELECT [Person], [Second Project] FROM Actual_res_Export
WHERE [Second Project] IS NOT NULL;

:

Okay, I have a query that needs to pull the project number from a table. This
isn't the problem. I have some people working on two projects so I have two
fields, one called Current Project and one called Second Project.

What is the best way to have a query look to see which people are on a
project in the first field and then look to see if other people are listed in
the second project field and then provide my list of all people working on
the one project.

I am on project 125020 and 126007, right now my query is only giving me the
names of the people on both projects when what I'm looking for is a Project
Query that will give me everyone on 125020 and everyone on 126007 with my
name on both lists.
Click to expand...
Click to expand...
Click to expand...
 
Nick's analysis of you data structure is correct. You database is not well
normalized. That is the real reason you are having this problem. Redesigning
you database would, in the long run, make your life easier.

I fully understood what you were asking in your original post. Perhaps you
don't understand Union queries. They are more usually used to join data from
two or more tables into one recordset, the SQL I posted will create a
recordset that joins data from the one table so that either of the project
fields will be in one field. The resulting recordset will have two fields,
Person and CurrentProject. If you have a records in your table like this:

Person CurrentProject Second Project
Lori 125020 126007

What you will get in the recordset will be this

Person CurrentProject
Lori 125020
Lori 126007

To add additional fields to the recordset, you just have to include them on
both sides of the union.

This will include people who are woking on one project as well.

Lori said:
I guess, I need to be a little clearer.

The query includes employee names, phone numbers and project addresses. Each
project has both a number and a name. Some people work on more than one
project at a time. The report I'm trying to generate is a report of each
employee working on each project. I work on project 125020 and project
126007. I am trying to create a query that would make sure that my name
appears on the list for both reports along with everyone else associated with
each project. I believe that I am trying to create an OR statement but I
haven't been qble to find one that works. I've tried a suggestion in another
post but the only name that comes up with project one and project 2 is mine.
It is only pulling out the names of people on two projects.

The query
--
Lori A. Pong


Klatuu said:
You could do this by using a Union Query. I don't know what other fields you
may need, but the basics would be:

SELECT [Person], [CurrentProject] FROM Actual_res_export
UNION ALL
SELECT [Person], [Second Project] FROM Actual_res_Export
WHERE [Second Project] IS NOT NULL;

Lori said:
Okay, I have a query that needs to pull the project number from a table. This
isn't the problem. I have some people working on two projects so I have two
fields, one called Current Project and one called Second Project.

What is the best way to have a query look to see which people are on a
project in the first field and then look to see if other people are listed in
the second project field and then provide my list of all people working on
the one project.

I am on project 125020 and 126007, right now my query is only giving me the
names of the people on both projects when what I'm looking for is a Project
Query that will give me everyone on 125020 and everyone on 126007 with my
name on both lists.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top