Complex IIf expressions suggestions. 1

[ciarra41]

mntID Cstn Cusid Reid Renam
1 32241 2224141 ton
2 32241 2224141 Me
3 32241 2274144 6245 Me


How could I use the illustration above to show: If you have the same [Cstn] and [Cusid] with one or more entry and the [Reid} value is null delete any other entries for that same [Cstn] without the same [Reid] and [Cusid] and the [Reid] is not null. So looking at the illustration I would delete row 3.

 

[AlexCuse]

I think first what you need to do is identify the Cstn/Cusid pairings that you need to look at (that have >1 entry containing NULL Reid). You can do that with this query:

SELECT Cstn, CusID, count(Cstn) AS CNT
FROM [TABLE]
WHERE Reid is null
GROUP BY Cstn, CusID
HAVING count(Cstn) > 1;

In my example, I saved this query as qryTest.

You can then inner join to this query to delete what you need to delete (at least for this limited sample data)

DELETE DISTINCTROW TABLE.*
FROM [TABLE] 
INNER JOIN qryTest AS b 
ON TABLE.Cstn=b.Cstn
WHERE TABLE.Reid Is Not Null;

HOpe this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[ciarra41]

How about if both [Cstn] and [Cusid] is >1 and [Renam] = to "ton"?
Both [Cstn] and [Cusid] has to have more than vaule not one or the other.

 

[AlexCuse]

Your requirements are confusing me. Do you want to differentiate rows based on Renam now as well?

I think maybe what you want is to change the first query to this?

SELECT Cstn, count(Cstn) AS CNT
FROM [TABLE]
WHERE Reid is null
GROUP BY Cstn
HAVING count(Cstn) > 1;

Make sure you have a backup before you test this.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ciarra41]

Oops, Your first example worked just fine have a star. The other part is another question I had.

How about if both [Cstn] and [Cusid] is >1 and [Renam] = to "ton"?
Exp: Both [Cstn] and [Cusid] has to have more than one of the same vaule not one or the other whereas [Renam] = to "ton".

 

[AlexCuse]

Ah, now I see what you mean. You would just need to add an additional where clause to that initial query that you're joining to in your delete. Like this:

SELECT Cstn, CusID, count(Cstn) AS CNT
FROM [TABLE]
WHERE Reid is null
[b]and Renam = 'ton'[/b]
GROUP BY Cstn, CusID
HAVING count(Cstn) > 1;

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ciarra41]

Almost...

[Reid] has nothing to do it this time. Only if both fields [Cstn] and [Cusid] has more than one of the same value whereas [Renam] = "ton".

 

[AlexCuse]

Ah, my mistake

SELECT Cstn, CusID, count(Cstn) AS CNT
FROM [TABLE]
WHERE Renam = 'ton'
GROUP BY Cstn, CusID
HAVING count(Cstn) > 1;

Possibly?

Ignorance of certain subjects is a great part of wisdom

 

[ciarra41]

what about [Cusid] should I count that too?

SELECT Cstn, CusID, count(Cstn)AS CNT, count (CusID)AS CNTID
FROM

WHERE Renam = 'ton'
GROUP BY Cstn, CusID
HAVING count(Cstn) > 1;

Should this work, remember if both fields [Cstn] and [CusID] has more that one value.

 

[AlexCuse]

Because of your GROUP BY:

GROUP BY Cstn, CusID

Your count is only going to be for combinations of Cstn and CusID

For example, say you have this data:

Cstn CusID
1 5
2 3
7 9
1 5
7 8
2 3
2 3

here is what you will get for your counts:

Cstn CusID CNT
1 5 2
2 3 3
7 9 1
7 8 1

Thinking about it like this might help you to understand what you really want the coun of. Is it Cstn's per CusID or vice versa?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

Here's some info about aggregates and GROUP BY that may be helpful:

An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman     SaleDate     Amount
JR           1/1/2006     $500.00
EM           1/1/2006     $250.00
JR           1/1/2006     $100.00
EM           1/2/2006     $101.00
JR           1/2/2006     $75.00
JR           1/2/2006     $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR        $775.00
EM        $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006   850.00
1/2/2006   276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM    1/1/2006    250.00
EM    1/2/2006    101.00
JR    1/1/2006    600.00
JR    1/2/2006    175.00

So, yes, by adding additional fields to your select, you will have different SUMs.

Can you see what the "GROUP BY" is doing now?  It's GROUPING together like information and performing the aggregate function in the SELECT on that GROUP.