Select Query & Crosstab Query

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

Any guidance or help is very much appreciated.

I have 1 table with the following fields:
NetSales, Vendor, Customer Name, Period, etc;

I originally tried to do this in a crosstab query so I can have
"Customer Name" as the Row Heading, "Vendor" as the Group Heading and
"NetSales" as the value. Everything works fine however I have over 60
different vendors in this table and I only want to see sales of 9 so I
entered the names of the vendor in the "Criteria" section which
resulted in 90% of what I needed. But to make it even more complicated
I need to group 4 of those 9 vendors together under one column
heading. So when it's all done i should have 4 vendors in the column
headings but the fourth one should be made up of the sum of 4 other
vendors and

Vend1 Vend2 Vend3 Vend4 ConsolidatedVendor(Vend5 & Vend6
& Vend7 & Vend8)

I've read many tutorials and posts on this and have realized that I
need to do individual Crosstab queries for each column heading and
then do a final select query to bring all of those other queries
together. However, when I try to do the CrossTab query to combine the
4 vendors into 1 it still list each vendor individually as a column
heading. So then I tried doing a Select Query instead to combine the 4
vendor's sales into 1 but now I'm getting multiple records per
customer. I think it has to do with grouping but I've been working on
this for so long that now I think I'm just going around in circles.

If anyone can point me in the right direction I would greatly
appreciate it. A sample of the SQL code is below.



SELECT [2009Sales].[Customer Name], Sum([2009Sales].NetSales) AS
ConsolidatedVendor
FROM 2009Sales
WHERE ((([2009Sales].Period)=10))
GROUP BY [2009Sales].[Customer Name], [2009Sales].[Outside Rep],
[2009Sales].Period, [2009Sales].Vendor
HAVING ((([2009Sales].Vendor)="VEND5" Or ([2009Sales].Vendor)="VEND6"
Or ([2009Sales].Vendor)="VEND7" Or ([2009Sales].Vendor)="VEND8"));
 
Post the SQL of your ORIGINAL crosstab query. That would help. Generically,
you could use a query like the following.

TRANSFORM Sum(NetSales]) as TotalSales
SELECT [Customer Name]
FROM [Name of the table or query]
WHERE Vendor In
("Vendor1","Vendor2","Vendor3","Vendor4","Vendor5","Vendor6","Vendor7","Vendor8","Vendor9")
GROUP BY
PIVOT IIF(Vendor in
("Vendor1","Vendor2","Vendor3","Vendor4","Vendor5"),Vendor,"Consolidated")
IN ("Vendor1","Vendor2","Vendor3","Vendor4","Vendor5","Consolidated")

A bit neater would would be to have a table of the vendors you are interested
in and a flag field that would tell you which ones to consolidate.

Table: VendorGroups
VendorName
VendorGroup (simple case of no group or one group this could be a yes/no field)

TRANSFORM Sum(NetSales]) as TotalSales
SELECT [Customer Name]
FROM [Name of the table or query] as A INNER JOIN VendorGroup as V
ON A.Vendor = V.Vendor
GROUP BY
PIVOT IIF(V.VendorGroup= True,"Consolidated",V.Vendor)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Any guidance or help is very much appreciated.

I have 1 table with the following fields:
NetSales, Vendor, Customer Name, Period, etc;

I originally tried to do this in a crosstab query so I can have
"Customer Name" as the Row Heading, "Vendor" as the Group Heading and
"NetSales" as the value. Everything works fine however I have over 60
different vendors in this table and I only want to see sales of 9 so I
entered the names of the vendor in the "Criteria" section which
resulted in 90% of what I needed. But to make it even more complicated
I need to group 4 of those 9 vendors together under one column
heading. So when it's all done i should have 4 vendors in the column
headings but the fourth one should be made up of the sum of 4 other
vendors and

Vend1 Vend2 Vend3 Vend4 ConsolidatedVendor(Vend5 & Vend6
& Vend7 & Vend8)

I've read many tutorials and posts on this and have realized that I
need to do individual Crosstab queries for each column heading and
then do a final select query to bring all of those other queries
together. However, when I try to do the CrossTab query to combine the
4 vendors into 1 it still list each vendor individually as a column
heading. So then I tried doing a Select Query instead to combine the 4
vendor's sales into 1 but now I'm getting multiple records per
customer. I think it has to do with grouping but I've been working on
this for so long that now I think I'm just going around in circles.

If anyone can point me in the right direction I would greatly
appreciate it. A sample of the SQL code is below.



SELECT [2009Sales].[Customer Name], Sum([2009Sales].NetSales) AS
ConsolidatedVendor
FROM 2009Sales
WHERE ((([2009Sales].Period)=10))
GROUP BY [2009Sales].[Customer Name], [2009Sales].[Outside Rep],
[2009Sales].Period, [2009Sales].Vendor
HAVING ((([2009Sales].Vendor)="VEND5" Or ([2009Sales].Vendor)="VEND6"
Or ([2009Sales].Vendor)="VEND7" Or ([2009Sales].Vendor)="VEND8"));
Click to expand...
 
Back
Top