Basic Report Question

[igendreau]

I have a table and each record lists a Company Name, Order Date, and an
Extended Sell Price. What I need to create is a report with 4
columns: Company Name, 2004 Sales, 2005 Sales, 2006 Sales. Each year's
sales will be the sum of the Extended Sell Price for that company
during that year. So the final result will be a list of companies, and
to the right of them, each companies sales totals for each year.
What's the easiest way to do this?

[Jeff L]

Make a query on your table.
Chose
CompanyName
2004 Sales: DSum("ExtendedSellPrice","YourTableName", "Year(OrderDate)
= 2004 And CompanyName = '" & [CompanyName] & "'")
2005 Sales: DSum("ExtendedSellPrice","YourTableName", "Year(OrderDate)
= 2005 And CompanyName = '" & [CompanyName] & "'")
2006 Sales: DSum("ExtendedSellPrice","YourTableName", "Year(OrderDate)
= 2006 And CompanyName = '" & [CompanyName] & "'")
as your fields.

You will want to select unique values, so change the setting in the
query's properties. Right click in the top window, select properties,
change unique values to yes.

Hope that helps!

[pietlinden]

igendreau wrote:

I have a table and each record lists a Company Name, Order Date, and an
Extended Sell Price. What I need to create is a report with 4
columns: Company Name, 2004 Sales, 2005 Sales, 2006 Sales. Each year's
sales will be the sum of the Extended Sell Price for that company
during that year. So the final result will be a list of companies, and
to the right of them, each companies sales totals for each year.
What's the easiest way to do this?

Any reason you can't use the crosstab query? There's an example of
almost exactly what you're asking in NWind.

TRANSFORM Sum(CCur([Order
Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS
OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1995# And #12/31/1995#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr
3","Qtr 4");

[pietlinden]

igendreau wrote:

I have a table and each record lists a Company Name, Order Date, and an
Extended Sell Price. What I need to create is a report with 4
columns: Company Name, 2004 Sales, 2005 Sales, 2006 Sales. Each year's
sales will be the sum of the Extended Sell Price for that company
during that year. So the final result will be a list of companies, and
to the right of them, each companies sales totals for each year.
What's the easiest way to do this?

How about this?

TRANSFORM Sum(xtbBase.[Extended Sell Price]) AS [SumOfExtended Sell
Price]
SELECT xtbBase.CompanyName
FROM xtbBase
GROUP BY xtbBase.CompanyName
PIVOT Format([OrderDate],"yyyy");