SQL Query Quandry

**John Bell** · Jul 20 '05, 03:29 AM

Re: SQL Query Quandry

"Laphan" <news@DoNotEmai lMe.co.uk> wrote in message
news:40819679_3 @127.0.0.1...[color=blue]
> Hi All
>
> Sorry for the spare info. It wasn't because I didn't want to display it.[/color]
I[color=blue]
> just didn't know how to get the data that you needed. I think I've got it
> now so please note the following:
>
> 1) The DB is SQL 6.5.
>
> 2) My DDL for the tables in question:
>
> /****** Object: Table [dbo].[SALESCENTRES] Script Date: 17/04/04
> 21:27:00 ******/
> CREATE TABLE [dbo].[SALESCENTRES] (
> [SALESCENTREID] [T_SALESCENTRESD OMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[SPOOLEDINVOICEL INES] Script Date:[/color]
17/04/04[color=blue]
> 21:27:05 ******/
> CREATE TABLE [dbo].[SPOOLEDINVOICEL INES] (
> [SPOOLEDINVOICEI D] [T_SpooledInvoic esDomain] NOT NULL ,
> [LINE] [int] NOT NULL ,
> [STOCKID] [T_STOCKDOMAIN] NULL ,
> [DESCRIPTION] [varchar] (255) NULL ,
> [FREETEXTLINE] [tinyint] NULL ,
> [UNITOFSALE] [varchar] (20) NULL ,
> [QUANTITY] [float] NULL ,
> [COSTPRICE] [float] NULL ,
> [SELLINGPRICE] [float] NULL ,
> [PRICELEVELID] [T_PRICELEVELSDO MAIN] NULL ,
> [DISCOUNTPERCENT] [float] NULL ,
> [NOMINALACCOUNTI D] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [VATRATEID] [T_VATRATESDOMAI N] NULL ,
> [VATPERCENT] [float] NULL ,
> [VATAMOUNT] [money] NULL ,
> [LINEPRICE] [money] NULL ,
> [TimeStamp] [timestamp] NULL
> )
> GO
>
> /****** Object: Table [dbo].[SPOOLEDINVOICES] Script Date: 17/04/04
> 21:27:09 ******/
> CREATE TABLE [dbo].[SPOOLEDINVOICES] (
> [SPOOLEDINVOICEI D] [T_SpooledInvoic esDomain] IDENTITY (1, 1) NOT NULL ,
> [INVOICETYPE] [smallint] NOT NULL ,
> [PEOPLEID] [T_PEOPLEDOMAIN] NOT NULL ,
> [ACCOUNTID] [T_ACCOUNTSDOMAI N] NOT NULL ,
> [ORDERNUMBER] [varchar] (40) NULL ,
> [INVOICENUMBER] [int] NULL ,
> [PREFIX] [varchar] (10) NULL ,
> [SUFFIX] [varchar] (10) NULL ,
> [INVOICEDATE] [datetime] NULL ,
> [DATEDUE] [datetime] NULL ,
> [PLACEDBY] [varchar] (40) NULL ,
> [TELEPHONE] [varchar] (40) NULL ,
> [LANGUAGEID] [T_LANGUAGESDOMA IN] NULL ,
> [AGENTID] [T_AGENTSDOMAIN] NULL ,
> [CUSTOMERTYPEID] [T_CUSTOMERTYPES DOMAIN] NULL ,
> [SALESCENTREID] [T_SALESCENTRESD OMAIN] NULL ,
> [DEPARTMENTID] [T_DEPARTMENTSDO MAIN] NULL ,
> [WAREHOUSEID] [T_WAREHOUSESDOM AIN] NULL ,
> [TRADINGTYPE] [smallint] NULL ,
> [TRADINGDAYOFMON TH] [smallint] NULL ,
> [TRADINGDAYSTIME] [smallint] NULL ,
> [DISCOUNTPERCENT] [float] NULL ,
> [SETTLEMENTPERCE NT] [float] NULL ,
> [PayNow] [tinyint] NULL ,
> [AmountTendered] [money] NULL ,
> [Balance] [money] NULL ,
> [DiscountTaken] [money] NULL ,
> [MethodOfPayment] [varchar] (40) NULL ,
> [PayReference] [varchar] (40) NULL ,
> [Authorisation] [varchar] (40) NULL ,
> [PRICELEVELID] [T_PRICELEVELSDO MAIN] NULL ,
> [TOTALNETT] [money] NULL ,
> [TOTALVAT] [money] NULL ,
> [TOTALDISCOUNT] [money] NULL ,
> [TRADINGTERMS] [varchar] (40) NULL ,
> [INVOICEMESSAGES] [varchar] (255) NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [PLRate] [float] NULL ,
> [BSRate] [float] NULL ,
> [INVOICECONTACTI D] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [DELIVERYCONTACT ID] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [ORDERCONTACTID] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [UserID] [T_UsersDomain] NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[STOCK] Script Date: 17/04/04 21:27:13
> ******/
> CREATE TABLE [dbo].[STOCK] (
> [STOCKID] [T_STOCKDOMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [PICTURE] [varchar] (40) NULL ,
> [WEIGHT] [float] NULL ,
> [VOLUME] [float] NULL ,
> [BARCODE] [smallint] NULL ,
> [NumberOfPriceBr eaks] [smallint] NOT NULL ,
> [STOCKCATEGORYID] [T_STOCKCATEGORI ESDOMAIN] NULL ,
> [SALESNOMINALID] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [PURCHASENOMINAL ID] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [SELLINGCOMMENT] [varchar] (255) NULL ,
> [INCLUDESELLINGC OMMENT] [tinyint] NULL ,
> [DISPLAYSELLINGC OMMENT] [tinyint] NULL ,
> [COSTCOMMENT] [varchar] (255) NULL ,
> [DISPLAYCOSTCOMM ENT] [tinyint] NULL ,
> [PRODUCTTRACKING] [smallint] NULL ,
> [ITEMTYPE] [smallint] NULL ,
> [VALUATIONPRICE] [float] NOT NULL ,
> [INCLUDEINCUSTOM ERSTURNOVER] [tinyint] NULL ,
> [INCLUDEINAGENTS TURNOVER] [tinyint] NULL ,
> [SUPERCEDED] [tinyint] NULL ,
> [SUPERCEDEDBY] [T_STOCKDOMAIN] NULL ,
> [SUPPLIERID] [T_PEOPLEDOMAIN] NULL ,
> [SUPPLIERSTOCKID] [varchar] (40) NULL ,
> [SUPPLIERCOMMENT] [varchar] (255) NULL ,
> [NEXTSERIALNUMBE R] [int] NULL ,
> [SERIALNUMBERLEN GTH] [smallint] NULL ,
> [SERIALNUMBERPRE FIX] [varchar] (10) NULL ,
> [SERIALNUMBERSUF FIX] [varchar] (10) NULL ,
> [SERIALNUMBERPRE FIXLENGTH] [smallint] NULL ,
> [SERIALNUMBERSUF FIXLENGTH] [smallint] NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[STOCKCATEGORIES] Script Date: 17/04/04
> 21:27:16 ******/
> CREATE TABLE [dbo].[STOCKCATEGORIES] (
> [STOCKCATEGORYID] [T_STOCKCATEGORI ESDOMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> ALTER TABLE [dbo].[SALESCENTRES] WITH NOCHECK ADD
> CONSTRAINT [pk_salescentres] PRIMARY KEY CLUSTERED
> (
> [SALESCENTREID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[SPOOLEDINVOICEL INES] WITH NOCHECK ADD
> CONSTRAINT [pk_spooledinvoi celines] PRIMARY KEY CLUSTERED
> (
> [SPOOLEDINVOICEI D],
> [LINE]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[SPOOLEDINVOICES] WITH NOCHECK ADD
> CONSTRAINT [pk_spooledinvoi ces] PRIMARY KEY CLUSTERED
> (
> [SPOOLEDINVOICEI D]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[STOCK] WITH NOCHECK ADD
> CONSTRAINT [pk_stock] PRIMARY KEY CLUSTERED
> (
> [STOCKID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[STOCKCATEGORIES] WITH NOCHECK ADD
> CONSTRAINT [pk_stockcategor ies] PRIMARY KEY CLUSTERED
> (
> [STOCKCATEGORYID]
> ) ON [PRIMARY]
> GO
>
> I know there appears to be a lot of data here, but believe me I am only
> wanting to use a small porttion of it.
>
> 3) My current query is as follows:
>
> SELECT SALESCENTRES.NA ME, STOCKCATEGORIES .NAME,
> SPOOLEDINVOICEL INES.QUANTITY, SPOOLEDINVOICES .INVOICEDATE
> FROM SALESCENTRES, SPOOLEDINVOICEL INES, SPOOLEDINVOICES , STOCK,
> STOCKCATEGORIES
> WHERE SPOOLEDINVOICEL INES.SPOOLEDINV OICEID =
> SPOOLEDINVOICES .SPOOLEDINVOICE ID AND STOCK.STOCKID =
> SPOOLEDINVOICEL INES.STOCKID AND STOCKCATEGORIES .STOCKCATEGORYI D =
> STOCK.STOCKCATE GORYID AND SALESCENTRES.SA LESCENTREID =
> SPOOLEDINVOICES .SALESCENTREID AND ((SPOOLEDINVOIC ES.INVOICEDATE> {ts
> '2004-04-01 00:00:00'}) AND (SPOOLEDINVOICE S.INVOICEDATE<{ ts '2004-08-01
> 00:00:00'}))
>
> Where the 2 date values would actually be variable data that the user had
> entered.
>
> As I said before when I put the SALESCENTRES.NA ME field in the left hand
> portion of my Pivot table, the STOCKCATEGORIES .NAME in the top portion of[/color]
my[color=blue]
> Pivot table and the SPOOLEDINVOICEL INES.QUANTITY in the data/middle[/color]
section[color=blue]
> of the pivot table I get the kind of result I want (see results below),[/color]
but[color=blue]
> it only retrieves data where I have values for the SALESCENTRES.NA ME and[/color]
the[color=blue]
> STOCKCATEGORIES .NAME. What I want is to show all of the SALESCENTRES.NA ME
> fields (down the left) and all of the STOCKCATEGORIES .NAME fields (across
> the top) and if there isn't a SPOOLEDINVOICEL INES.QUANTITY value for a
> particular cross section then it simply shows a zero. My examples below
> should show it better.
>
> My current pivot table shows:
>
> StockCat1 StockCat4
> SalesC1 25 3
> SalesC2 4 6
> Grand Total 29 9
>
> But I want to show:
>
> StockCat1 StockCat2 StockCat3
> StockCat4 (etc...)
> SalesC1 25 0 0
> 3
> SalesC2 4 0 0
> 6
> SalesC3 0 0 0
> 0
> etc...
> Grand Total 29 0 0
> 9
>
> I'm sure it's down to joins, but I'm at a loss on how to do it.
>
> I hope the above helps and appreciate any time you can give to it.
>
> Rgds
>
> Laphan
>
>
>[/color]

**John Bell** · Jul 20 '05, 03:29 AM

Re: SQL Query Quandry

Hi

Your syntax is not ANSI format. What you require is an outer join. This is
described with examples in Books Online:

mk:@MSITStore:C :\Program%20Fil es\Microsoft%20 SQL%20Server\80 \Tools\Books\ac d
ata.chm::/ac_8_qd_09_0zqr .htm

It is also useful to include your example data as insert statements along
with all the DDL.

e.g

SELECT C.NAME, T.NAME,
SPOOLEDINVOICEL INES.QUANTITY, I.INVOICEDATE
FROM SALESCENTRES C LEFT JOIN SPOOLEDINVOICES I ON
C.SALESCENTREID = I.SALESCENTREID
JOIN SPOOLEDINVOICEL INES L ON L.SPOOLEDINVOIC EID = I.SPOOLEDINVOIC EID
JOIN STOCK S ON S.STOCKID = L.STOCKID
STOCKCATEGORIES T ON T.STOCKCATEGORY ID = S.STOCKCATEGORY ID
WHERE I.INVOICEDATE > '2004-04-01 00:00:00'
AND I.INVOICEDATE < '2004-08-01 00:00:00'

John

"Laphan" <news@DoNotEmai lMe.co.uk> wrote in message
news:40819679_3 @127.0.0.1...[color=blue]
> Hi All
>
> Sorry for the spare info. It wasn't because I didn't want to display it.[/color]
I[color=blue]
> just didn't know how to get the data that you needed. I think I've got it
> now so please note the following:
>
> 1) The DB is SQL 6.5.
>
> 2) My DDL for the tables in question:
>
> /****** Object: Table [dbo].[SALESCENTRES] Script Date: 17/04/04
> 21:27:00 ******/
> CREATE TABLE [dbo].[SALESCENTRES] (
> [SALESCENTREID] [T_SALESCENTRESD OMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[SPOOLEDINVOICEL INES] Script Date:[/color]
17/04/04[color=blue]
> 21:27:05 ******/
> CREATE TABLE [dbo].[SPOOLEDINVOICEL INES] (
> [SPOOLEDINVOICEI D] [T_SpooledInvoic esDomain] NOT NULL ,
> [LINE] [int] NOT NULL ,
> [STOCKID] [T_STOCKDOMAIN] NULL ,
> [DESCRIPTION] [varchar] (255) NULL ,
> [FREETEXTLINE] [tinyint] NULL ,
> [UNITOFSALE] [varchar] (20) NULL ,
> [QUANTITY] [float] NULL ,
> [COSTPRICE] [float] NULL ,
> [SELLINGPRICE] [float] NULL ,
> [PRICELEVELID] [T_PRICELEVELSDO MAIN] NULL ,
> [DISCOUNTPERCENT] [float] NULL ,
> [NOMINALACCOUNTI D] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [VATRATEID] [T_VATRATESDOMAI N] NULL ,
> [VATPERCENT] [float] NULL ,
> [VATAMOUNT] [money] NULL ,
> [LINEPRICE] [money] NULL ,
> [TimeStamp] [timestamp] NULL
> )
> GO
>
> /****** Object: Table [dbo].[SPOOLEDINVOICES] Script Date: 17/04/04
> 21:27:09 ******/
> CREATE TABLE [dbo].[SPOOLEDINVOICES] (
> [SPOOLEDINVOICEI D] [T_SpooledInvoic esDomain] IDENTITY (1, 1) NOT NULL ,
> [INVOICETYPE] [smallint] NOT NULL ,
> [PEOPLEID] [T_PEOPLEDOMAIN] NOT NULL ,
> [ACCOUNTID] [T_ACCOUNTSDOMAI N] NOT NULL ,
> [ORDERNUMBER] [varchar] (40) NULL ,
> [INVOICENUMBER] [int] NULL ,
> [PREFIX] [varchar] (10) NULL ,
> [SUFFIX] [varchar] (10) NULL ,
> [INVOICEDATE] [datetime] NULL ,
> [DATEDUE] [datetime] NULL ,
> [PLACEDBY] [varchar] (40) NULL ,
> [TELEPHONE] [varchar] (40) NULL ,
> [LANGUAGEID] [T_LANGUAGESDOMA IN] NULL ,
> [AGENTID] [T_AGENTSDOMAIN] NULL ,
> [CUSTOMERTYPEID] [T_CUSTOMERTYPES DOMAIN] NULL ,
> [SALESCENTREID] [T_SALESCENTRESD OMAIN] NULL ,
> [DEPARTMENTID] [T_DEPARTMENTSDO MAIN] NULL ,
> [WAREHOUSEID] [T_WAREHOUSESDOM AIN] NULL ,
> [TRADINGTYPE] [smallint] NULL ,
> [TRADINGDAYOFMON TH] [smallint] NULL ,
> [TRADINGDAYSTIME] [smallint] NULL ,
> [DISCOUNTPERCENT] [float] NULL ,
> [SETTLEMENTPERCE NT] [float] NULL ,
> [PayNow] [tinyint] NULL ,
> [AmountTendered] [money] NULL ,
> [Balance] [money] NULL ,
> [DiscountTaken] [money] NULL ,
> [MethodOfPayment] [varchar] (40) NULL ,
> [PayReference] [varchar] (40) NULL ,
> [Authorisation] [varchar] (40) NULL ,
> [PRICELEVELID] [T_PRICELEVELSDO MAIN] NULL ,
> [TOTALNETT] [money] NULL ,
> [TOTALVAT] [money] NULL ,
> [TOTALDISCOUNT] [money] NULL ,
> [TRADINGTERMS] [varchar] (40) NULL ,
> [INVOICEMESSAGES] [varchar] (255) NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [PLRate] [float] NULL ,
> [BSRate] [float] NULL ,
> [INVOICECONTACTI D] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [DELIVERYCONTACT ID] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [ORDERCONTACTID] [T_CONTACTDETAIL SDOMAIN] NULL ,
> [UserID] [T_UsersDomain] NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[STOCK] Script Date: 17/04/04 21:27:13
> ******/
> CREATE TABLE [dbo].[STOCK] (
> [STOCKID] [T_STOCKDOMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [PICTURE] [varchar] (40) NULL ,
> [WEIGHT] [float] NULL ,
> [VOLUME] [float] NULL ,
> [BARCODE] [smallint] NULL ,
> [NumberOfPriceBr eaks] [smallint] NOT NULL ,
> [STOCKCATEGORYID] [T_STOCKCATEGORI ESDOMAIN] NULL ,
> [SALESNOMINALID] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [PURCHASENOMINAL ID] [T_NOMINALACCOUN TSDOMAIN] NULL ,
> [SELLINGCOMMENT] [varchar] (255) NULL ,
> [INCLUDESELLINGC OMMENT] [tinyint] NULL ,
> [DISPLAYSELLINGC OMMENT] [tinyint] NULL ,
> [COSTCOMMENT] [varchar] (255) NULL ,
> [DISPLAYCOSTCOMM ENT] [tinyint] NULL ,
> [PRODUCTTRACKING] [smallint] NULL ,
> [ITEMTYPE] [smallint] NULL ,
> [VALUATIONPRICE] [float] NOT NULL ,
> [INCLUDEINCUSTOM ERSTURNOVER] [tinyint] NULL ,
> [INCLUDEINAGENTS TURNOVER] [tinyint] NULL ,
> [SUPERCEDED] [tinyint] NULL ,
> [SUPERCEDEDBY] [T_STOCKDOMAIN] NULL ,
> [SUPPLIERID] [T_PEOPLEDOMAIN] NULL ,
> [SUPPLIERSTOCKID] [varchar] (40) NULL ,
> [SUPPLIERCOMMENT] [varchar] (255) NULL ,
> [NEXTSERIALNUMBE R] [int] NULL ,
> [SERIALNUMBERLEN GTH] [smallint] NULL ,
> [SERIALNUMBERPRE FIX] [varchar] (10) NULL ,
> [SERIALNUMBERSUF FIX] [varchar] (10) NULL ,
> [SERIALNUMBERPRE FIXLENGTH] [smallint] NULL ,
> [SERIALNUMBERSUF FIXLENGTH] [smallint] NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> /****** Object: Table [dbo].[STOCKCATEGORIES] Script Date: 17/04/04
> 21:27:16 ******/
> CREATE TABLE [dbo].[STOCKCATEGORIES] (
> [STOCKCATEGORYID] [T_STOCKCATEGORI ESDOMAIN] NOT NULL ,
> [NAME] [varchar] (40) NOT NULL ,
> [COMMENT] [varchar] (255) NULL ,
> [TIMESTAMP] [timestamp] NOT NULL
> )
> GO
>
> ALTER TABLE [dbo].[SALESCENTRES] WITH NOCHECK ADD
> CONSTRAINT [pk_salescentres] PRIMARY KEY CLUSTERED
> (
> [SALESCENTREID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[SPOOLEDINVOICEL INES] WITH NOCHECK ADD
> CONSTRAINT [pk_spooledinvoi celines] PRIMARY KEY CLUSTERED
> (
> [SPOOLEDINVOICEI D],
> [LINE]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[SPOOLEDINVOICES] WITH NOCHECK ADD
> CONSTRAINT [pk_spooledinvoi ces] PRIMARY KEY CLUSTERED
> (
> [SPOOLEDINVOICEI D]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[STOCK] WITH NOCHECK ADD
> CONSTRAINT [pk_stock] PRIMARY KEY CLUSTERED
> (
> [STOCKID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[STOCKCATEGORIES] WITH NOCHECK ADD
> CONSTRAINT [pk_stockcategor ies] PRIMARY KEY CLUSTERED
> (
> [STOCKCATEGORYID]
> ) ON [PRIMARY]
> GO
>
> I know there appears to be a lot of data here, but believe me I am only
> wanting to use a small porttion of it.
>
> 3) My current query is as follows:
>
> SELECT SALESCENTRES.NA ME, STOCKCATEGORIES .NAME,
> SPOOLEDINVOICEL INES.QUANTITY, SPOOLEDINVOICES .INVOICEDATE
> FROM SALESCENTRES, SPOOLEDINVOICEL INES, SPOOLEDINVOICES , STOCK,
> STOCKCATEGORIES
> WHERE SPOOLEDINVOICEL INES.SPOOLEDINV OICEID =
> SPOOLEDINVOICES .SPOOLEDINVOICE ID AND STOCK.STOCKID =
> SPOOLEDINVOICEL INES.STOCKID AND STOCKCATEGORIES .STOCKCATEGORYI D =
> STOCK.STOCKCATE GORYID AND SALESCENTRES.SA LESCENTREID =
> SPOOLEDINVOICES .SALESCENTREID AND ((SPOOLEDINVOIC ES.INVOICEDATE> {ts
> '2004-04-01 00:00:00'}) AND (SPOOLEDINVOICE S.INVOICEDATE<{ ts '2004-08-01
> 00:00:00'}))
>
> Where the 2 date values would actually be variable data that the user had
> entered.
>
> As I said before when I put the SALESCENTRES.NA ME field in the left hand
> portion of my Pivot table, the STOCKCATEGORIES .NAME in the top portion of[/color]
my[color=blue]
> Pivot table and the SPOOLEDINVOICEL INES.QUANTITY in the data/middle[/color]
section[color=blue]
> of the pivot table I get the kind of result I want (see results below),[/color]
but[color=blue]
> it only retrieves data where I have values for the SALESCENTRES.NA ME and[/color]
the[color=blue]
> STOCKCATEGORIES .NAME. What I want is to show all of the SALESCENTRES.NA ME
> fields (down the left) and all of the STOCKCATEGORIES .NAME fields (across
> the top) and if there isn't a SPOOLEDINVOICEL INES.QUANTITY value for a
> particular cross section then it simply shows a zero. My examples below
> should show it better.
>
> My current pivot table shows:
>
> StockCat1 StockCat4
> SalesC1 25 3
> SalesC2 4 6
> Grand Total 29 9
>
> But I want to show:
>
> StockCat1 StockCat2 StockCat3
> StockCat4 (etc...)
> SalesC1 25 0 0
> 3
> SalesC2 4 0 0
> 6
> SalesC3 0 0 0
> 0
> etc...
> Grand Total 29 0 0
> 9
>
> I'm sure it's down to joins, but I'm at a loss on how to do it.
>
> I hope the above helps and appreciate any time you can give to it.
>
> Rgds
>
> Laphan
>
>
>[/color]

SQL Query Quandry

SQL Query Quandry

Comment

Comment