Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Find last record

Discussion in 'Information Technology' started by Chris Stewart-Campbell, Jul 28, 2006.

Tags:
  1. Chris Stewart-Campbell

    Chris Stewart-Campbell
    Expand Collapse
    Guest

    I have created a report that lists items from several orders altogether. The
    items are listed in ascending "bin order," which has nothing to do with the
    order they were entered into the computer or anything like that. I need to be
    able to identify the last item in each order - and flag it on my report. I
    believe I will be able to create the "flag" using VBA once I can figure out
    how to identify that last record. I need to identify the last record for each
    order, not the last item for all of the orders together. Any ideas? (I hope I
    identified my problem well enough to solicite answers.) Thanks in advance.

    Chris Stewart-Campbell
     
  2. Loading...

    Similar Threads Forum Date
    Pathfinder Joins Sikh Philosophy Network! New SPN'ers Sep 5, 2016
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016
    Movies Ashdoc's movie review---Finding Fanny ( English version ) Theatre, Movies & Cinema Sep 15, 2014
    Leisure Can someone pls help me find this Waheguru Simran...?? Business, Lifestyle & Leisure Aug 5, 2014

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    I'm not quite clear what you mean by 'last' here. Last in what order? If you
    mean the order in which records were entered into the table, then you will
    need to add a field to record that if you don't have one. Access does not do
    that automatically. You can do it by adding a Date/Time field and setting
    the Default Value property of that field to ...

    =Now()

    Once you have a field that you can use to sort the records in the order you
    want, you can identify the max record per order using a subquery such as ...

    SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2 WHERE
    tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    FROM tblTest;

    --
    Brendan Reynolds
    Access MVP

    "Chris Stewart-Campbell" <Chris Stewart-Campbell@discussions.microsoft.com>
    wrote in message news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    >I have created a report that lists items from several orders altogether.
    >The
    > items are listed in ascending "bin order," which has nothing to do with
    > the
    > order they were entered into the computer or anything like that. I need to
    > be
    > able to identify the last item in each order - and flag it on my report. I
    > believe I will be able to create the "flag" using VBA once I can figure
    > out
    > how to identify that last record. I need to identify the last record for
    > each
    > order, not the last item for all of the orders together. Any ideas? (I
    > hope I
    > identified my problem well enough to solicite answers.) Thanks in advance.
    >
    > Chris Stewart-Campbell
     
  4. Chris Stewart-Campbell

    Chris Stewart-Campbell
    Expand Collapse
    Guest

    Okay, I’m sorry I was not more specific in my last correspondence. When I say
    “last†I do not mean the last item entered. I mean the last item for a sales
    order listed on my report. My report lists items in the following ascending
    order: by Bin, by Sales Order #, by order in which item was originally
    entered (this sort had to be added for reasons I will not go into here), and
    by item #. See example below and explanation below it.

    Item # Bin # Sales Order #

    1510006 A-014 108677
    1220002 A-015 108677
    1LU040 B-043 108677
    1LU040 B-043 108970
    1060010 E-031 108974 last item
    1060010 E-031 108976 last item
    0090035 E-033 108970 last item
    30030748 LIC1 108677
    30031548 LIC1 108677
    30030750 LIC1 108677 last item
    RIM0206027 Z-099 108975 last item

    The above example comes from an actual report of mine – except that there
    are many more items and much info related to each order (that is irrelevant
    here), so that the report covers many pages. The “last item†notation is what
    I would like to be able to print.

    I tried your code thinking perhaps that the query would recognize my “last
    item†up there as MAX, but it apparently didn’t. It pulled several records
    for each order and then just listed 0’s and -1’s under the column “last item.â€

    Any further ideas?

    Chris Stewart-Campbell


    "Brendan Reynolds" wrote:

    > I'm not quite clear what you mean by 'last' here. Last in what order? If you
    > mean the order in which records were entered into the table, then you will
    > need to add a field to record that if you don't have one. Access does not do
    > that automatically. You can do it by adding a Date/Time field and setting
    > the Default Value property of that field to ...
    >
    > =Now()
    >
    > Once you have a field that you can use to sort the records in the order you
    > want, you can identify the max record per order using a subquery such as ...
    >
    > SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2 WHERE
    > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    > FROM tblTest;
    >
    > --
    > Brendan Reynolds
    > Access MVP
    >
    > "Chris Stewart-Campbell" <Chris Stewart-Campbell@discussions.microsoft.com>
    > wrote in message news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    > >I have created a report that lists items from several orders altogether.
    > >The
    > > items are listed in ascending "bin order," which has nothing to do with
    > > the
    > > order they were entered into the computer or anything like that. I need to
    > > be
    > > able to identify the last item in each order - and flag it on my report. I
    > > believe I will be able to create the "flag" using VBA once I can figure
    > > out
    > > how to identify that last record. I need to identify the last record for
    > > each
    > > order, not the last item for all of the orders together. Any ideas? (I
    > > hope I
    > > identified my problem well enough to solicite answers.) Thanks in advance.
    > >
    > > Chris Stewart-Campbell

    >
    >
    >
     
  5. Chris Stewart-Campbell

    Chris Stewart-Campbell
    Expand Collapse
    Guest

    Brendan -

    I just realized that the -1 in the Last Item column is identifying what it
    thinks is the last item, but it is just identifying the item with the largest
    number, not the last one in the sort that I am looking at.

    Chris Stewart-Campbell

    "Chris Stewart-Campbell" wrote:

    > Okay, I’m sorry I was not more specific in my last correspondence. When I say
    > “last†I do not mean the last item entered. I mean the last item for a sales
    > order listed on my report. My report lists items in the following ascending
    > order: by Bin, by Sales Order #, by order in which item was originally
    > entered (this sort had to be added for reasons I will not go into here), and
    > by item #. See example below and explanation below it.
    >
    > Item # Bin # Sales Order #
    >
    > 1510006 A-014 108677
    > 1220002 A-015 108677
    > 1LU040 B-043 108677
    > 1LU040 B-043 108970
    > 1060010 E-031 108974 last item
    > 1060010 E-031 108976 last item
    > 0090035 E-033 108970 last item
    > 30030748 LIC1 108677
    > 30031548 LIC1 108677
    > 30030750 LIC1 108677 last item
    > RIM0206027 Z-099 108975 last item
    >
    > The above example comes from an actual report of mine – except that there
    > are many more items and much info related to each order (that is irrelevant
    > here), so that the report covers many pages. The “last item†notation is what
    > I would like to be able to print.
    >
    > I tried your code thinking perhaps that the query would recognize my “last
    > item†up there as MAX, but it apparently didn’t. It pulled several records
    > for each order and then just listed 0’s and -1’s under the column “last item.â€
    >
    > Any further ideas?
    >
    > Chris Stewart-Campbell
    >
    >
    > "Brendan Reynolds" wrote:
    >
    > > I'm not quite clear what you mean by 'last' here. Last in what order? If you
    > > mean the order in which records were entered into the table, then you will
    > > need to add a field to record that if you don't have one. Access does not do
    > > that automatically. You can do it by adding a Date/Time field and setting
    > > the Default Value property of that field to ...
    > >
    > > =Now()
    > >
    > > Once you have a field that you can use to sort the records in the order you
    > > want, you can identify the max record per order using a subquery such as ...
    > >
    > > SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    > > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2 WHERE
    > > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    > > FROM tblTest;
    > >
    > > --
    > > Brendan Reynolds
    > > Access MVP
    > >
    > > "Chris Stewart-Campbell" <Chris Stewart-Campbell@discussions.microsoft.com>
    > > wrote in message news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    > > >I have created a report that lists items from several orders altogether.
    > > >The
    > > > items are listed in ascending "bin order," which has nothing to do with
    > > > the
    > > > order they were entered into the computer or anything like that. I need to
    > > > be
    > > > able to identify the last item in each order - and flag it on my report. I
    > > > believe I will be able to create the "flag" using VBA once I can figure
    > > > out
    > > > how to identify that last record. I need to identify the last record for
    > > > each
    > > > order, not the last item for all of the orders together. Any ideas? (I
    > > > hope I
    > > > identified my problem well enough to solicite answers.) Thanks in advance.
    > > >
    > > > Chris Stewart-Campbell

    > >
    > >
    > >
     
  6. Chris Stewart-Campbell

    Chris Stewart-Campbell
    Expand Collapse
    Guest

    Okay, final post - I think. I switched "max" to "last" and it worked! Thank
    you for your help . . . One problem, however, the query takes a very long
    time to run. Any thoughts about that? Or do I just need to live with it?

    "Chris Stewart-Campbell" wrote:

    > Brendan -
    >
    > I just realized that the -1 in the Last Item column is identifying what it
    > thinks is the last item, but it is just identifying the item with the largest
    > number, not the last one in the sort that I am looking at.
    >
    > Chris Stewart-Campbell
    >
    > "Chris Stewart-Campbell" wrote:
    >
    > > Okay, I’m sorry I was not more specific in my last correspondence. When I say
    > > “last†I do not mean the last item entered. I mean the last item for a sales
    > > order listed on my report. My report lists items in the following ascending
    > > order: by Bin, by Sales Order #, by order in which item was originally
    > > entered (this sort had to be added for reasons I will not go into here), and
    > > by item #. See example below and explanation below it.
    > >
    > > Item # Bin # Sales Order #
    > >
    > > 1510006 A-014 108677
    > > 1220002 A-015 108677
    > > 1LU040 B-043 108677
    > > 1LU040 B-043 108970
    > > 1060010 E-031 108974 last item
    > > 1060010 E-031 108976 last item
    > > 0090035 E-033 108970 last item
    > > 30030748 LIC1 108677
    > > 30031548 LIC1 108677
    > > 30030750 LIC1 108677 last item
    > > RIM0206027 Z-099 108975 last item
    > >
    > > The above example comes from an actual report of mine – except that there
    > > are many more items and much info related to each order (that is irrelevant
    > > here), so that the report covers many pages. The “last item†notation is what
    > > I would like to be able to print.
    > >
    > > I tried your code thinking perhaps that the query would recognize my “last
    > > item†up there as MAX, but it apparently didn’t. It pulled several records
    > > for each order and then just listed 0’s and -1’s under the column “last item.â€
    > >
    > > Any further ideas?
    > >
    > > Chris Stewart-Campbell
    > >
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > > > I'm not quite clear what you mean by 'last' here. Last in what order? If you
    > > > mean the order in which records were entered into the table, then you will
    > > > need to add a field to record that if you don't have one. Access does not do
    > > > that automatically. You can do it by adding a Date/Time field and setting
    > > > the Default Value property of that field to ...
    > > >
    > > > =Now()
    > > >
    > > > Once you have a field that you can use to sort the records in the order you
    > > > want, you can identify the max record per order using a subquery such as ...
    > > >
    > > > SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    > > > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2 WHERE
    > > > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    > > > FROM tblTest;
    > > >
    > > > --
    > > > Brendan Reynolds
    > > > Access MVP
    > > >
    > > > "Chris Stewart-Campbell" <Chris Stewart-Campbell@discussions.microsoft.com>
    > > > wrote in message news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    > > > >I have created a report that lists items from several orders altogether.
    > > > >The
    > > > > items are listed in ascending "bin order," which has nothing to do with
    > > > > the
    > > > > order they were entered into the computer or anything like that. I need to
    > > > > be
    > > > > able to identify the last item in each order - and flag it on my report. I
    > > > > believe I will be able to create the "flag" using VBA once I can figure
    > > > > out
    > > > > how to identify that last record. I need to identify the last record for
    > > > > each
    > > > > order, not the last item for all of the orders together. Any ideas? (I
    > > > > hope I
    > > > > identified my problem well enough to solicite answers.) Thanks in advance.
    > > > >
    > > > > Chris Stewart-Campbell
    > > >
    > > >
    > > >
     
  7. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    What we have here is a 'correlated sub-query', meaning that the sub-query
    will have to be executed once for every row in the outer query. That's not
    going to be fast if there are a large number of records. You could check
    that you have indexes on the fields listed in the ORDER BY and WHERE clause.
    As you are sorting by multiple fields, a multi-field index might help, e.g.
    if you're sorting by BinNumber, OrderNumber and DateEntered, try creating a
    multi-field index on those three fields.

    While indexes can dramatically improve data retrieval, they can slow down
    updates, as the index has to be updated as well. So when deciding what
    indexes are required, it is worth testing after each change, to determine
    whether the additional index really made an improvement.

    --
    Brendan Reynolds
    Access MVP

    "Chris Stewart-Campbell" <ChrisStewartCampbell@discussions.microsoft.com>
    wrote in message news:907EC1F4-8EFB-4ABA-8C09-7BE2D8FEA707@microsoft.com...
    > Okay, final post - I think. I switched "max" to "last" and it worked!
    > Thank
    > you for your help . . . One problem, however, the query takes a very long
    > time to run. Any thoughts about that? Or do I just need to live with it?
    >
    > "Chris Stewart-Campbell" wrote:
    >
    >> Brendan -
    >>
    >> I just realized that the -1 in the Last Item column is identifying what
    >> it
    >> thinks is the last item, but it is just identifying the item with the
    >> largest
    >> number, not the last one in the sort that I am looking at.
    >>
    >> Chris Stewart-Campbell
    >>
    >> "Chris Stewart-Campbell" wrote:
    >>
    >> > Okay, I'm sorry I was not more specific in my last correspondence. When
    >> > I say
    >> > "last" I do not mean the last item entered. I mean the last item for a
    >> > sales
    >> > order listed on my report. My report lists items in the following
    >> > ascending
    >> > order: by Bin, by Sales Order #, by order in which item was originally
    >> > entered (this sort had to be added for reasons I will not go into
    >> > here), and
    >> > by item #. See example below and explanation below it.
    >> >
    >> > Item # Bin # Sales Order #
    >> >
    >> > 1510006 A-014 108677
    >> > 1220002 A-015 108677
    >> > 1LU040 B-043 108677
    >> > 1LU040 B-043 108970
    >> > 1060010 E-031 108974 last item
    >> > 1060010 E-031 108976 last item
    >> > 0090035 E-033 108970 last item
    >> > 30030748 LIC1 108677
    >> > 30031548 LIC1 108677
    >> > 30030750 LIC1 108677 last item
    >> > RIM0206027 Z-099 108975 last item
    >> >
    >> > The above example comes from an actual report of mine - except that
    >> > there
    >> > are many more items and much info related to each order (that is
    >> > irrelevant
    >> > here), so that the report covers many pages. The "last item" notation
    >> > is what
    >> > I would like to be able to print.
    >> >
    >> > I tried your code thinking perhaps that the query would recognize my
    >> > "last
    >> > item" up there as MAX, but it apparently didn't. It pulled several
    >> > records
    >> > for each order and then just listed 0's and -1's under the column "last
    >> > item."
    >> >
    >> > Any further ideas?
    >> >
    >> > Chris Stewart-Campbell
    >> >
    >> >
    >> > "Brendan Reynolds" wrote:
    >> >
    >> > > I'm not quite clear what you mean by 'last' here. Last in what order?
    >> > > If you
    >> > > mean the order in which records were entered into the table, then you
    >> > > will
    >> > > need to add a field to record that if you don't have one. Access does
    >> > > not do
    >> > > that automatically. You can do it by adding a Date/Time field and
    >> > > setting
    >> > > the Default Value property of that field to ...
    >> > >
    >> > > =Now()
    >> > >
    >> > > Once you have a field that you can use to sort the records in the
    >> > > order you
    >> > > want, you can identify the max record per order using a subquery such
    >> > > as ...
    >> > >
    >> > > SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    >> > > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2
    >> > > WHERE
    >> > > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    >> > > FROM tblTest;
    >> > >
    >> > > --
    >> > > Brendan Reynolds
    >> > > Access MVP
    >> > >
    >> > > "Chris Stewart-Campbell" <Chris
    >> > > Stewart-Campbell@discussions.microsoft.com>
    >> > > wrote in message
    >> > > news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    >> > > >I have created a report that lists items from several orders
    >> > > >altogether.
    >> > > >The
    >> > > > items are listed in ascending "bin order," which has nothing to do
    >> > > > with
    >> > > > the
    >> > > > order they were entered into the computer or anything like that. I
    >> > > > need to
    >> > > > be
    >> > > > able to identify the last item in each order - and flag it on my
    >> > > > report. I
    >> > > > believe I will be able to create the "flag" using VBA once I can
    >> > > > figure
    >> > > > out
    >> > > > how to identify that last record. I need to identify the last
    >> > > > record for
    >> > > > each
    >> > > > order, not the last item for all of the orders together. Any ideas?
    >> > > > (I
    >> > > > hope I
    >> > > > identified my problem well enough to solicite answers.) Thanks in
    >> > > > advance.
    >> > > >
    >> > > > Chris Stewart-Campbell
    >> > >
    >> > >
    >> > >
     
  8. Chris Stewart-Campbell

    Chris Stewart-Campbell
    Expand Collapse
    Guest

    Well, I think I am out of luck. My tables are linked tables and, of course,
    Access will not let me add indexes to linked tables. (I am using Access to
    run reports using data from Great Plains.) I'm guessing there is nothing
    further I can do?? Thanks for all your help. (I ran my report this morning
    and it took approx. 6 minutes to fun!)

    Chris Stewart-Campbell

    "Brendan Reynolds" wrote:

    >
    > What we have here is a 'correlated sub-query', meaning that the sub-query
    > will have to be executed once for every row in the outer query. That's not
    > going to be fast if there are a large number of records. You could check
    > that you have indexes on the fields listed in the ORDER BY and WHERE clause.
    > As you are sorting by multiple fields, a multi-field index might help, e.g.
    > if you're sorting by BinNumber, OrderNumber and DateEntered, try creating a
    > multi-field index on those three fields.
    >
    > While indexes can dramatically improve data retrieval, they can slow down
    > updates, as the index has to be updated as well. So when deciding what
    > indexes are required, it is worth testing after each change, to determine
    > whether the additional index really made an improvement.
    >
    > --
    > Brendan Reynolds
    > Access MVP
    >
    > "Chris Stewart-Campbell" <ChrisStewartCampbell@discussions.microsoft.com>
    > wrote in message news:907EC1F4-8EFB-4ABA-8C09-7BE2D8FEA707@microsoft.com...
    > > Okay, final post - I think. I switched "max" to "last" and it worked!
    > > Thank
    > > you for your help . . . One problem, however, the query takes a very long
    > > time to run. Any thoughts about that? Or do I just need to live with it?
    > >
    > > "Chris Stewart-Campbell" wrote:
    > >
    > >> Brendan -
    > >>
    > >> I just realized that the -1 in the Last Item column is identifying what
    > >> it
    > >> thinks is the last item, but it is just identifying the item with the
    > >> largest
    > >> number, not the last one in the sort that I am looking at.
    > >>
    > >> Chris Stewart-Campbell
    > >>
    > >> "Chris Stewart-Campbell" wrote:
    > >>
    > >> > Okay, I'm sorry I was not more specific in my last correspondence. When
    > >> > I say
    > >> > "last" I do not mean the last item entered. I mean the last item for a
    > >> > sales
    > >> > order listed on my report. My report lists items in the following
    > >> > ascending
    > >> > order: by Bin, by Sales Order #, by order in which item was originally
    > >> > entered (this sort had to be added for reasons I will not go into
    > >> > here), and
    > >> > by item #. See example below and explanation below it.
    > >> >
    > >> > Item # Bin # Sales Order #
    > >> >
    > >> > 1510006 A-014 108677
    > >> > 1220002 A-015 108677
    > >> > 1LU040 B-043 108677
    > >> > 1LU040 B-043 108970
    > >> > 1060010 E-031 108974 last item
    > >> > 1060010 E-031 108976 last item
    > >> > 0090035 E-033 108970 last item
    > >> > 30030748 LIC1 108677
    > >> > 30031548 LIC1 108677
    > >> > 30030750 LIC1 108677 last item
    > >> > RIM0206027 Z-099 108975 last item
    > >> >
    > >> > The above example comes from an actual report of mine - except that
    > >> > there
    > >> > are many more items and much info related to each order (that is
    > >> > irrelevant
    > >> > here), so that the report covers many pages. The "last item" notation
    > >> > is what
    > >> > I would like to be able to print.
    > >> >
    > >> > I tried your code thinking perhaps that the query would recognize my
    > >> > "last
    > >> > item" up there as MAX, but it apparently didn't. It pulled several
    > >> > records
    > >> > for each order and then just listed 0's and -1's under the column "last
    > >> > item."
    > >> >
    > >> > Any further ideas?
    > >> >
    > >> > Chris Stewart-Campbell
    > >> >
    > >> >
    > >> > "Brendan Reynolds" wrote:
    > >> >
    > >> > > I'm not quite clear what you mean by 'last' here. Last in what order?
    > >> > > If you
    > >> > > mean the order in which records were entered into the table, then you
    > >> > > will
    > >> > > need to add a field to record that if you don't have one. Access does
    > >> > > not do
    > >> > > that automatically. You can do it by adding a Date/Time field and
    > >> > > setting
    > >> > > the Default Value property of that field to ...
    > >> > >
    > >> > > =Now()
    > >> > >
    > >> > > Once you have a field that you can use to sort the records in the
    > >> > > order you
    > >> > > want, you can identify the max record per order using a subquery such
    > >> > > as ...
    > >> > >
    > >> > > SELECT tblTest.OrderNumber, tblTest.BinNumber, tblTest.DateEntered,
    > >> > > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2
    > >> > > WHERE
    > >> > > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    > >> > > FROM tblTest;
    > >> > >
    > >> > > --
    > >> > > Brendan Reynolds
    > >> > > Access MVP
    > >> > >
    > >> > > "Chris Stewart-Campbell" <Chris
    > >> > > Stewart-Campbell@discussions.microsoft.com>
    > >> > > wrote in message
    > >> > > news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    > >> > > >I have created a report that lists items from several orders
    > >> > > >altogether.
    > >> > > >The
    > >> > > > items are listed in ascending "bin order," which has nothing to do
    > >> > > > with
    > >> > > > the
    > >> > > > order they were entered into the computer or anything like that. I
    > >> > > > need to
    > >> > > > be
    > >> > > > able to identify the last item in each order - and flag it on my
    > >> > > > report. I
    > >> > > > believe I will be able to create the "flag" using VBA once I can
    > >> > > > figure
    > >> > > > out
    > >> > > > how to identify that last record. I need to identify the last
    > >> > > > record for
    > >> > > > each
    > >> > > > order, not the last item for all of the orders together. Any ideas?
    > >> > > > (I
    > >> > > > hope I
    > >> > > > identified my problem well enough to solicite answers.) Thanks in
    > >> > > > advance.
    > >> > > >
    > >> > > > Chris Stewart-Campbell
    > >> > >
    > >> > >
    > >> > >

    >
    >
    >
     
  9. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Nothing I can think of. But I don't know anything about Great Plains.
    Possibly someone more familiar with it might be able to suggest something.
    Good luck.

    --
    Brendan Reynolds
    Access MVP

    "Chris Stewart-Campbell" <ChrisStewartCampbell@discussions.microsoft.com>
    wrote in message news:440269F0-642A-4CF6-B972-2B57E1224E61@microsoft.com...
    > Well, I think I am out of luck. My tables are linked tables and, of
    > course,
    > Access will not let me add indexes to linked tables. (I am using Access to
    > run reports using data from Great Plains.) I'm guessing there is nothing
    > further I can do?? Thanks for all your help. (I ran my report this morning
    > and it took approx. 6 minutes to fun!)
    >
    > Chris Stewart-Campbell
    >
    > "Brendan Reynolds" wrote:
    >
    >>
    >> What we have here is a 'correlated sub-query', meaning that the sub-query
    >> will have to be executed once for every row in the outer query. That's
    >> not
    >> going to be fast if there are a large number of records. You could check
    >> that you have indexes on the fields listed in the ORDER BY and WHERE
    >> clause.
    >> As you are sorting by multiple fields, a multi-field index might help,
    >> e.g.
    >> if you're sorting by BinNumber, OrderNumber and DateEntered, try creating
    >> a
    >> multi-field index on those three fields.
    >>
    >> While indexes can dramatically improve data retrieval, they can slow down
    >> updates, as the index has to be updated as well. So when deciding what
    >> indexes are required, it is worth testing after each change, to determine
    >> whether the additional index really made an improvement.
    >>
    >> --
    >> Brendan Reynolds
    >> Access MVP
    >>
    >> "Chris Stewart-Campbell" <ChrisStewartCampbell@discussions.microsoft.com>
    >> wrote in message
    >> news:907EC1F4-8EFB-4ABA-8C09-7BE2D8FEA707@microsoft.com...
    >> > Okay, final post - I think. I switched "max" to "last" and it worked!
    >> > Thank
    >> > you for your help . . . One problem, however, the query takes a very
    >> > long
    >> > time to run. Any thoughts about that? Or do I just need to live with
    >> > it?
    >> >
    >> > "Chris Stewart-Campbell" wrote:
    >> >
    >> >> Brendan -
    >> >>
    >> >> I just realized that the -1 in the Last Item column is identifying
    >> >> what
    >> >> it
    >> >> thinks is the last item, but it is just identifying the item with the
    >> >> largest
    >> >> number, not the last one in the sort that I am looking at.
    >> >>
    >> >> Chris Stewart-Campbell
    >> >>
    >> >> "Chris Stewart-Campbell" wrote:
    >> >>
    >> >> > Okay, I'm sorry I was not more specific in my last correspondence.
    >> >> > When
    >> >> > I say
    >> >> > "last" I do not mean the last item entered. I mean the last item for
    >> >> > a
    >> >> > sales
    >> >> > order listed on my report. My report lists items in the following
    >> >> > ascending
    >> >> > order: by Bin, by Sales Order #, by order in which item was
    >> >> > originally
    >> >> > entered (this sort had to be added for reasons I will not go into
    >> >> > here), and
    >> >> > by item #. See example below and explanation below it.
    >> >> >
    >> >> > Item # Bin # Sales Order #
    >> >> >
    >> >> > 1510006 A-014 108677
    >> >> > 1220002 A-015 108677
    >> >> > 1LU040 B-043 108677
    >> >> > 1LU040 B-043 108970
    >> >> > 1060010 E-031 108974 last item
    >> >> > 1060010 E-031 108976 last item
    >> >> > 0090035 E-033 108970 last item
    >> >> > 30030748 LIC1 108677
    >> >> > 30031548 LIC1 108677
    >> >> > 30030750 LIC1 108677 last item
    >> >> > RIM0206027 Z-099 108975 last item
    >> >> >
    >> >> > The above example comes from an actual report of mine - except that
    >> >> > there
    >> >> > are many more items and much info related to each order (that is
    >> >> > irrelevant
    >> >> > here), so that the report covers many pages. The "last item"
    >> >> > notation
    >> >> > is what
    >> >> > I would like to be able to print.
    >> >> >
    >> >> > I tried your code thinking perhaps that the query would recognize my
    >> >> > "last
    >> >> > item" up there as MAX, but it apparently didn't. It pulled several
    >> >> > records
    >> >> > for each order and then just listed 0's and -1's under the column
    >> >> > "last
    >> >> > item."
    >> >> >
    >> >> > Any further ideas?
    >> >> >
    >> >> > Chris Stewart-Campbell
    >> >> >
    >> >> >
    >> >> > "Brendan Reynolds" wrote:
    >> >> >
    >> >> > > I'm not quite clear what you mean by 'last' here. Last in what
    >> >> > > order?
    >> >> > > If you
    >> >> > > mean the order in which records were entered into the table, then
    >> >> > > you
    >> >> > > will
    >> >> > > need to add a field to record that if you don't have one. Access
    >> >> > > does
    >> >> > > not do
    >> >> > > that automatically. You can do it by adding a Date/Time field and
    >> >> > > setting
    >> >> > > the Default Value property of that field to ...
    >> >> > >
    >> >> > > =Now()
    >> >> > >
    >> >> > > Once you have a field that you can use to sort the records in the
    >> >> > > order you
    >> >> > > want, you can identify the max record per order using a subquery
    >> >> > > such
    >> >> > > as ...
    >> >> > >
    >> >> > > SELECT tblTest.OrderNumber, tblTest.BinNumber,
    >> >> > > tblTest.DateEntered,
    >> >> > > [DateEntered]=(SELECT Max([DateEntered]) FROM tblTest AS tblTest2
    >> >> > > WHERE
    >> >> > > tblTest2.OrderNumber = tblTest.OrderNumber) AS [MaxInOrder?]
    >> >> > > FROM tblTest;
    >> >> > >
    >> >> > > --
    >> >> > > Brendan Reynolds
    >> >> > > Access MVP
    >> >> > >
    >> >> > > "Chris Stewart-Campbell" <Chris
    >> >> > > Stewart-Campbell@discussions.microsoft.com>
    >> >> > > wrote in message
    >> >> > > news:6622FB3A-C053-4782-90D3-A516C72D8614@microsoft.com...
    >> >> > > >I have created a report that lists items from several orders
    >> >> > > >altogether.
    >> >> > > >The
    >> >> > > > items are listed in ascending "bin order," which has nothing to
    >> >> > > > do
    >> >> > > > with
    >> >> > > > the
    >> >> > > > order they were entered into the computer or anything like that.
    >> >> > > > I
    >> >> > > > need to
    >> >> > > > be
    >> >> > > > able to identify the last item in each order - and flag it on my
    >> >> > > > report. I
    >> >> > > > believe I will be able to create the "flag" using VBA once I can
    >> >> > > > figure
    >> >> > > > out
    >> >> > > > how to identify that last record. I need to identify the last
    >> >> > > > record for
    >> >> > > > each
    >> >> > > > order, not the last item for all of the orders together. Any
    >> >> > > > ideas?
    >> >> > > > (I
    >> >> > > > hope I
    >> >> > > > identified my problem well enough to solicite answers.) Thanks
    >> >> > > > in
    >> >> > > > advance.
    >> >> > > >
    >> >> > > > Chris Stewart-Campbell
    >> >> > >
    >> >> > >
    >> >> > >

    >>
    >>
    >>
     

Share This Page