Access to SQL

Hello, How can I turn the following Access query into a SQL query?
Every time I try, I get Cartesian product
SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUNTR
Y
FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER =
dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
(QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
(QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER)
AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));Hi
This may be a data issue
Your query looks fine:
SELECT C.CLIENT_NUMBER,
C.LNAME1,
C.FNAME1,
C.INIT1,
C.LNAME2,
C.FNAME2,
C.INIT2,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.CITY,
A.STATE,
A.ZIPCODE,
A.COUNTRY
FROM QNameAddress1 Q
JOIN dbo_CLIENT C ON Q.CLIENT_NUMBER = C.CLIENT_NUMBER
JOIN dbo_ADDRXREF X ON Q.MaxOfPOLICY_DATE_TIME = X.POLICY_DATE_TIME AND
Q.POLICY_NUMBER = X.POLICY_NUMBER
JOIN dbo_ADDRESS A ON X.SEQUENCE_NUMBER = A.SEQUENCE_NUMBER AND
C.CLIENT_NUMBER = A.CLIENT_NUMBER
WHERE A.ADDR_USAGE='1'
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data (as insert statements). It may also be worth posting expected
output from sample data.
John
"Patrice" wrote:

> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUN
TRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
=
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER
)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>|||Plug that into the Query Analyzer and you should find the problem. Most
likely some or all of your INNER JOINS should be OUTER.
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:0D946FD4-B400-482F-8510-1AA01E1432BD@.microsoft.com...
> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE,
> dbo_ADDRESS.COUNTRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
> =
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER =
> dbo_ADDRESS.SEQUENCE_NUMBER)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>