Re: Using Selection Formula within a Group

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Holmes (rholmes_at_REMOVEmmwec.org)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 09:14:16 -0500

You'll want to use the Group Sorting feature.
Try this: Insert another group based on the date. Insert a summary field
getting the maximum date for this group. It really doesn't change anything
that you had before. Now, in the 'Report' menu option, you should find the
'Group Sort Expert' (version 9) or 'TopN/Sort Group Expert' (version 7).
You will then want to perform either a 'TopN' or 'BottomN' sort.
If the dates are in ascending order and you want the most recent dates,
choose 'BottomN'. Change 'N' to be two. this should give you the results
that you want.

Bob Holmes

"Milzy" <laura.milczarek@panthers.com.au> wrote in message
news:d7e64619.0403081458.39e0bee1@posting.google.com...
> Hi,
>
> I have a Crystal Report which I need to add selection criteria and
> formulas WITHIN each group.
>
> I have tried all help and internet searches and cannot for the life of
> me work this out.
>
> This is a report which needs to show price variation per product per
> vendor.
> Each record from the database has fields which show a vendor name,
> Product ID date received, quantity and Cost price.
>
> I have grouped by vendor ID so that all purchases from that vendor are
> together.
>
> I have then grouped by Product so that the different dates each
> product has been purchased are together.
>
> The next part is where I am coming unstuck.
>
> I need to only show the last 2 purchases of each product so that I can
> compare the prices and show the variance.
>
> When I try and use group selection formula, and show the top 2 dates
> for each product, it only shows each product with the dates that are
> equal to the top 2 in the whole database.
>
> For example
>
> Just say product 1 has purchases on 27/3/2004, 28/3/2004 and 29/3/2004
> and product 2 has purchases on 24/3/2004, 25/3/2004, and 26/3/2004.
>
> For Product 1 I want to see dates 29/3/2004 and 28/3/2004
> And Product 2 I want to see dates 26/3/2004 and 25/3/2004
>
> When I apply the formula I see the right dates for Product 1, but
> product 2 nothing because these dates are not part of the top 2
> overall.
>
> I want to show the top 2 in each group. Regardless of whether they are
> the top 2 dates in the whole database or not.
>
> From there I need to create some formula fields which only apply to
> the records in each group.
>
> Whenever I try and make selection formula it always seems to want to
> apply itself over the whole database and not just within the group.
>
> This is probably something really simple, but I just cannot figure it
> out.
>
> Can someone please help!!!

Relevant Pages

  • Re: Poly Couples
    ... If you want to claim that OO is the consolution prize for lame database ... there is a metamodel which describes the hierarchy. ... Metamodel is stored in SQL, however, the data is stored in non ... vendor, and the structure is different than the one you use originally. ...
    (comp.object)
  • Re: Poly Couples
    ... Essentially - different versions for different clients. ... insist that you use their existing database which is from a different ... vendor, and the structure is different than the one you use originally. ... characters would mark variable insertion place-holders in the SQL. ...
    (comp.object)
  • Re: Purchase Order form questions
    ... made a PO table on the many side of a one-to-many with the Vendor table. ... link table for a many-to-many join saving some of the duplicate redundant ... Dim strArgs As String ... Of course I need to keep the price at the time ...
    (microsoft.public.access.formscoding)
  • Re: Poly Couples
    ... business software" example mentioned in this thread. ... But the communication mechanism with the database remains the same. ... That doesn't mean that other languages (such as sql, ... vendor, and the structure is different than the one you use originally. ...
    (comp.object)
  • Re: Need expert coding help with a debugging issue.
    ... If you are declaring by default a recordset or database as a variant you may not get the hoped-for result. ... even if it works to jump to End With (at LocationEnd), ... code that tells the user exactly which bin location he or she should put the ... The bins are sorted by vendor. ...
    (microsoft.public.access.formscoding)