Query by group (1 Viewer)

[amirkharkongor]

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 No		Name of Athlete	School/Association		Events Participated	Gold	Silver	Bronze	Number of Medal won by an Athlete
1​
2​
3​

 

[June7]

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.

 

[amirkharkongor]

I am uploading again in the earlier version

 

[amirkharkongor]

This a the sample data

AthleteName	SchoolAssociation	Gender	Event	Category	Gold	Silver	Bronze
SANCTIFY PALE	West Jaintia Hills District Sports Association	Male	800	Under - 18	1	0	0
SANCTIFY PALE	West Jaintia Hills District Sports Association	Male	1500	Under - 18	0	1	0
MELIBAD KRO	Ri Bhoi District Sports Association	Female	Javelin	Under - 16	0	1	0
MELIBAD KRO	Ri Bhoi District Sports Association	Female	Long Jump	Under - 16	1	0	0
SENITHA G. MOMIN	North Garo Hills District Association	Female	Javelin	Under - 18	1	0	0
FLEMING SOHKHWAI	Ri Bhoi District Sports Association	Male	Long Jump	Under - 18	1	0	0
Donbok Shabong	Shillong Sports Association	Male	400	Under - 18	1	0	0
Donbok Shabong	Shillong Sports Association	Male	200	Under - 18	0	1	0
Lyntijingkyrmcn K. Jahrin	Mawkyrwat District Sports Association	Female	800	Under - 16	1	0	0
Lyntijingkyrmcn K. Jahrin	Mawkyrwat District Sports Association	Female	2000	Under - 16	0	0	1
KYRSHANLANG SHYLLA	West Jaintia Hills District Sports Association	Male	400	Under - 23	1	0	0
Selbiana K.Wanniang	Mawkyrwat District Sports Association	Female	800	Under - 18	1	0	0
Balasiewdor Pathaw	Nongstoin District Sports Association	Female	1500	Under - 18	1	0	0
Hepstarli Lyngkhoi	Mawkyrwat District Sports Association	Male	5000	Open	1	0	0
Hepstarli Lyngkhoi	Mawkyrwat District Sports Association	Male	10000	Open	0	0	1
Batskhemlang Thongnirit	Mawkyrwat District Sports Association	Male	200	Under - 16	1	0	0
SILRIME M. SANGMA	Tura District Athletic Association	Female	High Jump	Under - 16	1	0	0
Neverson Jyrwa	Mawkyrwat District Sports Association	Male	2000	Under - 16	1	0	0
Pynhun Puwein	Nongstoin District Sports Association	Female	800	Under - 23	1	0	0
SKHEMLANG SUBA	Ri Bhoi District Sports Association	Male	1500	Under - 23	1	0	0
Embhahlang Lyngdoh	Shillong Sports Association	Male	400	Under - 16	1	0	0
TUSHAL SANGMA	North Garo Hills District Association	Male	Javelin	Under - 18	1	0	0
ROSA MASLAI	Ri Bhoi District Sports Association	Female	400	Under - 16	1	0	0
Angelina Jackson	Shillong Sports Association	Female	400	Under - 18	1	0	0
Angelina Jackson	Shillong Sports Association	Female	100	Under - 18	1	0	0

 

[June7]

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]

you can use Quer1 or Query2, whichever you like.