Simple Query Question

[tom]

I have a query with one field that has duplicate
values. There is another field with currency data.
I want to print the sum of the currency data for the
fields with duplicate values.

For example

I want the following data:

1 x $1
2 x $1
3 y $1

To print out like this:

1 x $2
2 x $2
3 y $1

 

['69 Camaro]

Hi, Tom.

I have a query with one field that has duplicate
values. There is another field with currency data.
I want to print the sum of the currency data for the
fields with duplicate values.

Assuming the column with duplicate values is called BookTitle and the column
with currency data is called Fine, try the following syntax for your query:

SELECT BookTitle,
(SELECT SUM(Fine)
FROM tblBookFines AS BF
WHERE BF.BookTitle = B.BookTitle) AS TotFine
FROM tblBookFines AS B
ORDER BY BookTitle;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[tom]

Can this be done in design view as the syntax is
a bit confusing on a more complicated query?

 

['69 Camaro]

Hi, Tom.

Can this be done in design view

Yes, but it's best to post the SQL of a query to save time, especially when
subqueries are involved. Unless, of course, you'd like to hire me for the
half hour or so it would take to do the steps and write them out for you,
then test them one by one to make sure they'll work for you before I post it
to this newsgroup or send it to you via E-mail.

To use the SQL example, create a new query and open it in SQL View (select
the View -> SQL View menu) and paste the example SQL statement into the
white space. Replace BookTitle with the name of your column with duplicate
values, replace Fine with the name of your currency column, and replace
tblBookFines with the name of your table. Now run the query, and you'll see
the results in Datasheet View. You can also change to Design View to see
what you would have needed to do to create that SQL statement with the QBE
grid.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.