[AccessD] Consolidating two sets of values

[David Emerson]

Thanks Jack and Paul.  Much appreciated.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland
Sent: Saturday, 2 May 2015 5:06 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Consolidating two sets of values

couple of ways you can do this, you can use the IIF as previously said but
the problem comes where you require a lot of names to be joined, or you can
add an additional field to the employee table and put a code in there

Name         JoinCode
Bob           GEN
Jim            GEN
Joe            JOE
Lee            LEE

and run the query based on the JoinCode (or whatever you call it field)

On 2 May 2015 at 04:43, jack drawbridge <jackandpat.d at gmail.com> wrote:

> David;
>
> Small sample  based on your data:
>
> Select Names, sumofsales from
> (
> SELECT IIf(Empname="Bob" Or EmpName="Jim","GEN",empname) AS Names,
> Sum(tblSales.Sales) AS SumOfSales
> FROM tblSales
> GROUP BY IIf(Empname="Bob" Or EmpName="Jim","GEN",empname)
> )
>
> Gives result:
>
> *Query3*  Names sumofsales   GEN $7,000.00  Joe $1,000.00  Lee $8,000.00
>
> Good luck.
>
> On Fri, May 1, 2015 at 11:04 PM, David Emerson <newsgrps at dalyn.co.nz>
> wrote:
>
> > Hi Listers,
> >
> >
> >
> > I am having a brain freeze at the moment.  I have a table with three
> > fields:
> > Employee, Date, Sales.
> >
> >
> >
> > Up to now the client wanted the total for each employee.  No problem:
> >
> >
> >
> > SELECT tblSales.Employee, Sum(tblSales.Sales) AS SumOfSales
> >
> > FROM tblSales
> >
> > GROUP BY tblSales.Employee;
> >
> >
> >
> > Now the client wants the same except he wants two employee figures 
> > to be combined into one figure.  For example
> >
> >
> >
> > Results of current query:
> >
> >
> >
> > Bob $5000
> >
> > Jim $2000
> >
> > Joe $1000
> >
> > Lee $8000
> >
> >
> >
> > Now let's say that Bob and Jim are to be combined as Gen.  The 
> > results
> are
> > now to be:
> >
> >
> >
> > Gen $7000
> >
> > Joe $1000
> >
> > Lee $8000
> >
> >
> >
> > What is the syntax I should use?
> >
> >
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> >
> >
> >
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com