Query 
Author Message
 Query

I'm using PDOX 9 and I have a query question.

I have a table with various charges per client for each day. I will have
perhaps one to fif{*filter*}
(1-15) clients with one to five (1-5) charges for each day. I would like
to develop a query
that gives me the name of each client with the total amount charged for
a specific day.
Example:

 John Smith 3/20/00  100.00
 John Smith 3/20/00   50.00
 John Smith 3/20/00   25.00

 John Doe 3/20/00  100.00

I want to develop a report that will show:

 John Smith 3/20/00  175.00

 John Doe 3/20/00  100.00

Where do I start? I suspect that this requires several queries based on
each other but I am
hoping that there is an easier way. The only way I can imagine doing
this is to Query for
each name place these names into a dummy table then query each name and
do a calc placing this
information into another table. There must be an easier way but I am at
a loss to figure it out.

Thanks for your help. ron



Fri, 06 Sep 2002 03:00:00 GMT
 Query

Hi, Ron

Quote:

> I have a table with various charges per client for each day. I will have
> perhaps one to fif{*filter*}
> (1-15) clients with one to five (1-5) charges for each day. I would like
> to develop a query
> that gives me the name of each client with the total amount charged for
> a specific day.
> Example:

>  John Smith 3/20/00  100.00
>  John Smith 3/20/00   50.00
>  John Smith 3/20/00   25.00

>  John Doe 3/20/00  100.00

> I want to develop a report that will show:

>  John Smith 3/20/00  175.00

>  John Doe 3/20/00  100.00

In your query you only have to check the fields NAME and DATE. Leave the
field CHARGE unchecked. In CHARGE write:

calc sum

If you want to define a name for this sum field in your answer table
(eg. DAILYSUM), make it

calc sum as dailysum

Pdox will calculate the sums grouped by each of the checked fields so
that each >different< value in those fields will "get a calculation of
it's own".

I'd advice you to look at the basics of making a query either in your
user manuals or in Pdox help files. The example(s) in the manual pages
361-362 deal with this.

Cheers
Timo

--
*  If it walks on four legs and is a vegetarian, it's called food.
*
*  >> Need to contact me? Just remove the "NOOSPAM" from the reply
*  >> address .



Sat, 07 Sep 2002 03:00:00 GMT
 Query
If your report doesn't need the clients with no activity for the given
day the following should work:

; start

Query
ANSWER: :PRIV:ANSWER.DB

MyTable.db | Client | Date   | Charge                      |
           | Check  | Check  | _join1, calc sum as Charges |

ChargeDt.db | Date   |
            | _join1 |

EndQuery

; end

Prior to running the query change the single record in the ChargeDt
table to be the date for the charges to be totaled. Optionally you could
place this table on a form and run the query from the form so everything
(editing the table and running the query) is together in one package. If
the date is always known from a business rule (Today - 1, e.g.) you
could skip the 2nd table and use a variable in MyTable's Date instead.

If your report need clients who have no charges it will be a little more
involved.

Mike Keating

Quote:

> I'm using PDOX 9 and I have a query question.

> I have a table with various charges per client for each day. I will have
> perhaps one to fif{*filter*}
> (1-15) clients with one to five (1-5) charges for each day. I would like
> to develop a query
> that gives me the name of each client with the total amount charged for
> a specific day.
> Example:

>  John Smith 3/20/00  100.00
>  John Smith 3/20/00   50.00
>  John Smith 3/20/00   25.00

>  John Doe 3/20/00  100.00

> I want to develop a report that will show:

>  John Smith 3/20/00  175.00

>  John Doe 3/20/00  100.00

> Where do I start? I suspect that this requires several queries based on
> each other but I am
> hoping that there is an easier way. The only way I can imagine doing
> this is to Query for
> each name place these names into a dummy table then query each name and
> do a calc placing this
> information into another table. There must be an easier way but I am at
> a loss to figure it out.

> Thanks for your help. ron



Sat, 07 Sep 2002 03:00:00 GMT
 Query
Timo,

Thanks for the quick response. My brain is frozen, such a simple answer, I
should have been able to figure the answer out. Thanks again, ron

Quote:

> Hi, Ron


> > I have a table with various charges per client for each day. I will have
> > perhaps one to fif{*filter*}
> > (1-15) clients with one to five (1-5) charges for each day. I would like
> > to develop a query
> > that gives me the name of each client with the total amount charged for
> > a specific day.
> > Example:

> >  John Smith 3/20/00  100.00
> >  John Smith 3/20/00   50.00
> >  John Smith 3/20/00   25.00

> >  John Doe 3/20/00  100.00

> > I want to develop a report that will show:

> >  John Smith 3/20/00  175.00

> >  John Doe 3/20/00  100.00

> In your query you only have to check the fields NAME and DATE. Leave the
> field CHARGE unchecked. In CHARGE write:

> calc sum

> If you want to define a name for this sum field in your answer table
> (eg. DAILYSUM), make it

> calc sum as dailysum

> Pdox will calculate the sums grouped by each of the checked fields so
> that each >different< value in those fields will "get a calculation of
> it's own".

> I'd advice you to look at the basics of making a query either in your
> user manuals or in Pdox help files. The example(s) in the manual pages
> 361-362 deal with this.

> Cheers
> Timo

> --
> *  If it walks on four legs and is a vegetarian, it's called food.
> *
> *  >> Need to contact me? Just remove the "NOOSPAM" from the reply
> *  >> address .



Sat, 07 Sep 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Query using UDF joined to a sub query vs Temp Table to sub Query

2. Query Query Query

3. Query From Query Results

4. Subject: SQL Query - MAX, Grouping and Sub query

5. access query converted to sql7 query

6. Part of query in query result

7. Newbie: Access97 query to SQL Server 7 query

8. Simple queries versus complex queries

9. Query based on query

10. MSSqlServer Select query Query

11. SQL Query to query a view with a changing name

12. Query runs from enterprise manager but not query analyser


 
Powered by phpBB® Forum Software