Help Needed please

[quinncooper1988]

I need a little help on some queries.

Basically I have multiple queries that have a total price on them. (Data gathered from forms)

Now I need, if possible, to total up all these prices from the multiple queries, and have one total figure, that I can present in a form, or report.

Is this something that Access can do, total up various figures from various different queries? Is it easy to do?

Any help would be much appreciated.

Thanks

 

[Uncle Gizmo]

Can you post the SQL version for each of the queries?

 

[quinncooper1988]

Can you post the SQL version for each of the queries?

I tried getting it on there, however some reason it wont work, mite be the works systems blocking it or something.

 

[Dennisk]

the basic technique is to use a union query to merge the field you want to total,

here is an example

Select Myfield from Query1

Union all
Select myfield from Query2

if this was called qryUnion then you can create a totals query on the union query

select Sum(myField) as SumOfMyField FROM QryUnion

 

[Uncle Gizmo]

I tried getting it on there, however some reason it wont work, mite be the works systems blocking it or something.

One of the views available to you in MS access for your queries is an SQL view. This view is a simple text version of the query, which you should be able to post here just like any other text.

 

[quinncooper1988]

One of the views available to you in MS access for your queries is an SQL view. This view is a simple text version of the query, which you should be able to post here just like any other text.

Here is the SQL view: this is not the proper one, as im at home now, could not send the database to my email address, so I had to quickly copy the same work as I have already done at work.

Here is query one:

SELECT Sum([TEST1 6422].Price) AS SumOfPrice, [TEST1 6422].Approved
FROM [TEST1 6422]
GROUP BY [TEST1 6422].Approved
HAVING ((([TEST1 6422].Approved)=True))
ORDER BY Sum([TEST1 6422].Price);


Query two is exactly the same, but the information is from another table.


DENNISK,

thanks, I tried the example you supplied, however, every time I clicked on the query, a window box came up, asking for price? so I type a digit it, and it just comes with with teh digit I typed in, then -1.

Im gonna keep trying see, if i can figure it out.

cheers

 

[Uncle Gizmo]

I don't think you need the GROUP BY and HAVING clauses, try replacing them with a WHERE Clause:


WHERE ((([TEST1 6422].Approved)=True))