Query

[dr223]

Hi,

I have a query which arranges the following businnestypescombined;

SELECT QryCalcWPM.A.BusinessType & "/" & QryCalcWPM.B.BusinessType AS BusinessTypeCombined, QryCalcWPM.Score
FROM QryCalcWPM
ORDER BY QryCalcWPM.Score DESC;

Results:

BusinessCombined Score
KIOSK/MPESA1.03
MPESA/TAXI0.98
KIOSK/TAXI1.02
MPESA/TUKTUK1.19
TAXI/TUKTUK1.21
KIOSK/TUKTUK1.23

By the above result:

KIOSK/MPESA 1.03 means "KIOSK is better in ranking than MPESA",
MPESA/TAXI 0.98 means "MPESA is not better-off in ranking than TAXI"
etc


Therefore, KIOSK/MPESA > 1 then KIOSK was better in ranking than MPESA and if it was < 1 then it would have been the vice.

so now;

I want to write a query to rank the 4 business types with the above logic; So the results will be;

If we use this inferential for all, we conclude (it should display the results as):-

KIOSK
TAXI
MPESA
TUKTUK


assume we have 50 businesstypes - the query should accomodate that..

Any help please..

Thanks

 

[pbaldy]

Offhand, I'd think you could have a calculated column:

IIf(QryCalcWPM.Score > 0, QryCalcWPM.A.BusinessType, QryCalcWPM.B.BusinessType)

Which should give you the "winner" from each record. You can group/count on that field which should give you your ranking.

 

[dr223]

How can I do that please?

 

[dr223]

any help please to formulate this query - am working with Access 2003.

 

[pbaldy]

What have you got so far? Did you add that formula as a calculated column?

 

[dr223]

Yes, I did change the query to;

SELECT IIf([QryCalcWPM].[Score]>0,[QryCalcWPM].[A].[BusinessType],[QryCalcWPM].[B].[BusinessType]) AS BusinessTypeCombined, QryCalcWPM.Score[/B]
[B]FROM QryCalcWPM[/B]
[B]ORDER BY QryCalcWPM.Score DESC; [/B]

Results Based on the initial query:

BusinessCombined Score
KIOSK/MPESA0.97
MPESA/TAXI0.97
KIOSK/TAXI0.95
MPESA/TUKTUK1.21
TAXI/TUKTUK1.25
KIOSK/TUKTUK1.18


Results after the change
BusinessTypeCombinedScore
TAXI1.25
MPESA1.21
KIOSK1.18
KIOSK0.97
MPESA0.97
KIOSK0.95


There are ONLY 4 business types I expect to be showing and the one with the highest with comparison to the other should take priority.

So am expecting a result as;


Taxi
Mpesa
Kiosk
Tuktuk

Thanks

 

[pbaldy]

I'm curious why you didn't try what I posted. Can you post the db here?

 

[dr223]

pbadly - I tried what u suggested ..

SELECT IIf([QryCalcWPM].[Score]>0,[QryCalcWPM].[A].[BusinessType],[QryCalcWPM]..[BusinessType]) AS BusinessTypeCombined, QryCalcWPM.Score
FROM QryCalcWPM
ORDER BY QryCalcWPM.Score DESC;

The database is to huge and sensitive to attach.. any hints

 

[pbaldy]

And did it produce the list of "winners"? If so:

SELECT BusinessTypeCombined, Count(*) AS HowMany
FROM ThatOtherQuery
GROUP BY BusinessTypeCombined

should produce a list of how many times each was the winner.

 

[dr223]

ThatOtherQuery - gives wrong results.

 SELECT [COLOR=red]IIf([QryCalcWPM].[Score]>0,[QryCalcWPM].[A].[BusinessType],[QryCalcWPM].[B].[BusinessType]) AS BusinessTypeCombined, QryCalcWPM.Score[/B][/COLOR]
[B][B]FROM QryCalcWPM[/B][/B]
[B][B]ORDER BY QryCalcWPM.Score DESC;  [/B][/B]

As with the below data;

BusinessCombined Score
KIOSK/MPESA0.97
MPESA/TAXI0.97
KIOSK/TAXI0.95
MPESA/TUKTUK1.21
TAXI/TUKTUK1.25
KIOSK/TUKTUK1.18

We expect the results to be;

TAXI1.25
MPESA1.21
KIOSK1.18
MPESA0.97 (since it is < 1 then MPESA is better off in ranking to KIOSK)
TAXI0.97 (since it is < 1 then TAXI is better off in ranking MPESA)
TAXI0.95 (since it is < 1 then TAXI is better off in ranking KIOSK)

