ExcelBanter - Excel/SQL Query Quandry

Hi Dick/OneDay

Sorry for the spare info. It wasn't because I didn't want to display it. I
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_SALESCENTRESDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

/****** Object: Table [dbo].[SPOOLEDINVOICELINES] Script Date: 17/04/04
21:27:05 ******/
CREATE TABLE [dbo].[SPOOLEDINVOICELINES] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] 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_PRICELEVELSDOMAIN] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[NOMINALACCOUNTID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[VATRATEID] [T_VATRATESDOMAIN] 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] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] IDENTITY (1, 1) NOT NULL ,
[INVOICETYPE] [smallint] NOT NULL ,
[PEOPLEID] [T_PEOPLEDOMAIN] NOT NULL ,
[ACCOUNTID] [T_ACCOUNTSDOMAIN] 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_LANGUAGESDOMAIN] NULL ,
[AGENTID] [T_AGENTSDOMAIN] NULL ,
[CUSTOMERTYPEID] [T_CUSTOMERTYPESDOMAIN] NULL ,
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NULL ,
[DEPARTMENTID] [T_DEPARTMENTSDOMAIN] NULL ,
[WAREHOUSEID] [T_WAREHOUSESDOMAIN] NULL ,
[TRADINGTYPE] [smallint] NULL ,
[TRADINGDAYOFMONTH] [smallint] NULL ,
[TRADINGDAYSTIME] [smallint] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[SETTLEMENTPERCENT] [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_PRICELEVELSDOMAIN] 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 ,
[INVOICECONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[DELIVERYCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[ORDERCONTACTID] [T_CONTACTDETAILSDOMAIN] 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 ,
[NumberOfPriceBreaks] [smallint] NOT NULL ,
[STOCKCATEGORYID] [T_STOCKCATEGORIESDOMAIN] NULL ,
[SALESNOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[PURCHASENOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[SELLINGCOMMENT] [varchar] (255) NULL ,
[INCLUDESELLINGCOMMENT] [tinyint] NULL ,
[DISPLAYSELLINGCOMMENT] [tinyint] NULL ,
[COSTCOMMENT] [varchar] (255) NULL ,
[DISPLAYCOSTCOMMENT] [tinyint] NULL ,
[PRODUCTTRACKING] [smallint] NULL ,
[ITEMTYPE] [smallint] NULL ,
[VALUATIONPRICE] [float] NOT NULL ,
[INCLUDEINCUSTOMERSTURNOVER] [tinyint] NULL ,
[INCLUDEINAGENTSTURNOVER] [tinyint] NULL ,
[SUPERCEDED] [tinyint] NULL ,
[SUPERCEDEDBY] [T_STOCKDOMAIN] NULL ,
[SUPPLIERID] [T_PEOPLEDOMAIN] NULL ,
[SUPPLIERSTOCKID] [varchar] (40) NULL ,
[SUPPLIERCOMMENT] [varchar] (255) NULL ,
[NEXTSERIALNUMBER] [int] NULL ,
[SERIALNUMBERLENGTH] [smallint] NULL ,
[SERIALNUMBERPREFIX] [varchar] (10) NULL ,
[SERIALNUMBERSUFFIX] [varchar] (10) NULL ,
[SERIALNUMBERPREFIXLENGTH] [smallint] NULL ,
[SERIALNUMBERSUFFIXLENGTH] [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_STOCKCATEGORIESDOMAIN] 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].[SPOOLEDINVOICELINES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoicelines] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID],
[LINE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SPOOLEDINVOICES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoices] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID]
) 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_stockcategories] 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.NAME, STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY, SPOOLEDINVOICES.INVOICEDATE
FROM SALESCENTRES, SPOOLEDINVOICELINES, SPOOLEDINVOICES, STOCK,
STOCKCATEGORIES
WHERE SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID AND STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID AND STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID AND SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID AND ((SPOOLEDINVOICES.INVOICEDATE{ts
'2004-04-01 00:00:00'}) AND (SPOOLEDINVOICES.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.NAME field in the left hand
portion of my Pivot table, the STOCKCATEGORIES.NAME in the top portion of my
Pivot table and the SPOOLEDINVOICELINES.QUANTITY in the data/middle section
of the pivot table I get the kind of result I want (see results below), but
it only retrieves data where I have values for the SALESCENTRES.NAME and the
STOCKCATEGORIES.NAME. What I want is to show all of the SALESCENTRES.NAME
fields (down the left) and all of the STOCKCATEGORIES.NAME fields (across
the top) and if there isn't a SPOOLEDINVOICELINES.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

Dick Kusleika wrote in message
...
Laphan

If you don't, you'll probably only get a theoretical answer along the
lines of, 'You need an OUTER JOIN specifically, and some GROUP BY
clauses generally'.

tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need all
of
tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I need
tbl_STOCK contains STOCKID, STOCKCATID
tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the

Along the theoretical lines, you just need to series a bunch of outer joins
using only two tables at a time, like

Query1: tbl_SALESPERSONS outer join tbl_INVOICEHEADER
Query2: tbl_INVOICELINES outer join tbl_STOCK
Query3: Query1 outer join Query2
Query4: tbl_STOCKCAT outer join Query3

Then use Query4 as the source of your pivot table. All this needs to be
done in SQL Server or Access or whatever you're using, not in MSQuery.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com