Help with Query in Access

Azhar.iq

Registered User.
Local time
Today, 21:03
Joined
Apr 10, 2015
Messages
18
Hi all,

I have a table which have following structure and data in it (Pasting as CSV)...

HolRef,CustRef,Resort,Hotel,Type,Out,Home,Cost,Paid
SH0001,1,Berlin,Berlin Heights,Full Board,1,2,1400,250
SH0002,2,Douglas,All Seasons,Half Board,7,8,421,170
SH0003,3,Alicante,Sol Melia Grande,All Inclusive,5,6,845,160
SH0004,1,DisneyWorld,Animal Kingdom Lodge,Full Board,3,24,6240,1240
SH0005,5,Kissimee,Highlands Reserve,Half Board,3,44,4255,850
SH0006,6,Istanbul,Carlton,All Inclusive,9,10,7008,1400
SH0007,2,DisneyWorld,Wilderness Lodge,Room Only,3,44,8706,1740
SH0008,8,Berlin,Grand Plaza,Room Only,1,2,860,170
SH0009,5,Milan,Rosa,Room Only,11,12,4712,940
SH0010,9,Warsaw,Hilton,Half Board,33,34,4512,900
SH0011,1,DisneyWorld,All Star Sports,All Inclusive,3,4,11000,1100
SH0012,10,DisneyWorld,Coranado Springs,Room Only,35,36,8622,1720
SH0013,11,Tokyo,Hilton,Full Board,15,16,5605,1120
SH0014,3,Madrid,Grande Lodge,All Inclusive,37,38,975,190
SH0015,12,DisneyWorld,All Star Movies,Half Board,35,24,4826,960
SH0016,13,Paris,Sienne Grand Royal,Half Board,41,42,15300,1530
SH0017,14,Orlando,Universal Mecca,Room Only,35,36,8900,1780
SH0018,15,Madrid,Sevilla,All Inclusive,37,38,6200,1240
SH0019,7,Peel,Glen Helen Inn,Room Only,7,8,630,120

The second column contains the Customer Ref, specifying which customer booked a holiday.

How do i make a query which will show me the Customers and other details, who have made a holiday booking more than once?
 
You will need a sub-query, let's call that sub1. It will be an aggregate query (click the Sigma/Summation symbol in the ribbon), bring down the CustRef field, then the CustRef field again. Next, underneath one of the fields change the 'Group By' to 'Count', then uner that, in the criteria section put >1.

That's sub1 and it tels you the CustRef values of everyone in your table more than one time. Now you build another query using your table and sub1, you think them via their CustRef fields and you show all the fields from your table. Run it and you have the data you want.
 

Users who are viewing this thread

Back
Top Bottom