Sum of non matching results

[davey4444]

Apologies if my terminology isn't quite correct - I'm just starting to use Access but hopefully this request will make sense.

I have a table with sales in (TBL_Sales) as well as another (TBL_Key_Customers) which lists information about specific customers, in particular if they are part of a group e.g I would categorise Dave's Cars, Dave's Bikes and Dave's Coaches as being part of the Dave group.
I would like to query the TBL_Sales to see how many sales were made to the Dave group but then also what else was sold. e.g if Factory 1 sold 100 items of which 60 went to Dave's group then the remaining 40 would be shown as "Other".

Hopefully this all makes sense and is possible. Thanks.

 

[plog]

Without seeing your field names I can't give you specific instructions, but in general you would use an IIf statement to group your data like this:

DavesGroup: IIf ([CustomerGroup]="Dave", "Y", "N")

If you need more specific advice, list your table's field names along with some sample data in this format:

TableName
Field1Name, Field2Name, Field3Name
7, Larry, 4/8/2013
6, Peter, 12/11/2012

 

[davey4444]

Thanks for the quick reply, what you've said makes sense although I am slightly stuck in my Excel ways of thinking about things which doesn't help.

I tried your example but it couldn't quite work, here's some sample data -

TBL_Sales
Factory, Customer_ID, Customer_Name, Volume
London, 123, Dave's Coaches, 10
London,124, Mike's Taxis, 9
London, 1, Roger's Buses, 50
Birmingham, 125, Dave's Cars, 40

TBL_Key_Customers
Customer_ID, Customer_Group
123, Dave's Group
125, Dave's Group

Ideally I'd like my query output to show the following -

Factory , Customer Group , Volume
London , Dave's Group , 10
London , Other , 59
Birmingham , Dave's Group, 40

Thanks in advance.

 

[plog]

This would be your SQL:

SELECT Factory, IIF([Customer_Group]="Dave's Group", "Dave's Group", "Other") AS CustomerGroup, SUM(Volume) AS TotalVolume
FROM TBL_Sales LEFT JOIN TBL_Key_Customers ON TBL_Sales.Customer_ID=TBL_Key_Customers.Customer_ID
GROUP BY Factory, IIF([Customer_Group]="Dave's Group", "Dave's Group", "Other");

 

[davey4444]

Thanks all, it worked perfectly using IIF once I'd figured out my joins.