Query by Month 
Author Message
 Query by Month

I'm having trouble coming up with a good way of constructing a query.
All my sales record have a order date, and what I want is to query a
whole year of data and have 12 monthly sums.  I would appreciate any
pointer.

TIA

-- George



Tue, 26 Oct 1999 03:00:00 GMT
 Query by Month



Quote:
>I'm having trouble coming up with a good way of constructing a query.
>All my sales record have a order date, and what I want is to query a
>whole year of data and have 12 monthly sums.  I would appreciate any
>pointer.

>TIA

>-- George

That what I done:
1) create a calendar table with fields:
   - Date, Month, Day, Year
2) then you can run query (for month e.g May (5))as follows:

   Table.DB | Date  | Data         |
            | _date | CALC SUM ALL |

   Calendar.DB | Date  | Month   |
               | _date | Check 5 |

Raymond Kwok

///////////////////////////////////////////\/

// Raymond Kwok ///////////////////////////////\/
// Consultant Computer Section //////////////////\/
// lkl Consultant Services //////////////////////////////\/
// Expertise in Borland Paradox, networking, OS, H/W //////\/
// |h ?y t 2? Locale /////////////////////////////////\/
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\



Sat, 30 Oct 1999 03:00:00 GMT
 Query by Month


Quote:
>I'm having trouble coming up with a good way of constructing a query.
>All my sales record have a order date, and what I want is to query a
>whole year of data and have 12 monthly sums.  I would appreciate any
>pointer.
>TIA
>-- George

The easiest way to get this result is to not do it with a query but
rather through a report.  Reports, unlike queries, can group a date
field by time periods such as week, month, quarter, year.

Create a report  with your data table or any subset of it.  In the
report designer, group by month and insert summary fields in each
group footer.

Another technique I have used on occasion is to add a field for
holding the month number.  As I need to, usually before running a
query, I run a scan script which goes through the table inserting the
month number corresponding to the date field in each record.  This
technique lets me do quick by/month queries without going into a
report.

Let me know if you need further info on either method.

John Tetreault



Sat, 30 Oct 1999 03:00:00 GMT
 Query by Month



Quote:
>I'm having trouble coming up with a good way of constructing a query.
>All my sales record have a order date, and what I want is to query a
>whole year of data and have 12 monthly sums.  I would appreciate any
>pointer.

QUERY

MYTAB | SalesDate  |  Amount  |
      | 1/../97    | calc sum |

ENDQUERY

=====================================================================
Kasey K. S. Chang (a guy)           Paradox for Windows Programmer

http://users.aol.com/ksc1           X-COM Guru  among other things
^^^^^----------------------------------------------------------------
Enter Star Trek Nexus -- the PREMIER Guide to Star Trek on Internet!



Tue, 02 Nov 1999 03:00:00 GMT
 Query by Month

Actually Kasey's query will only give you month 1 (January).  You need to
have a related table which has [MonthBeginDate] and [MonthEndDate] and link
with an example element >=_Begin, <=_End

Query

Mytab  |SalesDate          | Amount    |
           |>=_Begin,<=End| Calc Sum |

Dates  |MonthBeginDate | MonthEndDate|
          | Check, _Begin   | _End              |

EndQuery  

Quote:
> >I'm having trouble coming up with a good way of constructing a query.
> >All my sales record have a order date, and what I want is to query a
> >whole year of data and have 12 monthly sums.  I would appreciate any
> >pointer.

> QUERY

> MYTAB | SalesDate  |  Amount  |
>       | 1/../97    | calc sum |

> ENDQUERY

> =====================================================================
> Kasey K. S. Chang (a guy)           Paradox for Windows Programmer



Sat, 06 Nov 1999 03:00:00 GMT
 Query by Month



With slight modification of suggested solution:

create a calendar table with fields, at least,
Date and Month (you can add DayofWeek, Day, Year, ...)

Query

Mytab  |SalesDate | Amount   |
       |_dat      | Calc Sum |

Dates  | Date | Month   | Year       |
       | _dat | Check 5 | Check 1997 |

EndQuery  

Raymond Kwok

Quote:
>Actually Kasey's query will only give you month 1 (January).  You need to
>have a related table which has [MonthBeginDate] and [MonthEndDate] and link
>with an example element >=_Begin, <=_End

>Query

>Mytab  |SalesDate          | Amount    |
>           |>=_Begin,<=End| Calc Sum |

>Dates  |MonthBeginDate | MonthEndDate|
>          | Check, _Begin   | _End              |

>EndQuery  

>> >I'm having trouble coming up with a good way of constructing a query.
>> >All my sales record have a order date, and what I want is to query a
>> >whole year of data and have 12 monthly sums.  I would appreciate any
>> >pointer.

>> QUERY

>> MYTAB | SalesDate  |  Amount  |
>>       | 1/../97    | calc sum |

>> ENDQUERY

>> =====================================================================
>> Kasey K. S. Chang (a guy)           Paradox for Windows Programmer

///////////////////////////////////////////\/

// Raymond Kwok ///////////////////////////////\/
// Consultant Computer Section //////////////////\/
// lkl Consultant Services //////////////////////////////\/
// Expertise in Borland Paradox, networking, OS, H/W //////\/
// |h ?y t 2? Locale /////////////////////////////////\/
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


Tue, 09 Nov 1999 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Last Day of Month, End of Month, First Day of Month

2. Months Months Months

3. Outputting days of the month for each month

4. Rolling-up Past Month figures to current Month

5. Difficult current month last month comparison and ParallelPeriod

6. Comparing the current months figures to the same time last month

7. MDX Question - generating data for last 3 months or last 12 months

8. Calculated Member for Getting Current Month and Previous Year Same Month

9. DATEADD(month, ...) into a short month

10. Converting month numbers to month names

11. RE-POST: Rolling-up Past Month figures to current Month

12. Need to find start date of the month and enddate of the month given a date


 
Powered by phpBB® Forum Software