New to queries

[Support]

I have created several queries that examine a table containing call data
for all our retail outlets. Each query gives me the sum and cost of the
calls eg:
SELECT DISTINCTROW tblTotal.CalledNo, Sum(tblTotal.Cost) AS [Sum Of Cost],
Count(*) AS [Count Of tblTotal]
FROM tblTotal
GROUP BY tblTotal.CalledNo
HAVING (((tblTotal.CalledNo)="01234 567890"));
What I would now like to do is present all the queries (18 in all) and show
how many calls there were in total and how much the total cost was.
I've tried using the query wizard to select mulitple queries but am getting
an error telling me I have chosen fields the wizard cannot connect to.
Do I need to create a new query that asks for all my numbers or can I use my
present queries?
Thanks

 

[PC Datasheet]

The query wizard only works when you are building a new query from multiple
tables. When you select a query to include in your new query, you get the
message "you have chosen fields the wizard cannot connect to" because the
wizard looks for the relationships that you have created in the relationship
window. Since the query you have selected does not have an established
relationship to any other object, the wizard can not find a relationship and
thus gives you the message. You have done nothing wrong! Just use the wizard
to include whatever tables you want in the new query and then finish
building the new query by manually adding your queries to the query window
and then manually pull down the fields into the query grid.

 

[Support]

Hi
thanks for the help but I don't see what you are getting at I'm afraid.
The only thing I can think of is that I add in the expression:
HAVING (((tblTotal.CalledNo)="01234 56789" Or (tblTotal.CalledNo)="01987
654321")
and so on with all the numbers I want. This will give me the totals for
each number but what I really
want is just one field showing sum and one field showing total cost without
having separate records which I will have in the eg above.
I have also the below but this isn't what I want either.

SELECT [qryCalled0870 8501150].[Sum Of Cost], [qryCalled0870 8501150].[Count
Of tblTotal], qryCalledBrighton.[Sum Of Cost], qryCalledBrighton.[Count Of
tblTotal]
FROM [qryCalled0870 8501150], qryCalledBrighton;

Thanks

The query wizard only works when you are building a new query from multiple
tables. When you select a query to include in your new query, you get the
message "you have chosen fields the wizard cannot connect to" because the
wizard looks for the relationships that you have created in the relationship
window. Since the query you have selected does not have an established
relationship to any other object, the wizard can not find a relationship and
thus gives you the message. You have done nothing wrong! Just use the wizard
to include whatever tables you want in the new query and then finish
building the new query by manually adding your queries to the query window
and then manually pull down the fields into the query grid.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I have created several queries that examine a table containing call data
for all our retail outlets. Each query gives me the sum and cost of the
calls eg:
SELECT DISTINCTROW tblTotal.CalledNo, Sum(tblTotal.Cost) AS [Sum Of Cost],
Count(*) AS [Count Of tblTotal]
FROM tblTotal
GROUP BY tblTotal.CalledNo
HAVING (((tblTotal.CalledNo)="01234 567890"));
What I would now like to do is present all the queries (18 in all) and show
how many calls there were in total and how much the total cost was.
I've tried using the query wizard to select mulitple queries but am getting
an error telling me I have chosen fields the wizard cannot connect to.
Do I need to create a new query that asks for all my numbers or can I

use

my
present queries?
Thanks

 

[John Vinson]

I have created several queries that examine a table containing call data
for all our retail outlets. Each query gives me the sum and cost of the
calls eg:
SELECT DISTINCTROW tblTotal.CalledNo, Sum(tblTotal.Cost) AS [Sum Of Cost],
Count(*) AS [Count Of tblTotal]
FROM tblTotal
GROUP BY tblTotal.CalledNo
HAVING (((tblTotal.CalledNo)="01234 567890"));
What I would now like to do is present all the queries (18 in all) and show
how many calls there were in total and how much the total cost was.

If you don't want to Group By CalledNo - i.e. if you want a total over
all calls - change the Group By operator on the Totals line under
CalledNo to "Where". Then on the criteria line, if you have several
calls to sum, put

IN ("01234 567890", "0987 654321", "0444 231223"... <and so on>)

The SQL will be

SELECT Sum(tblTotal.Cost) AS [Sum Of Cost],
Count(*) AS [Count Of tblTotal]
FROM tblTotal
WHERE (((tblTotal.CalledNo) IN ("01234 567890", "0987 654321", "0444
231223"));


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps