Results 1 to 8 of 8
  1. #1
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Totaling Values and Maintaining the Order of Entry

    I have a table that lists job number/part number/quantity and I use a counter field to keep this data in the order that it was enter into the table for reports. Some jobs have multiple entries of the same part number and quantity. I use a query to sort all of the part number for a job and a total to combine the quantities for similar part numbers. This creates a list of unique mark numbers and totals the quantities.

    I would like to keep the order of the part numbers in he same order as they were entered. Is there some way I can include my counter in the total query to get this done?

    I understand some of the consecutive numbers will drop out but that is not an issue. Want to keep same order for reporting.

    Thanks,

    Greg

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How about applying the MIN function to your Counter field in your Aggregate Query?

  3. #3
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    I added the counter field to my query but it lists each entry separately and no longer totals the similar part numbers. Do I need to create another query to get this accomplished?

    Thanks,

    Greg

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, you shouldn't need to.
    But you do need to change the "Group By" value on the Totals line for this field to "Min".

  5. #5
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    I like solutions like that!!!!

    Thanks or your help Worked great.

    Greg

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

    Aggregate Queries make sense logically, if you understand what they are doing.
    Basically, you have two categories of fields:
    1. Group By fields
    2. Aggregated fields

    The query will "Group" all records by all the fields that are indicated by "Group By" on the Totals row in Design View. So, it will combine all duplicate records when all the "Group By" fields are taken into consideration.
    Then, it will apply the Aggregate function to each "grouping" (whether it be Count, Sum, Min, Max, etc) for that particular field.

    Hope that help clarifies things a bit for you.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Want to keep same order for reporting.
    If you mean Access reports, then AFAIK, reports ignore query sorting in favour of report grouping and sorting - so your efforts may be for naught.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by GregShah View Post
    I would like to keep the order of the part numbers in he same order as they were entered.
    a) You can have an autonumeric primary key for job (details) table, and order your report by it;
    b) You can have a datetime field in you table(s), where date and time when the record was created will be stored (users aren't allowed to edit it), and you order your report by this/those field(s).

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

Similar Threads

  1. Replies: 3
    Last Post: 02-12-2017, 05:37 AM
  2. Replies: 1
    Last Post: 09-28-2016, 05:36 PM
  3. Replies: 8
    Last Post: 04-23-2015, 08:36 PM
  4. How do you Show Totaling Values on a Form?
    By Access_Headaches in forum Forms
    Replies: 0
    Last Post: 08-31-2010, 09:38 AM
  5. omit word in entry for alphabetical order
    By airhud86 in forum Access
    Replies: 1
    Last Post: 12-14-2009, 03:49 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