Results 1 to 5 of 5
  1. #1
    captgnvr is offline Advanced Beginner
    Windows 2K Access 97
    Join Date
    Sep 2009
    Posts
    44

    How to sort by three sort orders

    Dear All



    Have one table with information of crew.

    crew keep joining and leaving.

    The table is sorted according to rank-code, like master as 1 and steward as say 40; sorted by the joining date. Also have a field for disembarking date.


    I am having problem in doing this bcos:

    a) once I sort in the query according to rank-code and then by joining-date; it invariably keeps the three men joined as seaman all together bcos of the rank-code and unable to get the three seaman to be at the end of the last month crew.

    What is needed is :

    1. query to have list of crew for the month in such a way, all those who have disembarked to be in the same order as the last month; all the new joiners for the month to be listed below the last month crew onboard; in such a way the list will show all the crew for the current month brought forward from last month plus the list of new crew at the bottom.

    2. why such a table is required is bcos this crew list is linked to one excel where each persons stores purchased record is maintained. Against the names the entries are made and it will be easier to just add new crew members at the bottom and continue filling the purchase quantities and amount.


    Pls help

    Brgds/captgnvr

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Can you provide a simple list of how you would expect the list to be sorted and a list of how it is coming out now.

    David

  3. #3
    captgnvr is offline Advanced Beginner
    Windows 2K Access 97
    Join Date
    Sep 2009
    Posts
    44
    D/Dcrake

    Thanks for the response.

    In short I am trying to have the crewlist which
    In access I have a simple table with all the crew names and their info like joining date, their code and ranks.

    When I click on command button, I can take out a print out of all active crew list as on today. The sort is by rank-code where master is on top of the list and steward at the bottom of list. Till here no problems.

    Now I also have some stores items which is sold to them. I initially tried to do in access with all the store items like beers, soft drinks etc but unable to figure out how to make the store list connected to the crew list. I posted here and got some guidance but unable to go with it. So I decided when I go on leave, refer to the books I have and try again.

    In the mean time I thought I will link the excell sheet to the access crew list data to take the crew names.

    The problem I am encountering is, say I enter the purchase items in excel as per the names from access; in that month that particular crew might sign off and new names will crop up. Since it is getting the latest list, the name is different in the excel sheet.

    So what is ideal for me is to have the list of crew who are active not to change but to add all the new joiners for the month at the bottom of the present list.

    Since I am sorting it out by rank-code, it gets sorted out including the new joiner whereas I want all the new joiner together.

    for your information: I have a filed to show 'a' and 's' whereby I get active crew list by calling for 'a' in the query. sorry it has become a long explanation.

    something like-- all crew for the present month who have been onboard last month plus new joiners appended at the bottom of the list.

    brgds/captgnvr

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Why could you not get you transactions to work? All you need is a new transactions table that was linked to the crew table using the primary key in the crew

    TblCrew:
    CrewID:Autonumber (PK)
    RankID:Foreign Key > Ranks Table
    JoinDate: Date/Time
    CrewMemberName:Text
    ....

    TblTrans
    TransId:Autonumber (PK)
    TransDate: Date/Time
    CrewID: Foreign Key > PK in Crew Table
    ItemId : Foreign Key > PK in Consumables Table
    ItemQty: Number
    ItemCost:Currency
    ....

    TblConsumables:
    ItemId:Autonumber (PK)
    ItemName: Text
    ItemCost:Currency
    ....


    This would be a one to many relationship between CrewID and PK in crew table.

    You could then base you sales form these transactions.

    David

  5. #5
    captgnvr is offline Advanced Beginner
    Windows 2K Access 97
    Join Date
    Sep 2009
    Posts
    44
    D/Dcrake

    Just rcvd your directions; will make each table and try again. I would have sent the file but it is more than 1mb bcos of other matter in that.

    I am due to leave the ship on 20th this month. I thought do this before I leave but looks like time running out. The problem is do not get uninteruptd time due to sailing, cargo work etc. In any case I will quickly try and come back with the result.

    brgds/captgnvr

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sort Order Variation with nulls
    By jonsuns7 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 03:10 AM
  2. Macro to Run RemoveFilter/Sort
    By Ryan in forum Access
    Replies: 0
    Last Post: 08-30-2009, 01:41 AM
  3. Sort
    By mmp97 in forum Access
    Replies: 1
    Last Post: 12-18-2008, 10:14 PM
  4. Replies: 0
    Last Post: 03-26-2007, 02:12 AM
  5. Newbie(sort of) Subform help
    By rowman in forum Forms
    Replies: 0
    Last Post: 04-03-2006, 06:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums