Distinctrow

I'm trying to get only the distinct rows for the following but it doesn't
work. What am I doing wrong? Thanks for any help you can provide.

SELECT DISTINCTROW tblDRSJobs.txtDRSMonth, tblDRSJobs.txtDRSNumber,
Nz(([curFirstClassPostageRate]*[sngQuantity])+[curDeliveryCost],0) AS
curUSPSPostageCost, Nz([curUSPSPostageCost]-[curPostageStatementCost]) AS
[DiscountedPostageCost/Savings], tblDRSJobsByOperators.txtMachineType INTO
[tblDiscountedPostageCost/Saving]
FROM tblDRSJobs INNER JOIN tblDRSJobsByOperators ON tblDRSJobs.txtDRSNumber
= tblDRSJobsByOperators.txtDRSNumber
WHERE (((tblDRSJobsByOperators.txtMachineType)="SeCap" Or
(tblDRSJobsByOperators.txtMachineType)="Video Jet" Or
(tblDRSJobsByOperators.txtMachineType)="Offline" Or
(tblDRSJobsByOperators.txtMachineType)="DA95"));

"doesn't work"? How exactly isn't it working as you expect? Returning dupes?
Not returning all the expected records?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ann" wrote:

I'm trying to get only the distinct rows for the following but it doesn't
work. What am I doing wrong? Thanks for any help you can provide.

SELECT DISTINCTROW tblDRSJobs.txtDRSMonth, tblDRSJobs.txtDRSNumber,
Nz(([curFirstClassPostageRate]*[sngQuantity])+[curDeliveryCost],0) AS
curUSPSPostageCost, Nz([curUSPSPostageCost]-[curPostageStatementCost]) AS
[DiscountedPostageCost/Savings], tblDRSJobsByOperators.txtMachineType INTO
[tblDiscountedPostageCost/Saving]
FROM tblDRSJobs INNER JOIN tblDRSJobsByOperators ON tblDRSJobs.txtDRSNumber
= tblDRSJobsByOperators.txtDRSNumber
WHERE (((tblDRSJobsByOperators.txtMachineType)="SeCap" Or
(tblDRSJobsByOperators.txtMachineType)="Video Jet" Or
(tblDRSJobsByOperators.txtMachineType)="Offline" Or
(tblDRSJobsByOperators.txtMachineType)="DA95"));

Perhaps you want DISTINCT instead of DistinctRow.

"...doesn't work..." is not very descriptive. It does not tell us if you are
getting the wrong results ( too many records, not enough records, no records)
or a syntax error of some kind, etc.

My basic question is why are you doing this make table query in the first
place. It is almost always (some exceptions do exist) the wrong choice to do
so. You are usually better off just using a select query to return the
results you need and use that as if it were a table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ann wrote:
I'm trying to get only the distinct rows for the following but it doesn't
work. What am I doing wrong? Thanks for any help you can provide.

SELECT DISTINCTROW tblDRSJobs.txtDRSMonth, tblDRSJobs.txtDRSNumber,
Nz(([curFirstClassPostageRate]*[sngQuantity])+[curDeliveryCost],0) AS
curUSPSPostageCost, Nz([curUSPSPostageCost]-[curPostageStatementCost]) AS
[DiscountedPostageCost/Savings], tblDRSJobsByOperators.txtMachineType INTO
[tblDiscountedPostageCost/Saving]
FROM tblDRSJobs INNER JOIN tblDRSJobsByOperators ON tblDRSJobs.txtDRSNumber
= tblDRSJobsByOperators.txtDRSNumber
WHERE (((tblDRSJobsByOperators.txtMachineType)="SeCap" Or
(tblDRSJobsByOperators.txtMachineType)="Video Jet" Or
(tblDRSJobsByOperators.txtMachineType)="Offline" Or
(tblDRSJobsByOperators.txtMachineType)="DA95"));

It repeats all the duplicate and triplicate records. I only want to see the
entire row once.

"Jerry Whittle" wrote:

"doesn't work"? How exactly isn't it working as you expect? Returning dupes?
Not returning all the expected records?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ann" wrote:

I'm trying to get only the distinct rows for the following but it doesn't
work. What am I doing wrong? Thanks for any help you can provide.

SELECT DISTINCTROW tblDRSJobs.txtDRSMonth, tblDRSJobs.txtDRSNumber,
Nz(([curFirstClassPostageRate]*[sngQuantity])+[curDeliveryCost],0) AS
curUSPSPostageCost, Nz([curUSPSPostageCost]-[curPostageStatementCost]) AS
[DiscountedPostageCost/Savings], tblDRSJobsByOperators.txtMachineType INTO
[tblDiscountedPostageCost/Saving]
FROM tblDRSJobs INNER JOIN tblDRSJobsByOperators ON tblDRSJobs.txtDRSNumber
= tblDRSJobsByOperators.txtDRSNumber
WHERE (((tblDRSJobsByOperators.txtMachineType)="SeCap" Or
(tblDRSJobsByOperators.txtMachineType)="Video Jet" Or
(tblDRSJobsByOperators.txtMachineType)="Offline" Or
(tblDRSJobsByOperators.txtMachineType)="DA95"));

Perhaps your definition of "distinct" isn't quite what the DISTINCT and
DISTINCTROW keywords are designed to provide. Could you show us some
examples of the duplicate records?

You can save us some time by providing the source records that produced the
resulting duplicates.


Ann wrote:
It repeats all the duplicate and triplicate records. I only want to
see the entire row once.

"Jerry Whittle" wrote:

"doesn't work"? How exactly isn't it working as you expect?
Returning dupes? Not returning all the expected records?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ann" wrote:

I'm trying to get only the distinct rows for the following but it
doesn't work. What am I doing wrong? Thanks for any help you can
provide.

SELECT DISTINCTROW tblDRSJobs.txtDRSMonth, tblDRSJobs.txtDRSNumber,
Nz(([curFirstClassPostageRate]*[sngQuantity])+[curDeliveryCost],0)
AS curUSPSPostageCost,
Nz([curUSPSPostageCost]-[curPostageStatementCost]) AS
[DiscountedPostageCost/Savings],
tblDRSJobsByOperators.txtMachineType INTO
[tblDiscountedPostageCost/Saving]
FROM tblDRSJobs INNER JOIN tblDRSJobsByOperators ON
tblDRSJobs.txtDRSNumber = tblDRSJobsByOperators.txtDRSNumber
WHERE (((tblDRSJobsByOperators.txtMachineType)="SeCap" Or
(tblDRSJobsByOperators.txtMachineType)="Video Jet" Or
(tblDRSJobsByOperators.txtMachineType)="Offline" Or
(tblDRSJobsByOperators.txtMachineType)="DA95"));

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"