Results 1 to 4 of 4
  1. #1
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10

    Making a new field in a query

    Okay - so you can probably guess by my subject line that I'm a newbie. Well, not really a newbie but someone who creates in Access only once in a blue moon so I forget a lot of how it works.

    I have a database where I record my projects. Projects are prioritized by both ship date and priority. In other words, a project can have a priority 1 but the ship date may be 6 months old, or a project can have a priority 1 with a ship date of tomorrow. I know how to query on this information. No problem.

    What I'd like to do is have my query report the order by number. I'm calling it Job Order. In other words, the order in which I need to do these projects. I may have several Priority 1s but because they have different ship dates, I need to know what to work on first.

    Basically, what I'd like it to magically do is autonumber the results from the query. That autonumber would tell me what to work on first. I don't know if Queries do autonumbering though! I tried to use Count but that just counts the record so I simply get the number 1 for each record.

    Once the query runs, it would look like this:

    Model 100
    Priority 1
    Ship Date 2/1/09
    Job Order 1

    Model 200
    Priority 1
    Ship Date 2/4/09


    Job Order 2

    Model 300
    Priority 2
    Ship Date 1/1/09
    Job Order 3

    and so on...

    Any help you can offer would be greatly appreciated!
    Anne

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    What about using sort?

    Select Ascending in the Sort row for Priority and Ship Date with the Priority column to the left of the Ship Date column to have Priority sorted before Ship Date.

  3. #3
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10
    Good idea but I'd really like to have a number. The lame reason? I would like to use this number in a form.

    Note - the Priority field contains numbers 1-5, while there may be 75 projects in the queue. If I had 9 projects with a priority of 1, which one would I do first? That's why I need a number separate from the Priority field. They are not always based on ship date either. Sometimes, they are based on "affects revenue" - which is another checkbox on the form (and in the query). It will help a lot if I can just put the resulting number on the form (for my end users of the form).

    Thanks!
    Anne

  4. #4
    mslieder is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2005
    Posts
    10
    More trouble than it's worth. I've given up on this idea and am running a simple query. The user will have to manually enter the Job Order.

    Thanks for your help!

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

Similar Threads

  1. Calculated Query Field used in different Query
    By cakdhooper in forum Queries
    Replies: 0
    Last Post: 11-21-2008, 03:07 PM
  2. query to find the highest value in a field
    By jhjarvie in forum Queries
    Replies: 0
    Last Post: 09-02-2008, 02:27 PM
  3. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 AM
  4. Replies: 0
    Last Post: 09-25-2006, 03:42 AM
  5. Form field not displaying in query
    By Valeda in forum Queries
    Replies: 2
    Last Post: 05-05-2006, 10:08 AM

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