Solved filter query column by another Column (1 Viewer)

[mhakim]

i have query like this

First Column accnum 111,112 ,113 , 121,122,123
Send Column Debit 700,500,0 200,900,100
Third Column Credit 400,200,0 100,800,600
_____________________________________________________________________________________
So Question is

i need to make Fourth cloumn in query contain only values that have accnum like 111,112,113
i has to appear like 700,500 only

 

[arnelgp]

maybe create a Total query:

Select Left$([accnum], 2]) As AcctGroup, Sum(Debit) As TotalDebit, Sum(Credit) As TotalCredit
From yourTable Group By Left$([accnum],2)

 

[plog]

Why would you list you columnar sample data in rows? Don't make us work harder than we have to to understand your situation.

Also, debits and credits should not be stored in separate fields of a table. Use positive and negative numbers in one column for them.

Finally, perhaps I don't understand your question but you don't need a fourth column, you simply put your criteria (accnun=111, 112, 113) in the appropriate spot ( under the accnun field)

 

[oleronesoftwares]

add the new column
function is below
expr1:=if(accnum like 11*,accnum,0)

 

[arnelgp]

Also, debits and credits should not be stored in separate fields

expr1:=if(accnum like 11*,accnum,0)

this will only show 1 debit/accnum value?

 

[oleronesoftwares]

Alternatively you can write a new query
SELECT accnum, debit,credit FROM Customers WHERE accnum Like “11*”;

 

[oleronesoftwares]

this will only show 1 debit/accnum value?

it will put the accnum value into the new column where accnum has 11 as the first two values

 

[mhakim]

add the new column
function is below
expr1:=if(accnum like 11*,accnum,0)

please take a look
this is query in view mode
i just want to add more 2 two colums as above (Filter more data on query )

add the new column
function is below
expr1:=if(accnum like 11*,accnum,0)

please take a look
this is query in view mode
i just want to (Filter more data on query ) add more 2 two colums as PIC Attached

 

[oleronesoftwares]

please take a look
this is query in view mode
i just want to (Filter more data on query ) add more 2 two colums as PIC Attached

for first column
add the new column
function is below
expr1:=if(sequence2 like 11*,sumofenddebit,0)

for second column
add the new column
function is below
expr2:=if(sequence2 like 12*,sumofenddebit,0)


the function will be in the column definition field, not criteria section.

 

[mhakim]

for first column
add the new column
function is below
expr1:=if(sequence2 like 11*,sumofenddebit,0)

for second column
add the new column
function is below
expr2:=if(sequence2 like 12*,sumofenddebit,0)


the function will be in the column definition field, not criteria section.

thanks for reply
i am so grateful

Syntax error appear

 

[oleronesoftwares]

IIf([TAG_LINE_CMMT] Like "*PC5*" follow this sample. Am out of where my PC is.

 

[mhakim]

IIf([TAG_LINE_CMMT] Like "*PC5*" follow this sample. Am out of where my PC is.

thanks
Appreciated

 

[Pat Hartman]

This is something you should be doing in a report rather than in a query. If the acct field has multiple attributes, it should be multiple fields rather than just one. That will make summing and grouping simple in a report. Or if you leave the field mushed, add a column that extracts what you want to group by

Select ..., Left(acct, 1) as AcctGroup

Then you can use AcctGroup in the report to sort and group on to get the sub totals you want.