Two Out Of Three Ain't Bad...

Have sort of a strange request (I guess) and was wondering if it was
possible. Access 2003.

I have three tables imported from an Excel spreadsheet that have data
selected by three different people. What I am trying to do is create a
query that only includes those records that have been selected twice. I
have created a Union Query that shows who selected what, but what I am
trying to prevent is having to scroll down and manually determine which ones
were selected at least twice.

A little background:

1. List of tasks
2. List of Systems
3. Assign/Tie tasks to systems
4. If 2 of 3 folks tied Task 001 [Task#] to System 02 [Sys#], select it.
5. Continue through all 1500 tasks

Is this possible?

--
Bill Foley
Microsoft Office Specialist Master Instructor
www.pttinc.com

have you tried using the FindDuplicates selection in the Query Wizard, to
help you create a query?

hth


"Bill Foley" pttincatitexasdotnet wrote in message
...
Have sort of a strange request (I guess) and was wondering if it was
possible. Access 2003.

I have three tables imported from an Excel spreadsheet that have data
selected by three different people. What I am trying to do is create a
query that only includes those records that have been selected twice. I
have created a Union Query that shows who selected what, but what I am
trying to prevent is having to scroll down and manually determine which
ones
were selected at least twice.

A little background:

1. List of tasks
2. List of Systems
3. Assign/Tie tasks to systems
4. If 2 of 3 folks tied Task 001 [Task#] to System 02 [Sys#], select it.
5. Continue through all 1500 tasks

Is this possible?

--
Bill Foley
Microsoft Office Specialist Master Instructor
www.pttinc.com

Sounds doable, but you'll need to give us more details about the structure
of the data and tables that you imported so that we can see how the data are
related and what they contain.

--

Ken Snell
MS ACCESS MVP

"Bill Foley" pttincatitexasdotnet wrote in message
...
Have sort of a strange request (I guess) and was wondering if it was
possible. Access 2003.

I have three tables imported from an Excel spreadsheet that have data
selected by three different people. What I am trying to do is create a
query that only includes those records that have been selected twice. I
have created a Union Query that shows who selected what, but what I am
trying to prevent is having to scroll down and manually determine which
ones were selected at least twice.

A little background:

1. List of tasks
2. List of Systems
3. Assign/Tie tasks to systems
4. If 2 of 3 folks tied Task 001 [Task#] to System 02 [Sys#], select it.
5. Continue through all 1500 tasks

Is this possible?

--
Bill Foley
Microsoft Office Specialist Master Instructor
www.pttinc.com

1) You really need to get this all in one table. Your union query does
effectively do that, but it would be more efficient if you actually
imported the data into one table with an identifier.

Assuming your union query has Task# and Sys# as fields this can be done,
although I would strongly recommend you rename those fields to TaskNum
and SysNum.

SELECT [Task#], [Sys#]
FROM [Your Union Query]
Having Count([Sys#]) 1

That would list all task and system combinations that appear more than
once in the union query. By the way, you probably want to use UNION ALL
and not just UNION in the UNION query. Union returns unique records in
the entire set queries in the Union.

Example; Union query is returning only two fields Task and System
If the first query returned
Task 001 and System 02
And the second query returned
Task 001 and System 02
Then you would only see 1 record with Task 001 and System 02 if you use
UNION as the operator. If you use UNION ALL, then you would see 2 rows.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Bill Foley wrote:
Have sort of a strange request (I guess) and was wondering if it was
possible. Access 2003.

I have three tables imported from an Excel spreadsheet that have data
selected by three different people. What I am trying to do is create a
query that only includes those records that have been selected twice. I
have created a Union Query that shows who selected what, but what I am
trying to prevent is having to scroll down and manually determine which ones
were selected at least twice.

A little background:

1. List of tasks
2. List of Systems
3. Assign/Tie tasks to systems
4. If 2 of 3 folks tied Task 001 [Task#] to System 02 [Sys#], select it.
5. Continue through all 1500 tasks

Is this possible?

Thanks John. That worked a peach!

--
Bill Foley
Microsoft Office Specialist Master Instructor
www.pttinc.com
"John Spencer" wrote in message
...
1) You really need to get this all in one table. Your union query does
effectively do that, but it would be more efficient if you actually
imported the data into one table with an identifier.

Assuming your union query has Task# and Sys# as fields this can be done,
although I would strongly recommend you rename those fields to TaskNum and
SysNum.

SELECT [Task#], [Sys#]
FROM [Your Union Query]
Having Count([Sys#]) 1

That would list all task and system combinations that appear more than
once in the union query. By the way, you probably want to use UNION ALL
and not just UNION in the UNION query. Union returns unique records in
the entire set queries in the Union.

Example; Union query is returning only two fields Task and System
If the first query returned
Task 001 and System 02
And the second query returned
Task 001 and System 02
Then you would only see 1 record with Task 001 and System 02 if you use
UNION as the operator. If you use UNION ALL, then you would see 2 rows.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Bill Foley wrote:
Have sort of a strange request (I guess) and was wondering if it was
possible. Access 2003.

I have three tables imported from an Excel spreadsheet that have data
selected by three different people. What I am trying to do is create a
query that only includes those records that have been selected twice. I
have created a Union Query that shows who selected what, but what I am
trying to prevent is having to scroll down and manually determine which
ones were selected at least twice.

A little background:

1. List of tasks
2. List of Systems
3. Assign/Tie tasks to systems
4. If 2 of 3 folks tied Task 001 [Task#] to System 02 [Sys#], select it.
5. Continue through all 1500 tasks

Is this possible?