Query by group (1 Viewer)

Local time
Today, 08:56
Joined
Mar 23, 2022
Messages
45
Hi friends,

I have uploaded one sample database.

I want to query the Medal Winners of different Athlete in the following format but i have no idea how to do that. Please help me.

Sl NoName of AthleteSchool/AssociationEvents ParticipatedGoldSilverBronzeNumber of Medal won by an Athlete
1​
2​
3​
 

Attachments

  • sample.accdb
    1 MB · Views: 138

June7

AWF VIP
Local time
Yesterday, 19:26
Joined
Mar 9, 2014
Messages
5,503
I use Access 2010 and cannot open your db because a later Access version is required. Must be using some features that Access 2010 cannot handle. Maybe someone else will come along.

Otherwise, post a version that is compatible with 2010 or post data in raw table structure.
 
Local time
Today, 08:56
Joined
Mar 23, 2022
Messages
45
I am uploading again in the earlier version
 

Attachments

  • test.accdb
    1.3 MB · Views: 135
Local time
Today, 08:56
Joined
Mar 23, 2022
Messages
45
This a the sample data
AthleteNameSchoolAssociationGenderEventCategoryGoldSilverBronze
SANCTIFY PALEWest Jaintia Hills District Sports AssociationMale800Under - 18100
SANCTIFY PALEWest Jaintia Hills District Sports AssociationMale1500Under - 18010
MELIBAD KRORi Bhoi District Sports AssociationFemaleJavelinUnder - 16010
MELIBAD KRORi Bhoi District Sports AssociationFemaleLong JumpUnder - 16100
SENITHA G. MOMINNorth Garo Hills District AssociationFemaleJavelinUnder - 18100
FLEMING SOHKHWAIRi Bhoi District Sports AssociationMaleLong JumpUnder - 18100
Donbok ShabongShillong Sports AssociationMale400Under - 18100
Donbok ShabongShillong Sports AssociationMale200Under - 18010
Lyntijingkyrmcn K. JahrinMawkyrwat District Sports AssociationFemale800Under - 16100
Lyntijingkyrmcn K. JahrinMawkyrwat District Sports AssociationFemale2000Under - 16001
KYRSHANLANG SHYLLAWest Jaintia Hills District Sports AssociationMale400Under - 23100
Selbiana K.WanniangMawkyrwat District Sports AssociationFemale800Under - 18100
Balasiewdor PathawNongstoin District Sports AssociationFemale1500Under - 18100
Hepstarli LyngkhoiMawkyrwat District Sports AssociationMale5000Open100
Hepstarli LyngkhoiMawkyrwat District Sports AssociationMale10000Open001
Batskhemlang ThongniritMawkyrwat District Sports AssociationMale200Under - 16100
SILRIME M. SANGMATura District Athletic AssociationFemaleHigh JumpUnder - 16100
Neverson JyrwaMawkyrwat District Sports AssociationMale2000Under - 16100
Pynhun PuweinNongstoin District Sports AssociationFemale800Under - 23100
SKHEMLANG SUBARi Bhoi District Sports AssociationMale1500Under - 23100
Embhahlang LyngdohShillong Sports AssociationMale400Under - 16100
TUSHAL SANGMANorth Garo Hills District AssociationMaleJavelinUnder - 18100
ROSA MASLAIRi Bhoi District Sports AssociationFemale400Under - 16100
Angelina JacksonShillong Sports AssociationFemale400Under - 18100
Angelina JacksonShillong Sports AssociationFemale100Under - 18100
 

June7

AWF VIP
Local time
Yesterday, 19:26
Joined
Mar 9, 2014
Messages
5,503
That is not exactly the raw data table. You do not have a field for each medal type. That is the output you want which would normally be produced by a CROSSTAB query. With your data the output will result in 649 records. There are 650 records in AthleteEvent table. Record 577 in AthleteEvent has no data so it is dropped because of the INNER JOIN.

Consider:
TRANSFORM Nz(Count([EventID]),0) AS CountEvent
SELECT AthleteData.AthleteName, AthleteEvent.SchoolAssociation, AthleteEvent.Event, Count(AthleteEvent.EventID) AS TotalEvent
FROM AthleteData INNER JOIN AthleteEvent ON AthleteData.AthleteID = AthleteEvent.AthleteID
GROUP BY AthleteData.AthleteName, AthleteEvent.SchoolAssociation, AthleteEvent.Event
PIVOT AthleteEvent.MedalType;

I don't know why this produces two fields that count the Null MedalType records. Don't recall ever seeing that before.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:26
Joined
May 7, 2009
Messages
19,249
you can use Quer1 or Query2, whichever you like.
 

Attachments

  • test.accdb
    1.3 MB · Views: 149

Users who are viewing this thread

Top Bottom