The results from the query is;

TAXI1.25
MPESA1.21
KIOSK1.18
KIOSK0.97
MPESA0.97
KIOSK0.95

This is the initial error we have with the query. Any way I can amend it to resolve this issue?



Based on the above ranking, we exepect the results to be;


Taxi (is better of in ranking to MPESA, Kiosk, Tuktuk)
Mpesa (is better of in ranking to Kiosk and Tuktuk)
Kiosk (is better of in ranking to Tuktuk)
Tuktuk

Thanks

 

[pbaldy]

This would be much easier with some data to play with. Can you export the base table to a new db and post that, deleting any sensitive info?

 

[dr223]

Ok - attached is a sample of the database.

Please shade some light for me - the problem is in QryWPMScores2

If you open QryCalcWPM2 you'll notice the;

A.BusinessType B.BusinessType Score
KIOSK MPESA 0.97
MPESA TAXI 0.97
KIOSK TAXI 0.95
MPESA TUKTUK 1.21
TAXI TUKTUK 1.25
KIOSK TUKTUK 1.18


This means;

KIOSK - MPESA = 0.97 meaning (MPESA is better in ranking than KIOSK) since 0.97 < 1
MPESA - TAXI = 0.97 meaning ( TAXI is better in ranking than MPESA)
KIOSK - TAXI = 0.95 meaning ( TAXI is better in ranking than KIOSK)
MPESA - TUKTUK = 1.21 meaning ( MPESA is better in ranking than TUKTUK) since 1.21 > 1
TAXI - TUKTUK = 1.25 meaning (TAXI is better in ranking than TUKTUK)
KISOK - TUKTUK = 1.18 meaning (KIOSK is better in ranking than TUKTUK)

Now, follow up the above ranking the 4 businesses will be;

1) TAXI
2) MPESA
3) KIOSK
4) TUKTUK

Hope am clearer..

Thank you so much

 

[pbaldy]

There was a logical flaw in the formula, it should be:

IIf([QryCalcWPM2].[Score]>1,[QryCalcWPM2].[A].[BusinessType],[QryCalcWPM2]..[BusinessType])

 

[dr223]

Tried as suggested;

SELECT IIf([QryCalcWPM2].[Score]>1,[QryCalcWPM2].[A].[BusinessType],[QryCalcWPM2].[B].[BusinessType]) AS BusinessType
FROM QryCalcWPM2
ORDER BY QryCalcWPM2.Score DESC;

Result I received was:

BusinessType

TAXI
MPESA
KIOSK
MPESA
TAXI
TAXI

I want the query to yield;

TAXI
MPESA
KIOSK
TUKTUK

Any help please...

Thank you

 

[dr223]

Someone out there should be able to help...

Thanks

 

[pbaldy]

SELECT IIf([QryCalcWPM2].[Score]>1,[QryCalcWPM2].[A].[BusinessType],[QryCalcWPM2]..[BusinessType]) AS BusinessTypeCombined, Count(*) AS TotalCount
FROM QryCalcWPM2
GROUP BY IIf([QryCalcWPM2].[Score]>1,[QryCalcWPM2].[A].[BusinessType],[QryCalcWPM2]..[BusinessType])
ORDER BY Count(*) DESC;

 

[dr223]

Thanks pbaldy-

Result;

BusinessTypeCombined TotalCount
TAXI 3
MPESA 2
KIOSK 1



Nearly there - I dont get the businesstype TUKTUK as the last type available. Why?

Thanks

 

[pbaldy]

Because they were never a winner.

 

[dr223]

Ok - fine but I still need to have that at the last of the list.. Its a business type and I cant ignore it at all. If there any approach I could change the query to consider this.

There will always be 1 business type that will not be a winner and I want it to be at the bottom of the list..

Thanks again for all your input.. Much appreciation

 

[pbaldy]

We're coming back to what I suggested earlier. Make the first query just a base:

SELECT IIf([QryCalcWPM2].[Score]>1,[QryCalcWPM2].[A].[BusinessType],[QryCalcWPM2]..[BusinessType]) AS BusinessTypeCombined, QryCalcWPM2.Score
FROM QryCalcWPM2

Then create another based on that one:

SELECT TblBusTyp.BusinessType, Count(QryWPMScores2.Score) AS CountOfScore
FROM TblBusTyp LEFT JOIN QryWPMScores2 ON TblBusTyp.BusinessType = QryWPMScores2.BusinessTypeCombined
GROUP BY TblBusTyp.BusinessType
ORDER BY Count(QryWPMScores2.Score) DESC;