Query wizard - LawsonGuru.com - LawsonGuru.com Forums

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244

prav

New Member

Posts: 2

1/26/2011 6:58 PM

Hi all,
I am trying to write a query to get a list of all the vendors and their addresses that had payments in 2010 with the total payment above $600.

My idea is to use APPAYMENT as the main file and then use the relation to APVENMAST to get the vendor related info.
Now when I am entering selection criteria, how can I enter something like sum(TRN-CHK-AMT) > 600 ?

Does the query wizard allow to use column functions?

Greatly appreciate your help in this.

Sherry S.

Basic Member

Posts: 11

1/28/2011 1:42 PM

Hi,
yes you can under the selection criteria

Tip You can select from a list of available values by choosing the Display Sample Data for Selection option and selecting the field again. Available values appear in the list.

Operator	Description
=	Equal to the value you set.
!=	Not equal to value you set.
<	Less than the value you set.
<=	Less than or equal to you set.
>	Greater than the value you set.
>=	Greater than or equal to the value you set.
~	Similar to value you set.
!~	Not similar to the value you set.

If you set more than one criterion, the conjunction that describes the relationship between the criteria. Possible values are AND and OR

Shane Jones

Veteran Member

Posts: 460

2/2/2011 3:01 PM

I have never found a way to do a SUM within the Query Wizard... If you are needing to sum records and then apply a greater than you might need to find a different tool. I am not sure if it is possible within Excel Addins without multiple steps. If you really want to use Add-ins you will need to pull every record and then apply a group by and sum the values within Excel. You might find more luck with a Crystal Reports.

JonA

Veteran Member

Posts: 1162

2/2/2011 4:38 PM

I don't think you can use a sum function within the criteria tab either. That has to be done in Excel after the query is finished. I prefer a pivot table to run totals by vendor and then sort.