trouble with query

I have a database (Access 2000) that holds ship itinerary information and a list of businesses at ports that are visited

I want to get a list businesses at the ports a ship visits over a period of time:

Eg. Find all businesses in the ports that the ‘Carnival Destiny’ visits over the next fortnight.

---------------------------------------------------------------------------------
TABLES:

ItMain:

CID ‘ primary key – Cruise ID
Ship ‘ name of ship

ItDates :

CID
Date ‘ dates an itinerary is repeated on e.g. 7-7-03, 7-14-03 etc

ItPorts:

CID
Day ‘ cruise day (seven day cruise 0-1-2-3-4-5-6)
Port ‘ port that ship visits


BizMain:

BID ‘ primary key - Business ID
Port ‘ port that business is located in

-----------------------------------------------------------------------------------

I thought this would be easy to solve, even with my poor knowledge of SQL.

Alas, No! My subquery refused to cooperate!

Can anyone post a solution for me, or even point me in the right direction (so that I can have the pleasure of solving it myself)

Thanks in advance,

Guy Norton

Join your tables with an INNER JOIN. Then use the WHERE clause to specify the date range.

Example:

SELECT i.CID, i.Ship
FROM itMain As i
INNER JOIN ItDates As d ON d.CID = i.CID
INNER JOIN ItPorts As p ON p.CID = i.CID
INNER JOIN BizMain As b on b.Port = p.Port
WHERE d.[Date] = '12/12/2001'

Hope this helps.

Hi Dcarva,
Thanks
It works under SQL server (added bid to it)
I'm trying to get it to work in Access but it's non-standard implementation of SQL doesnt want to know!

Thanks for the help
Cheers, Guy

P.S. I am only interested in getting the BID's. Is it possible to return them 'DISTINCT'...and stop my ASP script from looping and checking for dupes?

Yes. Distinct will return unique records.

---------
Hi Dcarva,
Thanks
It works under SQL server (added bid to it)
I'm trying to get it to work in Access but it's non-standard implementation of SQL doesnt want to know!

Thanks for the help
Cheers, Guy

P.S. I am only interested in getting the BID's. Is it possible to return them 'DISTINCT'...and stop my ASP script from looping and checking for dupes?

re DISTINCT
thanks!
(I say witha little embarrassment!)
Guy