HELP Queries
eey...@netexpress.net
jo...@stone.foobar.co.uk wrote:
>Can you tell me how to setup a Queries as follows (or if this is the best way).
>My problem is that I want to sort out the numbers that are allowed from my phone bill.
>So if I have 50 calls on my phone bill I would want to sort out the numbers that are allowed and put
>to one side.
>So if the numbers allowed was 1234 ,5678, 9101112 say
Place all of the numbers which are allowed in one table. This way you
can easily add or delete from it.
>Then sort out the rest of the numbers that are not allowed.
Create a query and join this table to your billing table through the
phone numbers together. If you look at the information in your query
you will see this shows only those numbers which are allowed. You
want the exception query. Click on the join line to change it.
Select to show all records from the billing table and only those which
match from the allowed table.
In your query view, bring down the phone number field from the billing
table. In the criteria, type NULL. This will change to Is Null so
don't worry. Now view the query. This will show you all numbers
which are not in your allowed table.
>Put them together ,sort the time out of call ,and cost then total of the calls that are grouped
Your grouping and items would depend on how the billing table is
formatted. You can do a crosstab query to get results or look at the
Grouping query where you can define how your items are counted and
summed.
>So I would get a format as follows.
> COST
>CALL TO NUMBER TIME HR:MIN:SEC
>T Brown 777777 19:24 3:45
>T Brown 777777 13:43 :12
>T Brown 777777 22:09 .030
>
>F Smith 88888 14:09 4:23
>F.Smith 88888 8:30 8:34
>F Smith 88888 10:45 4:45:23
>.
>
>I have setup a Report with the headers as follows.
>
>CALL TO NUMBER DATE TIME HR:MIN:SEC COST
>T.SMITH 12345 05/OCT/97 19:24 10:24 .030
You can group and total within the report which will probably be
easier than on the query. You can add any field and multiply fields
to give you your totals.
Since your information will be within the detail, you can hide this
and show the total summary in your group footer to give you your
totals.
Patrick Thornburg
LAPI Software
eey...@netexpress.net