Almost duplicate rows

[Zonie32]

I have a table that has fields of ZipCode, Network and Factor.

The Network names are duplicated, which is fine, and also some have the same ZipCode, but the factors are either 0 or 53.

So example:

Network 3digZipCode Factor
ABC Inc. 995 0
ABC Inc. 995 53
ABC Inc. 997 0
ABC Inc. 998 0

I want just the first 2 lines pulled into my query, because they have the same Zip, but 2 different Factors. I need to find all those Networks that have same Zip with those 2 factors.

Anyone?? I am using MS Access 2007 and I am using the Select Query in Design View, not SQL. Although, I'm not opposed to SQL statements!

 

[PHV]

find all those Networks that have same Zip with those 2 factors

SELECT Network, [3digZipCode]
FROM yourTable
GROUP BY Network, [3digZipCode]
HAVING Count(*)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zonie32]

Thank you for the quick response! Was very helpful.

Now, another question...going another route here...how would I write the query to say...

Give me ALL the rows with zeros but if there is the same zipcode and network that has a ZERO on one line and a 53 on the other line, I just need the 53 for those specific rows.

So basically, I need all zeros, except when the duplicate row shows a zero and 53, then I just need the 53 for that specific network.

sorry, this is hard to explain...

 

[PHV]

SELECT Network, [3digZipCode], Max(Factor) AS theFactor
FROM yourTable
GROUP BY Network, [3digZipCode]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zonie32]

PHV,

I ran this and it didn't quite give me the results I need. Here is what I get: What I need from this is if the Network and Zip are the same but the factors are 0 and 53, I only need the 53. But I do need the other networks as well that may only have 0's.

Network Zip theFactor
First Health Network 10 0
First Health Network 10 53
First Health Network 11 0
First Health Network 11 53
First Health Network 12 0
First Health Network 12 53
First Health Network 13 0
First Health Network 13 53

 

[lameid]

Apparently your Network and 3digZipCode are not exactly the same in all rows if you see them repeated from PHV's statement. My guess would be trailing spaces in the Network field.

 

[Zonie32]

They all appear to be the same, no extra spaces or anything. There are other columns as well...maybe that is the difference.

There is a Date field, an LOB field and a State field. The dates can be different on the rows too.

 

[PHV]

So, what is YOUR sql code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zonie32]

The LOB field actually has the 0 and 53's in it, not Factor field like I previously had. Sorry about that.


SELECT MASTER.Network, MASTER.[Zip], Max(MASTER.LOB) AS theLOB, MASTER.Date, MASTER.St, MASTER.PPOID, MASTER.Factor
FROM MASTER
GROUP BY MASTER.Network, MASTER.[Zip], MASTER.LOB, MASTER.Date, MASTER.St, MASTER.PPOID, MASTER.Factor;

 

[PHV]

This is CLEARLY not that I suggested (and not that was asked)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zonie32]

Sorry. Things got changed around, field names changed, fields were added....