Query Question

[Sully38]

I have Two tables one contain an indexed field and currency totals. The second table contains (2)indexed fields. What I am trying to do is run a query that will group table a to the 2 fields in table B and only carry the currency field in once. Everytime I try to run this I bring in the currency value on as many hits I have. Any ideas to how to accomplish this is appreciated.

 

[Joe4]

Are you saying that you are getting multiple rows per index and you just want one total currency amount per index?

If so, click the Totals button in your query builder. This will put the words "Group By" under your fields. Under the Currency field, change "Group By" to "Sum", and this should do it.

 

[Sully38]

No that part was easy:
Table A

Tom 2.00
Greg 3.00

Table B

greg chevy
greg Ford
greg Nissan

My query returns: (using a basic table a. field 1 = table b.field 1)
greg chevy 3.00
greg Ford 3.00
greg Nissan 3.00


So when I total it is $9.00

Can I make that $3.00 just show up once not all 3 times?

 

[SydneyGeek]

Easy in a report as long as you don't want a Grand Total -- a pain in a query.
Using the Report Wizard, build a report based on the query you described.
Set it to Group on Name, and Sum on Value (the currency field). You should end up with
Tom $9.00
Chevy $3.00
Ford $3.00
Nissan $3.00
etc. Now to change that. Design view...
In the Group Header (or Footer, depending how the wizard behaved), delete the calculated field with =Sum([Value])
CUT the Value field from the Detail section and paste it into the Group Header. If the Group Footer is displayed, View | Sorting and Grouping. Select Value, and change it to not display the Footer.

Give it a spin.

Denis