Comparing counts and deriving a value

[Luigi_Cortisone]

Hi, hope someone can help

I'm fairly new to vba but I'm sure the answer is in here somewhere.

My table contains the following fields...

ConductorID - a conductor shares one or more (mostly 2) poles with other conductors.
Material - the pole material of each pole related to an individual conductor is stored with the conductor. Because most conductors have multiple poles the table contains duplicate conductor ids.

I have made a query from the table which groups material for each conductor. For most conductors the poles have the same material so there is only one record for the conductor. The pole material is derived as the one specific for that conductor

However where it gets a little more complex is where conductors have a variety of materials - any combination of concrete, wood and unknown.

I need to derive a material for each conductor based on the following logic.

The material with the highest count (eg if wood is '2' and concrete is '1') then the material for that conductor is 'wood'
If the count is the same for a conductor eg concrete is '1' and wood is '1' then the material is 'concrete'' OR if 'unknown' is '1' and wood is '1' the material is wood
If the only material for the conductor is 'unknown' OR 'unknown' is the highest count then the default is 'concrete'

The output will be a list of conductor records with their material derived from the above logic, ie one record only per conductor

I've uploaded a snapdhot of the dB with the source table and query

Many thanks

Cheers, Luigi

 

[jal]

Are those materials the only possibilities?

Wood, Concrete, Unknown

 

[jal]

What happens if a conductor has both concrete and wood - and both have a count of 2?

 

[Luigi_Cortisone]

Hi Jal

Concrete, wood and unknown are the only three values

If the count is the same for concrete and wood then the value is concrete

Cheers, Luigi

 

[jal]

Hi Jal

Concrete, wood and unknown are the only three values

If the count is the same for concrete and wood then the value is concrete

Cheers, Luigi

Ok, if I have time I'll put some more effort into this. I was need this info you just provided. Here's what I started. This query provides some kind of result set but doesn't handle tiebreakers. (That's why I needed to know that these are the only 3 values).


Select HighestCounts.*, AllTheCounts.Material as TheMaterial From
(
SELECT ConductorID, Max(CountOfMaterial) as HighestCount FROM
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial
FROM material
GROUP BY conductorid, material
)
Group By ConductorID
) as HighestCounts
INNER JOIN
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial
FROM material
GROUP BY conductorid, material
) as AllTheCounts
On AllTheCounts.ConductorID = HighestCounts.ConductorID
And AllTheCounts.CountOfMaterial = HighestCounts.HighestCount

 

[jal]

As far as handling tie breakers, I can probably assign some kind of a "priority" or "ranking" each of the 3 values.

Material....Points
Concrete....3
Wood........2
Unknown.....1

 

[jal]

Here's my first attempt at a full-blown solution (but be warned I don't usually get things right the first time, so do some testing/validation).

Select HighestCounts.*, IIF(AllTheCounts.Material = 'Unknown', 'Concrete', AllTheCounts.Material) as TheMaterial From
(
SELECT ConductorID, Max(Points) as HighestPoints FROM
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial, Count(Material) * IIF(Material = 'Concrete', 3, IIF(Material = 'Wood', 2, 1)) as Points
FROM material
GROUP BY conductorid, material
)
Group By ConductorID
) as HighestCounts
INNER JOIN
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial, Count(Material) * IIF(Material = 'Concrete', 3, IIF(Material = 'Wood', 2, 1)) as Points
FROM material
GROUP BY conductorid, material
) as AllTheCounts
On AllTheCounts.ConductorID = HighestCounts.ConductorID
And AllTheCounts.Points = HighestCounts.HighestPoints

 

[jal]

Woops, I didn't take into account this stipulation:

If the only material for the conductor is 'unknown' OR 'unknown' is the highest count then the default is 'concrete'

 

[jal]

Ok, i edited the first version - I had to rewrite the first line of the query to take into account that other stipulation. Hopefully this should work.

 

[jal]

I was just having doubts about my point system which, again, is this:

Material....Points
Concrete....3
Wood........2
Unknown.....1

Two unknowns should beat one wood but in my setup it probably ends up with a tie. This is confusing. I may have to rethink this.

 

[jal]

Proabbly the solution is to apply the point system only when the regular system has a tie. But as to how to code that....

 

[Luigi_Cortisone]

Yes two unknowns should produce a 'concrete'

I also discovered a few null values in the source table which shold default to concrete

Cheers, Luigi

 

[jal]

Well, maybe I can just change the values. The "tie" problem I just mentioned occurs at the point of common multiple. For example if the points are these:

Material....Points
Concrete....9
Wood........8
Unknown.....7

Then the common multiple for wood and unknown is 7 x 8 or 56. Therefore my system will fail at this point (i.e. 8 unknowns ties with 7 woods). One solution, then, would be to make sure that the common multiple is so high that it will never be reached in a real-life database. For example:

Material....Points
Concrete.... 7777777779
Wood.........7777777778
Unknown......7777777777

 

[jal]

The revised version, then (with the new point values) is this:


Select HighestCounts.*, IIF(AllTheCounts.Material = 'Unknown', 'Concrete', AllTheCounts.Material) as TheMaterial From
(
SELECT ConductorID, Max(Points) as HighestPoints FROM
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial, Count(Material) * IIF(Material = 'Concrete', 7777777779, IIF(Material = 'Wood', 7777777778,7777777777)) as Points
FROM material
GROUP BY conductorid, material
)
Group By ConductorID
) as HighestCounts
INNER JOIN
(
SELECT ConductorID, material, Count(Material) as CountOfMaterial, Count(Material) * IIF(Material = 'Concrete', 7777777779, IIF(Material = 'Wood', 7777777778,7777777777)) as Points
FROM material
GROUP BY conductorid, material
) as AllTheCounts
On AllTheCounts.ConductorID = HighestCounts.ConductorID
And AllTheCounts.Points = HighestCounts.HighestPoints

 

[jal]

I also discovered a few null values in the source table which shold default to concrete

Cheers, Luigi

Hmm...I'm wondering if you might want to handle nulls on your end, up front. For instance you could set the default value of that column to "Unknown". Or you could write an update query:

UPDATE Material SET Material = 'Unknown' WHERE Material IS NULL OR Len(Material & '') = 0

 

[Luigi_Cortisone]

Thanks, I'll handle the nulls as you suggest

testing now, cheers

 

[jal]

Hmmm.I think it's still not working. Not sure why as yet.

 

[jal]

False alarm, I was looking at the wrong conductor. Maybe it is working.

 

[jal]

Here's a good way to run tests. Empty the table and add rows for only ONE conductor - and try different combinations like this one:

Conductor Material
33.............wood
33............Unknown
33............Unknown

This produced "Concrete" as it should. Maybe I'll run a few more samples like that.

 

[jal]

I ran a few more tests as described in my last post. Seems to be working. Let me know if you find any discrepancies.