Simple or not?

[danikuper]

This query may be simple but I can't make it work...

Here's the situation. I have several products, each product belongs to a product family. For example:

Family A contains:
Product A.1
Product A.2

Family B contains:
Product B.1
Product B.2

I have the following invoices:
Invoice #1
Product A.1.....$1,000
Product A.2.....$500
TOTAL:...........$1,500

Invoice #2
Product B.1......$500
Product B.2......$200
TOTAL:............$700

Invoice #3
Product A.2.....$500
TOTAL:..........$500


I want to query my invoices and get the total sales for the Families. The catch is this: I can only add total $$ for an invoice that has either A.1 or B.1 as products.

For the invoices above I would get:
Family A: $1,500
Family B: $700

I cannot add invoice #3 to the total of Family A because it has only A.2 and not A.1.

My tables look like:
tblInvoice
InvoiceID (PK)
CustomerName
InvoiceDate

tblItem
InvoiceID (PK)
ProductID (PK)
Revenue

tblFamily
FamilyID (PK)
FamilyName

tblProduct
ProductID(PK)
ProductName
FamilyID (FK)

Is this query possible? How would I do that?

Help!
Thanks in advance
daniel

 

[o1110010]

What have you tried so far? A cross table query didn't work?

 

[Rich]

Group by the InvoicePK and not the FamilyPK

 

[Pat Hartman]

Create a query that joins tblItem to tblProduct on ProductID and tblProduct to tblFamily on FamilyID. Then select FamilyName from tblFamily, and Revenue from tblItem. Select the sigma button to turn the query into a Totals query. Change "Group By" to "Sum" for the Revenue field. This will give you a total revenue by family. Save this query. You can then use this query as the recordsource for a crosstab query if that's what you need.

If you need date information or selection criteria, you'll need to include tblInvoice in the join. If you add any selection critera (such as a date range) and you ultimately want to use this as the basis for a crosstab query, you MUST explicitly define any parameters. The Crosstab query will not work unless you do this.

 

[danikuper]

Not quite

Pat,

If I do what you suggested, I'm going to get as result of my query something like:

Family A - $$$$
Family B - $$$$

And the query will add all items in all invoices that belong to each of the families. The problem is that in invoice # 3 in my example (1st message on this thread) the value of that item cannot be included in the total revenue for Family A.

I think I'll have to create two queries, one that will give me invoice numbers where products A.1 and B.1 are present and then create a second query that will sum the revenue for each item belonging to invoices matching my invoice numbers in the first query... does this make sense?

thanks for the help.
daniel

Create a query that joins tblItem to tblProduct on ProductID and tblProduct to tblFamily on FamilyID. Then select FamilyName from tblFamily, and Revenue from tblItem. Select the sigma button to turn the query into a Totals query. Change "Group By" to "Sum" for the Revenue field. This will give you a total revenue by family. Save this query. You can then use this query as the recordsource for a crosstab query if that's what you need.

If you need date information or selection criteria, you'll need to include tblInvoice in the join. If you add any selection critera (such as a date range) and you ultimately want to use this as the basis for a crosstab query, you MUST explicitly define any parameters. The Crosstab query will not work unless you do this.

 

[Pat Hartman]

Sorry, I read this

I cannot add invoice #3 to the total of Family A because it has only A.2 and not A.1.

to mean that you wanted to but couldn't as opposed to that you wanted to include ONLY invoices where items from both familes were ordered.

You can do this with nested queries.

Query1 - select distinct families:
Select i.InvoiceID, f.FamilyName
From (tblItem as i Inner Join tblProduct as p on i.ProductID = p.ProductID) Inner Join tblFamily as f ON p.FamilyID = f.FamilyID
Group By i.InvoiceID, f.FamilyName;

Query2 - counts the number of families that an invoice has:
Select q.InvoiceID, Count(*) CountOfFamilies
From query1 as q
Group By i.InvoiceID;

query3 - bring it all together:
Select f.FamilyName, Sum(i.Revenue) As SumOfRevenue
From ((tblItem as i Inner Join tblProduct as p on i.ProductID = p.ProductID) Inner Join tblFamily as f ON p.FamilyID = f.FamilyID) InnerJoin query2 as q ON i.InvoiceID = q.InvoiceID
Where q.CountOfFamilies > 1
Group By f.FamilyName;

I'm sure that someone will offer this solution as a subselect. I choose the nested queries for 2 reasons:
1. They are easier to test since they can be tested in pieces.
2. Access optimizes them far better than subselects especially coorelated subselects. Therefore they tend to run faster on larger sets of data.

The queries have not been tested so be ware of missing parens, etc.