Results 1 to 4 of 4

Thread: SQL in Access

  1. #1
    Join Date
    Jul 2003
    Location
    Breda, NL
    Posts
    2

    Unanswered: SQL in Access

    Hi everyone,

    I am having trouble with a query (I am a rookie) and was wondering if anyone can help. If this is not the correct forum then can somebody please tell me where I should go!

    (Firstly, does anyone know of a good source for information concerning the quirks of Access SQL?)


    Short description of problem.

    I have three tables that hold cruiseship itineraries>> itMain, itDate, itPorts


    ================================================== ====================

    itMain: Holds general information about cruise

    FIELDS: CID (ie cruiseID=primary key), ship, cruiseline, name of cruise
    DATA: 30, Nordic Empress, RCI, Western Caribbean

    itDate: Holds cruise dates

    FIELDS: CID, date (date cruise starts)
    DATA: 30, 10 May 2003


    itPorts: Holds itineray info

    FIELDS: CID, day, port, arrive, depart
    DATA: 30, 3, St Thomas, 7.00, 17.00

    ================================================== =====================

    Here is the link to the database itself
    http://home.planet.nl/~norto001/Itineraries.zip
    (it's in Access2000)

    What I want to do but have so far failed to achieve is find out which ships a ship will meet when docked.

    For example using the data above I know the Nordic Empress will be in St Thomas on 13 May 2003. What I want to know is which ships are in St Thomas on the same day. I would want to do this for a range of dates week, etc (this last bit is easy!).

    I have tried using nested queries etc, but have failed miserably.

    Is there anyone who would like to help?

    Thanks, Guy

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I created a query using this SQL.

    Let me know if that is what you want.

    SELECT itMain.CruiseName, itPorts.Day, itPorts.Port, itPorts.Country, itPorts.Arrive, itPorts.Depart
    FROM itMain INNER JOIN itPorts ON itMain.CID = itPorts.CID
    WHERE (((itPorts.Day)=[Enter Day]) AND ((itPorts.Port)=[Select Port]));

  3. #3
    Join Date
    May 2003
    Location
    Maryland
    Posts
    11

    Re: SQL in Access

    I think you want to Build a Query in the Design Wizard that looks like this.

    Select Main.CID Main.Ship Main.Date Ports.Day, Ports.ArriveTm Ports.Depart time, DtInPort:=Main.Date + Ports.Day, Port
    Order By DtInPort, Port


    Thats should return you a Datasheet, ordered by the Dates ships are in port and the Ports so It will be real easy to spot ships that are together.
    If the return is to large you might want to put in a where statement.
    WHERE DtInPort = MyDate

    and check through a few at a time.

    Now if you only want results where two ships are in the same port on the same day, I think you'll have to do some VBA under the hood to compare and delete unwanted records.

    Lemme know if this helps, Later BB

    Originally posted by GuyNorton
    Hi everyone,

    I am having trouble with a query (I am a rookie) and was wondering if anyone can help. If this is not the correct forum then can somebody please tell me where I should go!

    (Firstly, does anyone know of a good source for information concerning the quirks of Access SQL?)


    Short description of problem.

    I have three tables that hold cruiseship itineraries>> itMain, itDate, itPorts


    ================================================== ====================

    itMain: Holds general information about cruise

    FIELDS: CID (ie cruiseID=primary key), ship, cruiseline, name of cruise
    DATA: 30, Nordic Empress, RCI, Western Caribbean

    itDate: Holds cruise dates

    FIELDS: CID, date (date cruise starts)
    DATA: 30, 10 May 2003


    itPorts: Holds itineray info

    FIELDS: CID, day, port, arrive, depart
    DATA: 30, 3, St Thomas, 7.00, 17.00

    ================================================== =====================

    Here is the link to the database itself
    http://home.planet.nl/~norto001/Itineraries.zip
    (it's in Access2000)

    What I want to do but have so far failed to achieve is find out which ships a ship will meet when docked.

    For example using the data above I know the Nordic Empress will be in St Thomas on 13 May 2003. What I want to know is which ships are in St Thomas on the same day. I would want to do this for a range of dates week, etc (this last bit is easy!).

    I have tried using nested queries etc, but have failed miserably.

    Is there anyone who would like to help?

    Thanks, Guy
    K did you Flashy thing me, K??

  4. #4
    Join Date
    Jul 2003
    Location
    Breda, NL
    Posts
    2

    Re: SQL in Access

    Thanks for the replies....

    Not quite what I need...

    I want to find out the ships that a specified ship will meet up with over a range of dates.....

    Someone gave me this which works in SQL7 (using Carnival Destiny as example)

    SELECT TOP 1000 m.Ship, m.Line, d.[Date] + p.[Day] AS Dt, p.Port, x.ship AS Expr1, x.line AS Expr2
    FROM dbo.itMain m INNER JOIN
    dbo.itDates d ON m.CID = d.CID INNER JOIN
    dbo.itPorts p ON m.CID = p.CID LEFT OUTER JOIN
    (SELECT m.ship, m.line, d .[date], p.[day], p.port
    FROM itMain m INNER JOIN
    itDates d ON m.CID = d .CID INNER JOIN
    itPorts p ON m.CID = p.CID) x ON d.[Date] + p.[Day] = x.[date] + x.[day] AND p.Port = x.port AND m.Ship <> x.ship
    WHERE (m.Ship = 'Carnival Destiny')
    ORDER BY dt

    It works, but I would rather keep things in Access as SQL is a sledgehammer for my nutshell!

    Regards, Guy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •