L
LarryWestMCSD
I just upsized my database from Access 2003 to SQL Server 2000.
Before, when I ran a simple join query [see below] it worked fine. Now,
when I run the same query (the only difference being that the tables are
linked to SQL Server as opposed to a different Access database), it will
return multiple copies of the first result. For example, if the query would
have returned 35 unique records, now it just returns the first record 35
times! The problem also occurs in Access 2002, but not if I downgrade and
run the application using Access 97. The query also works properly if I use
the View designer in SQL Server Enterprise manager to create the same query
as the Query Designer in Access.
Any suggestions as to what I am doing wrong and how to fix it?
Larry J. West, MCSD, MOUS, FLMI, ACS
P.S. If it matters, this program was originally written in Access 97, and
was convertedt o Access 2003 in order to take advantage of improved security.
Going backto 97 to stay is not an option.
Query is below [some fields removed to shorten it]:
SELECT Customer.CaseNo, Customer.LastName, Customer.FirstName,
DateDiff('yyyy',(Customer!DOB),Date())-IIf(Month(Date())<Month(Customer!DOB),1,0)-IIf(Month(Date())=Month(Customer!DOB)
And Day(Date())<Day(Customer!DOB),1,0) AS Age, Customer.DOB, Customer.Gender,
Table2.Date, Table2.Time, Table2.Unit, Table2.Comments
FROM Table2 INNER JOIN Customer ON Table2.CaseNo = Customer.CaseNo;
P.S. Due to HIPPA regulations I would be unable to send a copy of the
database itself.
Before, when I ran a simple join query [see below] it worked fine. Now,
when I run the same query (the only difference being that the tables are
linked to SQL Server as opposed to a different Access database), it will
return multiple copies of the first result. For example, if the query would
have returned 35 unique records, now it just returns the first record 35
times! The problem also occurs in Access 2002, but not if I downgrade and
run the application using Access 97. The query also works properly if I use
the View designer in SQL Server Enterprise manager to create the same query
as the Query Designer in Access.
Any suggestions as to what I am doing wrong and how to fix it?
Larry J. West, MCSD, MOUS, FLMI, ACS
P.S. If it matters, this program was originally written in Access 97, and
was convertedt o Access 2003 in order to take advantage of improved security.
Going backto 97 to stay is not an option.
Query is below [some fields removed to shorten it]:
SELECT Customer.CaseNo, Customer.LastName, Customer.FirstName,
DateDiff('yyyy',(Customer!DOB),Date())-IIf(Month(Date())<Month(Customer!DOB),1,0)-IIf(Month(Date())=Month(Customer!DOB)
And Day(Date())<Day(Customer!DOB),1,0) AS Age, Customer.DOB, Customer.Gender,
Table2.Date, Table2.Time, Table2.Unit, Table2.Comments
FROM Table2 INNER JOIN Customer ON Table2.CaseNo = Customer.CaseNo;
P.S. Due to HIPPA regulations I would be unable to send a copy of the
database itself.