Jump to content


Photo

Need help with report


  • Please log in to reply
3 replies to this topic

#1 ypf1258

ypf1258

    Advanced Member

  • Moderators
  • PipPipPip
  • 101 posts
  • Gender:Male
  • Location:ny

Posted 24 July 2005 - 06:24 PM

I am trying to create my first report

what i am trying to get is a report of customers that purchased in 2004 and did not return in 2005
i have created a query as following

Cust (table) >odr_date is greater then{07/01/2002}
and Items(table) IT_DATE is less then {07/01/2004}

what should of happened is that it should pull up all the orders that where placed after Jul. 01 2002 till jul.2004
what happens is that it pulls up all the orders since 2002 and does not filter for orders placed past Jul 2004
according to my knowledge the field odr_date from the cust table is what shows you it the main screen when you view a customer is the last time the customer placed an order
am i doing anything wrong does anybody have any alternative way to get this report done it would be greatly appreciated

#2 dseibold

dseibold

    Advanced Member

  • Moderators
  • PipPipPip
  • 137 posts
  • Gender:Male
  • Location:Stockton, CA

Posted 25 July 2005 - 09:20 AM

Why don't you search the customer table and compare the entry date (the date the customer was first entered in your system) with the odr_date which is the date of the last activity?

CUST>entrydate is greater than {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This should get the results you want.
David Seibold
Wild Horses, Inc.
Operations Manager

#3 ypf1258

ypf1258

    Advanced Member

  • Moderators
  • PipPipPip
  • 101 posts
  • Gender:Male
  • Location:ny

Posted 26 July 2005 - 04:00 PM

Why don't you search the customer table and compare the entry date (the date the customer was first entered in your system) with the odr_date which is the date of the last activity?

CUST>entrydate is greater than {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This should get the results you want.

View Post


David thanks for your help but in my case it will not help me

1 our average prospect before purchase could be in our system for 3 years before a purchase

2 what i want is to be able to see all the non return customers

3 the main reason y i want cms.dbf is because after i get my results i want to modify the report to get more inf like what items did the non return customer order

#4 dseibold

dseibold

    Advanced Member

  • Moderators
  • PipPipPip
  • 137 posts
  • Gender:Male
  • Location:Stockton, CA

Posted 27 July 2005 - 11:55 AM

David  thanks for your help but in my case it will not help me

1 our average prospect before purchase could be in our system for 3 years before a purchase

2  what i want is to be able to see all the non return customers

3 the main reason y  i want cms.dbf is because  after i get my results i want to modify the report to get more inf like what items did the non return customer order

View Post


OK. Then this should help you:

CUST must be the master table
CMS is linked to the CUST table by custnum
ITEM is linked to the CMS table by order

Your query should be:
CUST>odr_date is greater than or equal to {07/01/2002}
and CUST>odr_date is less than {07/01/2004}

This will select your customers who (1) ordered between these dates and (2) have not ordered since that date.

The CMS link will identify the orders they made and the ITEM link will list the items made in those orders.

You may want to group your report by customer and by order. Then you will be able to print the detail of each order.

Hope this is helpful.
David Seibold
Wild Horses, Inc.
Operations Manager




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users