Distinctrow

[Ann]

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"));

 

[Jerry Whittle]

"doesn't work"? How exactly isn't it working as you expect? Returning dupes?
Not returning all the expected records?

 

[John Spencer]

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]

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

"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.

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"));

 

[Bob Barrows]

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.

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

"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.

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"));