Query giving table heading Cust Name and 12 monthly sales (ie J F

[Guest]

My database is as follows:
tbl_Bill_To_Address (Bill_To_Code (primary key), Bill_To_Customer)
tbl_Rep (Rep_Code (Primary key), Representative)
tbl_Territory (Territory_Code (Primary key), Territory)
tbl_Product (Stock_Code (primary key), Stock_Description)
tbl_Del_Point(Del_Point, Stock_Code (compound Primary key), Bill_To_Code,
Territory_Code, Rep_Code, State)
tbl_Sales_Budget_2005(Delivery_Point, Stock_Code, Category, Month (compound
primary key), Value)

Category has the domain: BudgetSales, BudgetProfit, ActualSales, ActualProfit

This works really well but I want to create queries which gives me the
following tables (for example):
Table Name: Sales Budget Rep by Customer 2005
Representative Customer Jan Feb Mar Apr etc .... Dec

I have had a few goes but the query that appears to work, only works for 5
months and when I add another, it says the query is too complicated (which I
guess it is). What I do is select all the tables except
tbl_Sales_Budget_2005 and query tables which I created for each month and
having the following information: Delivery_Point, Stock_Code, Category,
Month, Value (where Month will be Apr or May or Jun etc). Then I join
Delivery_Point and Stock_Code of each of these query tables to Delivery_Point
and Stock_Code in the Deliver_Point table.

This appears to work for Jan, Feb, Mar, Apr but if I add the query table
with May data, I get a message saying the query was too complex!

With the data I have at hand, sure it must be possible to easily create the
query that I want?

Can somebody help me out? (Otherwise, I have to cut and paste to Excel!
)

Thanks
Bon

 

[John Vinson]

This works really well but I want to create queries which gives me the
following tables (for example):
Table Name: Sales Budget Rep by Customer 2005
Representative Customer Jan Feb Mar Apr etc .... Dec

Sounds like you need a Crosstab Query. See the online help for
Crosstab, or use the query wizard.

John W. Vinson[MVP]