is this query possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that joins to tables. From that query I need to make another
query. The query will give the invoice number, product id, percentage,
quantity. If the percentage is higher than 13, then sum those quantities.
If not, then sum those quantities. Eventually, I will put this into a
report. Do I have to make two queries or is it possible to run one?

Thanks,
 
Mel,

Make a calculated field in the query, something like this...
Over13: [Percentage]>0.13

Then you can Group By this field in a Totals Query, and Sum the
Quantity, and you should get separate totals for those with Percentage
over and under 13%.
 
I learned today that it is possible to write a single query that queries a
query.

I.e. Query 1 performs action 1 and gives results 1. Query 2 performs action
2 on query 1 and gives results 2.

So if Qry1 was:

Select [field1], [field2]
from [Table1]
WHERE [some statement1]

and Qry2 was:
Select [Qry1].[Field1], [Qry1].[Field2]
from [Table1] JOIN qry1 on Table1.field1 = qry1.field1
Where [Some statement2]

you could combine the two queries into a single query like this:

Select [Qry1].[Field1], [Qry1].[Field2]
from [Table1] JOIN
(
Select [field1], [field2]
from [Table1]
WHERE [some statement1]
) as Qry1
on Table1.field1 = qry1.field1
Where [Some statement2]

I have written this rather generically, but you can see the "breaks" of
where to "insert" the appropriate statement(s) necessary for your
query/queries.
 
Back
Top