How to combine two queries into one?

[Dawn]

There are two table ,
Table1: name ,city ,salary,
Table2:city, province
Query1:SELECT table1.name,table1,city, Sum(table1.[salary]) as
sumofsalryofperson
FROM table1
GROUP BY table1.[ name], table1.[ city];

Query2: SELECT Table2.[ province], Sum(Query1.[sumofsalryofperson])
FROM Table2 INNER JOIN Query1 ON Table2.[city] = Query1.[city]
GROUP BY Table2.[province];

Is there anyway to combine above two queries into one?
Many thanks

 

[Dale Fye]

Dawn,

In your first query, you are grouping by Name and City. This implies that
each person may have more than one record in the table, is that correct?

Is it pertinent? In your second query, it looks like you are trying to get
the sum of the salaries of all the people in the province, why are you not
doing the same thing for City in the first query?

If what you want is a result set that shows the sum of salaries for the
province and for each of the cities in the province, I think I would do it
like:

SELECT T2.Province, T2.City, Sum(T1.Salary) as SumOfSalaries
FROM table1 as T1
INNER JOIN table2 as T2
ON T1.City = T2.City
GROUP BY T2.Province, T2.City
UNION
SELECT T2.Province, First("") as City, Sum(T1.Salary) as SumOfSalaries
FROM table1 as T1
INNER JOIN table2 as T2
ON T1.City = T2.City
GROUP BY T2.Province

In order to create this query, you will need to go into the SQL view.
Additionally, in order to use a UNION query, each part of the query must
contain the same number of fields and the fields must match the data type of
the fields in the other half of the union.

My recommendation is that you create the two parts of the query independant
of one another, make sure they are working as you want, then copy the SQL and
paste it into a new query.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dawn]

Dale,
First I’d like to thank for your patient explanation. I have some further
questions to ask ,hope you can rely with the same patience,thanks.
1. For each person there are several records in the table 1, but for every
table1.name its table1.city is unique, in fact table1.salary refers to
account balance. So the primary key in table1 is account no., but is ignored
in both two queries. I group both by name and city ,because the field city is
going to be inner joined with table2.city and also because whether to be
grouped by either name or by two fields name and city duo to the exclusive
relation between name and city, the results are same, except the latter will
add a city field beside field name.
2. Before seeing your sql proposed, I never thought of to use “unionâ€
function, thank you again to give me a new way of thinking. And I’v tried in
the sql view platform to put in your sql, it appeared not as I wanted,
because after running the query, it turned out with records not with unique
province, as I explained as above I supposed the results turned out with the
lists of provinces (ignoring grouped by city) and the total account balance
within the city.
3. because I am not familiar with the meaning of First("") as City, I
first omitted in the sql view platform, but while running it returned with
error, as long as I put the sentence in ,it runs well, can you explain the
sentence for me ,if it need explain the whole union query written in sql?
4.I am absolutely sorry as to beg you to write another query for me in sql,
according to the information above.
Many thanks.
Dawn