Access 97 Query Involving Selecting Distinct Field Values

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

Guest

Hello,
I currently have an Access 97 database and would like to accomplish the
following in a query :
I would like to select records from a table called Customer Order Details
where the field DO is the same but the field Warehouse is different.
Is this possible? Thanks in advance.

Sincerely,

James Simpson
Straightway Technologies Inc.
 
Hello,
I currently have an Access 97 database and would like to accomplish the
following in a query :
I would like to select records from a table called Customer Order Details
where the field DO is the same but the field Warehouse is different.
Is this possible? Thanks in advance.
Click to expand...

Where the field DO is the same as... what???

Guessing here, you want to compare multiple records in the table with
one another. You can do this with a Self Join. Add the table to the
query grid TWICE. Join the two instances by DO. Access will alias the
second instance of the table by appending _1 to its name; put a
criterion on [Table_1].[Warehouse] of
.[Warehouse]
Click to expand...


Select whatever fields you want to see.

John W. Vinson[MVP]
 
Hello again,
I tried using your advice of adding the two tables and joining them
together based on DO, (I performed an inner join). However, upon looking at
the data I receive the error "Syntax error on join operation". In addition,
your reply does not answer my initial question which was how to create a
query that selects records that have the same DO but different warehouse
number. To better illustrate what I am talking about consider the following
table that contains two fields, DO and Warehouse, the following is the data I
would be looking at:

DO Warehouse
********************
1234 55
1234 66
1234 89
1235 33
1235 33
1235 33

As you can see from here, with this query it should return the the first
three values in the table as the DO number for the first three are the same,
but the warehouse numbers are not. The remaining DOs (with the 1235) would
not be returned in the query as they all have the same warehouse number. Is
it possible to script a query that could do that? Thanks in advance.

Sincerely,

James Simpson
Straightway Technologies Inc.



John Vinson said:
Hello,
I currently have an Access 97 database and would like to accomplish the
following in a query :
I would like to select records from a table called Customer Order Details
where the field DO is the same but the field Warehouse is different.
Is this possible? Thanks in advance.
Click to expand...

Where the field DO is the same as... what???

Guessing here, you want to compare multiple records in the table with
one another. You can do this with a Self Join. Add the table to the
query grid TWICE. Join the two instances by DO. Access will alias the
second instance of the table by appending _1 to its name; put a
criterion on [Table_1].[Warehouse] of
.[Warehouse]
Click to expand...


Select whatever fields you want to see.

John W. Vinson[MVP]
Click to expand...
 
using your field names, and calling the table "tblCustomerOrderDetails", and
adding a primary key field called DetailID, i came up with the following
query that weeds out multiple "matching" records with identical DO values
*and* identical Warehouse values. it also excludes single records where the
DO value appears only once in the table.

SELECT tblCustomerOrderDetails.DO, Warehouse, First(DetailID) AS DetailIDpk
FROM tblCustomerOrderDetails INNER JOIN (SELECT tblCustomerOrderDetails.DO
FROM tblCustomerOrderDetails
GROUP BY tblCustomerOrderDetails.DO
HAVING Count(Warehouse)>1) AS A ON tblCustomerOrderDetails.DO = A.DO
GROUP BY tblCustomerOrderDetails.DO, Warehouse
HAVING Count(DetailID)=1;

hth


James Simpson said:
Hello again,
I tried using your advice of adding the two tables and joining them
together based on DO, (I performed an inner join). However, upon looking at
the data I receive the error "Syntax error on join operation". In addition,
your reply does not answer my initial question which was how to create a
query that selects records that have the same DO but different warehouse
number. To better illustrate what I am talking about consider the following
table that contains two fields, DO and Warehouse, the following is the data I
would be looking at:

DO Warehouse
********************
1234 55
1234 66
1234 89
1235 33
1235 33
1235 33

As you can see from here, with this query it should return the the first
three values in the table as the DO number for the first three are the same,
but the warehouse numbers are not. The remaining DOs (with the 1235) would
not be returned in the query as they all have the same warehouse number. Is
it possible to script a query that could do that? Thanks in advance.

Sincerely,

James Simpson
Straightway Technologies Inc.



John Vinson said:
Hello,
I currently have an Access 97 database and would like to accomplish the
following in a query :
I would like to select records from a table called Customer Order Details
where the field DO is the same but the field Warehouse is different.
Is this possible? Thanks in advance.
Click to expand...

Where the field DO is the same as... what???

Guessing here, you want to compare multiple records in the table with
one another. You can do this with a Self Join. Add the table to the
query grid TWICE. Join the two instances by DO. Access will alias the
second instance of the table by appending _1 to its name; put a
criterion on [Table_1].[Warehouse] of
.[Warehouse]
Click to expand...


Select whatever fields you want to see.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top