Field in Dynamic Query

[jca]

I would like to know if there's a way to always make sure certain fields will be in a dynamic query ?

Like, I have a dynamic query which list the transactions a client made during a certain period. The column header is the month of the transaction and the value is the volume in that month. So I have header like May, June, July, ... But if no one made a transaction in the month of May, how can I make it appears in the query even if the month is empty ?

Right now I have another query using the results of the dynamic query and the query crash when there's a month missing. So I would like the query to put like 0 in the field when the field it's looking for is not there.

I hope I was clear enough for you to understand. If not, make a reply saying on what you need me to be clearer.

Thanks in advance,
JC

 

[RichMorrison]

1) Make a table of "Months" with 12 rows.

2) Make a query that sums Transaction by Month.

2) Join the Month table to the Transaction query with a "Left Join". See Help for details.

3) If the Transaction query Month is null in your query use zero for the Amount.

4) If not null, use the sum from the Transaction query.

Bon chance,
RichM

 

[jca]

Thanks for your help Rich
It's such an obvious and easy way to do it
I don't know why I didn't tough about it myself
I guest sometimes you have it and sometimes you don't

JC