Paradox 7 Query Problem - HELP!
Jeff D.
100,000 records. We have been accumulating this data for 8 years. Problem:
I currently run a report that is 39 pages long. I need to eliminate
information that is nolonger of value. The query I run now is as follows (4
fields):
1. Field Name: (field check box is selected) Lead Type
2. Field Name: (field check box is selected) Lead Source
3. Field Name: (field check box is selected) Lead Rating
with the following record criteria - Excellent or Good or Fair or
Qualified, calc count all
4. Field Name: Value of Order (field check box is NOT selected) record
criteria - calc sum all as Order Value Sum, calc count as # of Orders
Each record in the database is tied to a "Lead Source" and assigned a "Lead
Rating." If a record results in a sale, the dollar value is entered into
the database (tied to that record) and that record is credited with an
order. The purpose of the report is to show which "Lead Sources" are
generating leads that result in sales.
For Example:
(Lead Source) IPB
_________________________
Lead Types Lead Rating # of Leads # of orders Value of Orders
Ads Excellent 45 7 $10,000
Ads Good 23 12 $14,000
Ads Fair 148 2 $600
PR Excellent 23 2 $1000
PR Good 78 34 $19999
PR Fair 345 43 $20000
_____________________________________________________________________
Totals 662 100 $65559
There are hundreds of lead sources that mirror the above example, 39 pages
worth. What I cannot figure out is how to eliminate the following:
1. Lead Sources (with all lead types over two years old) that do not have
"ANY" sales. I do not want to eliminate individual lead types (within a
lead source) that do not have any sales but "Any lead source that has as a
cummulative total (see example above) of $0.00 Sales. For example.....in my
example above - instead of the total being $65559, if it was zero & all
lead types are over two years old....I would like to eliminate it from my
report.
I do not know how to do this in my query above.
Any help in this matter would be greatly appreciated.
Please respond to my e-mail address: le...@tmemail.com or call
800-295-0931 & ask for Jeff, Mgr. Lead Management Services
Thank you!
Jeff
Liz
A few questions:
1. What is the structure of the table(s) (be sure to tell us
which field(s) is(are) the primary key)?
2. Could you explain the elimination criteria a bit more? For
example, how do you know if a lead type is 'over two years old'?
And do you mean:
a. if all the lead types under this lead source are more than two
years old, exclude the records. Also exclude any records where
the sum of Value of Orders = 0. (sources matching _either_
criteria are excluded)
or
b. if all the lead types under this lead source are more than two
years old _and_ the sum of Value of Orders = 0, exclude the
records. (only sources matching _both_ criteria are excluded)
?
Whatever the answers, I expect multiple queries...
Liz
Liz
I've downloaded the files, because one of the CTechs is sure to
come along and delete them. Please check out x for some
newsgroup etiquette info.
As for your problem, I'm in the middle of a crunch, but will try
to get to it before the end of the day. (Unless someone else
beats me.)
Liz
"Jeff D." wrote:
>
> Liz:
> Question #1:Being not well versed on Paradox & having nothing to do with
> the set up of the database...I am not sure what you are asking. There is
> one table containing all 100,000+ records (believe it or not). Since there
> is only one table, I don't believe there is a primary key...if there must
> be one, then the unique identifier is the "Lead #." The fields contain
> either text or numbers (dates, $ amounts).
> Question #2: One field is labeled "First Contact Date." Each lead/record is
> assigned a first contact date.This is the field I would like to use for
> "Over two years old."
> Question #2: B is the correct criteria.
>
> I have attached the query I am trying to modify and also a sample of the
> table. I am really at a loss with this. The lead source "IEN" would be an
> example of a lead source that should not appear because it fits the
> criteria.
>
> Thank you for your help!
>
> Best regards,
>
> Jeff Dollman
>
Jeff D.
"check out x" mean/or where do I look for "etiquette" info?"
Thank you,
Jeff D.
Liz <lea...@aros.net> wrote in article <3A68708B...@aros.net>...
Liz
It means I forgot to paste the URL in where the x was! Bad me!
Here's the URL:
http://www.techtricks.com/assorted/newshints.php3
Liz
Jeff D.
now) and am wondering what specific etiquette you were referring too?
1. Don't send attachments (which I did)
2. If you do send attachments include all files not just the .dB file (I
did not send all files)
3. Spoofing e-mail address (I didn't do)
I know I have missed something(s)....when you get time could you please let
me know what?
Thank you for your time & have a great weekend!
Jeff D.
_____________________________________________________________________
Liz <lea...@aros.net> wrote in article <3A6894D6...@aros.net>...
Lorrin Moore
Your query
Query
ANSWER: :PRIV:ANSWER.DB
SORT: Lyon.DB->"Lead Rating", Lyon.DB->"Lead Type", Lyon.DB->"Lead Source",
5,
6, Lyon.DB->"Lead Rating"
Lyon.DB | Lead Type | Lead Source |
| Check | Check |
Lyon.DB | Lead Rating |
| Check Excellent or Good or Fair or Qualified, calc count all |
Lyon.DB | Value of Order |
| calc sum all as Order Value Sum, calc count as # of Orders |
Lyon.DB | Followup Returned |
| blank or >=2/1/2000, <=1/31/2001 |
EndQuery
Your Table Struct:
Field Name Field Type
------------------------- ----------
Last Name A20
First Name A15
Telephone A12
Company Name A30
Phone Source A6
First Contact Date D
Last Contact Date D
Next Contact Date D
Title A25
Title category A2
Business A40
Address1 A40
Address2 A40
City A15
State A2
Zipcode A10
Country A20
Extension A4
Fax A12
TMKT Rep A3
SIC Code A8
No of Employees A5
Territory Number A3
Lead Type A1
Lead Source A5
Lead Source Date D
Notes 1 A125
Notes 2 A125
Notes 3 A125
Notes 4 A125
Lead Rating A9
Distributor Involved A3
Literature to Send A80
Products Interested In A80
Received Order A3
Value of Order $
Lost Order A3
Value of Order Lost $
Lost to Whom A40
Still Pending A3
SCA Comments A150
Literature History A80
Followup Returned D
Reader Service # A4
Customer Status A10
Project in Progress A10
Price Quote A5
Date Needed By D
Rep Contact A5
--
Lorrin (filter the spam to reply)
Jeff D.
Thank you very much!
Jeff D.
_____________________________________________________________________
Lorrin Moore <msop...@s0p1admesspiagmn.com> wrote in article
<3a68b34a@cnews>...
Liz
Besides what Lorrin had to say: one zip file is A) smaller,
B) easier to download since it's one file instead of many.
So, don't post files if you don't have to (the newsgroup
participants in many countries pay for their phone
connections by the minute...) and if you do have to, make
them really small (don't ever post more than ~50KB, if you
have more than 50KB ask if anyone is willing to receive it
direct to their email address, or put it on an FTP site and
tell us where to find it).
Meanwhile, don't let any of this scare you away from the
group. We try to be helpful, and we're a pretty friendly
bunch. I might recommend you check out the Database Basics
and Data Normalization papers on the Paradox Resources page
at http://www.rdaworldwide.com/ - you may find these quite
helpful since you're relatively new to Paradox.
Now, about Paradox tables: they include the following files:
.DB (the table)
.MB (Memo and blob fields, if any)
.PX (primary index, if any)
.VAL (validity checks, if any)
.TV (table view settings, if any)
.FAM (listing of files related to the .DB, if any)
.Xnn (Secondary single-field index)
.Ynn (Secondary single-field index)
.XGn (Composite secondary index)
.YGn (Composite secondary index)
But don't post anything else (at least not yet). I want to
send this info to get it out of the way, then I'll finish
reading the groups and get back to your real question.
Liz
Liz
Lead Type to Lead Source!
Liz
Liz wrote:
>
> Finally, your third query would be (based on what your
> original post said):
>
> ANS1.DB | Lead Source |
> | _join1 |
>
> Contact2.db | Lead Type | Lead Source |
> | Check | Check _join1 |
>
> Contact2.db | Lead Rating |
> | Check Excellent or Good or Fair or Qualified, calc count all |
>
> Contact2.db | Value of Order |
Liz
First, don't know if it was because of the download, or if
your table is corrupt at your end, but it was corrupt here.
I ran the table repair utility and that fixed it. You may
wish to check the table at your end. (Always make a backup
of the table before running table repair.)
Second, the query you sent was for a different table -
LYON.DB, but you sent CONTACT2.DB...
Third, I've decided that if the data were normalized, this
would be a lot easier. That web site mentioned in my other
post can help with that...
Now, to your query... I'm not even certain this can be done
with queries. If it can, it's going to take a number of
them.... I think the first query might look like this:
Contact2.db | First Contact Date | Lead Source |
| calc max as FCD | Check |
Contact2.db | Value Of Order |
| calc max as VOO |
This will get you all unique values in Lead Source along
with the maximum (most recent) date and maximum value from
each combo. (If the maximum date is more than two years
old, and the maximum value is 0 or less, then all the dates
are more than two years old and all the values are 0 or
less.)
The second query, would be run against the answer table from
the first, and would look something like:
ANS1.DB | Lead Source | FCD | VOO |
delete | | <01/19/1999 | <=0 |
This will delete all records where the date is less than two
years ago AND the value is less than or equal to 0. (Note,
your options for the 'two years ago' date are to enter it by
hand, to use code to generate that criteria, or to be vague
about what was two years ago and use: "<TODAY-730" (without
the quotes) - 730 is the number of days to subtract from
today.)
Finally, your third query would be (based on what your
original post said):
ANS1.DB | Lead Source |
| _join1 |
Contact2.db | Lead Type | Lead Source |
| Check _join1 | Check |
Contact2.db | Lead
Rating |
| Check Excellent or Good or Fair or Qualified,
calc count all |
Contact2.db | Value of
Order |
| calc sum all as Order Value Sum, calc count as
# of Orders |
This would get you only those Lead Sources with at least one
record newer than two years ago OR with a sum value of more
than 0.
I think. I haven't tested any of this, it's all off the top
of my head. Also, the table you posted has no values in the
VOO field, and only one Lead Source where all the records
are older than two years, so testing wouldn't have done me
much good
Try this out, and let us know if you need more help.
Regards,
Liz
---
Get the lead out before you reply
Steve Caple
> I've downloaded the files, because one of the CTechs is sure to
> come along and delete them.
Getting to it belatedly - but it may save some occasional European readers
some d/l time.
Consider it deleted as of 12:30pm Pacific time.
--
Steve Caple [C_Tech - Paradox]
Jeff D.
to find the correct solution.
Query #1 seems to work
Query #2 seems to work (I have check the db to verify info generated from
query)
Query #3 does not seem to work...it does not remove:
- Lead Sources where "all leads/records (within a Lead Source) with a First
Contact Date over two years old & have a cumulative total of zero sales.
Any ideas?
best regards,
Jeff Dollman
Liz
The third query isn't meant to remove anything. It's meant to
retrieve the applicable records:
"This would get you only those Lead Sources with at least one
record newer than two years ago OR with a sum value of more than
0."
Did you see my follow-up post about moving the _join1 to the
proper field?
Could you post the actual text of your three queries (don't try
to simplify, give us the whole thing)? (Open them in Notepad and
copy and paste the entire query into a message here - don't
attach the actual qbe file.)
Thanks,
Liz
Jeff D.
Two problems with the report generated:
1. Lead Sources where "all leads/records (within a Lead Source) with a
First Contact Date over two years old & have a cumulative total of zero
sales.
two lead types listed...the total # of leads is double what it should be.
If there are 10 lead types listed, the total is 10 times what it should be.
Query #1
Query
ANSWER: :PRIV:ANSWER.DB
Lyon.DB | First Contact Date | Lead Source |
| calc max as First Contact Date | Check |
Lyon.DB | Value of Order |
| calc max as Value of Order |
EndQuery
Query #2
Query
ANSWER: :PRIV:ANSWER.DB
:PRIV:Answer.db | First Contact Date | Value of Order |
Delete | <01/01/1999 | <=0 |
EndQuery
Query #3
Query
ANSWER: :PRIV:ANSWER.DB
SORT: Lyon.DB->"Lead Rating", Lyon.DB->"Lead Type", Lyon.DB->"Lead Source",
5,
6, Lyon.DB->"Lead Rating"
Lyon.DB | Lead Type | Lead Source |
| Check | Check _1 |
Lyon.DB | Lead Rating |
| Check Excellent or Good or Fair or Qualified, calc count all |
Lyon.DB | Value of Order |
| calc sum all as Order Value Sum, calc count as # of Orders |
Lyon.DB | Followup Returned |
| blank or >=2/1/2000, <=1/31/2001 |
:PRIV:Answer.db | Lead Source |
| _1 |
EndQuery
Am I a pain or what?
Jeff Dollman
Liz <lea...@aros.net> wrote in article <3A6DE7C0...@aros.net>...
Liz
First, we can deal with your other problem (#2 below) after
we've determined that the first two queries are indeed
getting the Lead Source values of interest.
Step one, and it may be a pain, is to determine all the Lead
Source values you want in the report. If you have to do
this manually, do it (suggest you query all fields from your
source table and delete all the records from the resulting
table which you do NOT want in your report - strategically
sorting the unkeyed answer table may make this easier).
Step two is to run the first two queries I gave you (the
ones you show below) and look at the list of Lead Source
values and make sure that all the ones from step one and
only the ones from step one are in the answer table from
this step (suggest not using ANSWER.DB as a table name for
any of these steps).
If step two reveals that the first two queries yield the
Lead Source values of interest, then query three is getting
the records of interest and there's some other problem if
records _not_ of interest show in the report - perhaps show
us the report's data model. If step two shows that the
results of the first two queries are not what you want, then
you need to re-state what you want.
To my mind (perhaps someone else can step in here and find
my error), the first two queries will get rid of all Lead
Source values where all of their "First Contact Date" values
are <01/01/1999 _and_ all of their "Value of Order" values
are <=0. This leaves you with the Lead Source values of
interest, and if you use this table in the third query, you
should only get records from LYON.DB where Lead Source
matches the limited Lead Source from the first two
queries...
Let me know how step one and step two go...
Liz
"Jeff D." wrote:
>
> Liz: