problem with report grouping

A

aminihojat

Hi all,
I have report1 that is opened by command button1 and its record source
comes from table1 :
Id fldname amount
1 smith 100
1 smith 100
1 smith 100
1 jack 100
2 mary 200
2 mary 200
2 mary 200
2 mary 200
report1: groups on Id field and sum for amount
id fldname amount
1 smith 400
2 mary 800
but I want to change smith to jack in the report1 like :
id fldname amount
1 jack 400
2 mary 800
how can I do it in vba or other solutions?
Thanks in advance
amini
 
D

Douglas J. Steele

Assuming your report is based on a query like:

SELECT Id, fldname, Sum(amount)
FROM MyTable
GROUP BY Id, fldname

try:

SELECT Id, IIf([fldname] = "jack", "smith", [fldname]) As fieldname,
Sum(amount)
FROM MyTable
GROUP BY Id, IIf([fldname] = "jack", "smith", [fldname])

If your sample was a simplification of what you're trying to do, and you
really have lots of translations you want done, I'd recommend creating a
translation table (Translation) with two text fields (NameFrom and NameTo)
so that you'd have

NameFrom NameTo
jack smith
jane doe
fred flintstone

You'd then use

SELECT Id, Nz(Translation.NameTo, [fldname]), Sum(amount)
FROM MyTable LEFT JOIN Translation
ON MyTable.fldname = Translation.NameFrom
GROUP BY Id, Nz(Translation.NameTo, [fldname])
 
A

aminihojat

Hi Douglas J. Steele
your solution was very helpful . i have asked a question a few days ago
about solar calendar but i haven't recevied any answer. would you please take
a look to that ?
many thanks to you in advance
amini

Douglas J. Steele said:
Assuming your report is based on a query like:

SELECT Id, fldname, Sum(amount)
FROM MyTable
GROUP BY Id, fldname

try:

SELECT Id, IIf([fldname] = "jack", "smith", [fldname]) As fieldname,
Sum(amount)
FROM MyTable
GROUP BY Id, IIf([fldname] = "jack", "smith", [fldname])

If your sample was a simplification of what you're trying to do, and you
really have lots of translations you want done, I'd recommend creating a
translation table (Translation) with two text fields (NameFrom and NameTo)
so that you'd have

NameFrom NameTo
jack smith
jane doe
fred flintstone

You'd then use

SELECT Id, Nz(Translation.NameTo, [fldname]), Sum(amount)
FROM MyTable LEFT JOIN Translation
ON MyTable.fldname = Translation.NameFrom
GROUP BY Id, Nz(Translation.NameTo, [fldname])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


aminihojat said:
Hi all,
I have report1 that is opened by command button1 and its record source
comes from table1 :
Id fldname amount
1 smith 100
1 smith 100
1 smith 100
1 jack 100
2 mary 200
2 mary 200
2 mary 200
2 mary 200
report1: groups on Id field and sum for amount
id fldname amount
1 smith 400
2 mary 800
but I want to change smith to jack in the report1 like :
id fldname amount
1 jack 400
2 mary 800
how can I do it in vba or other solutions?
Thanks in advance
amini
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top