DUPLICATE RECORDS IN QUERIES

[Guest]

I HAVE DUPLICATE RECORDS IN MY QUERY AND I NEED SOME HELP

TABLE 1 ...

FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7
5812 SHBD1 J. DOE 8000 CVN1 S. SMITH T. BROWN
5812 SHDB1 J. DOE 145000 VCS1 S. SMITH T.
BROWN


HOW CAN I SHRINK THIS UP TO ONE LINE AND SUM FIELD4

 

[John Spencer]

You could use a totals query, but you need to decide what you are going to
do about field 5 where you have CVN1 and VCS1 (which are not duplicate
values).
Do you need to show field 5 in the results?
If so, which value do you want to keep
-- just one of them it doesn't matter
-- the first in sort order
-- the last in sort order
-- ?? some other defining characteristic

An SQL statement would look something like:
SELECT Field1, Field2, Field3, Sum(Field4) as TotalAmount, Max(Field5) as
LowestInAlphabet, Field6, Field7
FROM TheTable
GROUP BY Field1, Field2, Field3, Field6, Field7


"PLEASE HELP ME. NOW !!!" <PLEASE HELP ME. NOW
[email protected]> wrote in message
news[email protected]...

 

[Tom Ellison]

Dear ???:

You can GROUP BY columns FIELD 1, 2, 3, and 6, but the values in FIELDs 4,
5, and 7 are DIFFERENT!

On each such column where there is different information, you may use any
aggreagate (SUM, MIN, MAX, etc.) or you may eliminate that column from the
results. Otherwise these rows will all show up individually. You can, as
you say, SUM FIELD4. What do you wish to do with 5 and 7?

Tom Ellison


"PLEASE HELP ME. NOW !!!" <PLEASE HELP ME. NOW
[email protected]> wrote in message
news[email protected]...

 

[Duane Hookom]

Create a totals/group by query like:

SELECT FIELD1, FIELD3, Sum(FIELD4) as SumField4, FIELD6
FROM [TABLE 1]
GROUP BY FIELD1, FIELD3,FIELD6;