Query on two fields with one output (1 Viewer)

[Courtman]

Hi,

I am trying to get a query to do the following. With this data:

Date - From - To
01-Jan-02 - Luton - Manchester
01-Jan-02 - Manchester - Luton
02-Jan-02 - Luton - Fenland
03-Jan-02 - Leicester - Luton

I want an output that would count the number of times I've visited Luton, Manchester etc. Previously I just worked on the departure airfield but this may not pick up an airfield if I've only LANDED there... The output should look as follows:

Airfield - First Visit - Last Visit - No. of Visits
Fenland - 02-Jan-02 - 02-Jan-02 - 1
Leicester - 03-Jan-02 - 03-Jan-02 - 1
Luton - 01-Jan-02 - 03-Jan-02 - 4
Manchester - 01-Jan-02 - 01-Jan-02 - 2

How do I make a query look at the From and To fields in the main table and come up with the above answer?

Thanks all!

 

[Jon K]

Try these two queries (type/paste each in the SQL View of a new query, replacing with the correct table name in the first query):-

qryOne:-
SELECT cdate(Date) as DateOfVisit, [From] as Airfield
FROM [TableName]
UNION ALL SELECT cdate(Date) as DateOfVisit, [To] as Airfield
FROM [TableName];


qryTwo:-
SELECT Airfield, format(min(DateOfVisit), "dd-mmm-yy") AS [First Visit],
format(max(DateOfVisit), "dd-mmm-yy") AS [Last Visit], Count(*) AS [Number of Visits]
FROM qryOne
GROUP BY Airfield;


Run the second query.