Forcing query to make a record

[Stapes]

Hi

I know when you run a cross tab query, you can force it to make the
columns you want by going in to Query Properties and setting the
Column Headings attribute.
However, I am trying to produce a dataset containing each Customers
purchases per year. I read the invoice file to do this, but I want it
to produce a record for every customer & every year. At the moment, if
no invoice exists for a particular year, no record appears. I need it
to show a zero spend for the year if that is the case.

Any suggestions?

Stapes

 

[Stapes]

Hi

I know when you run a cross tab query, you can force it to make the
columns you want by going in to Query Properties and setting the
Column Headings attribute.
However, I am trying to produce a dataset containing each Customers
purchases per year. I read the invoice file to do this, but I want it
to produce a record for every customer & every year. At the moment, if
no invoice exists for a particular year, no record appears. I need it
to show a zero spend for the year if that is the case.

Any suggestions?

Stapes

P.S. This is my query: -

SELECT TM_CompContact.PK_Contact, TM_Invoice.AcNo,
IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
AS Yr, Sum(TM_Invoice.TotalSpend) AS SumOfTotalSpend,
Sum(BookSum.SumOfNUM_Qty) AS Qty
FROM (TM_Invoice INNER JOIN BookSum ON TM_Invoice.SageInvoiceNo =
BookSum.SageInvoiceNo) INNER JOIN TM_CompContact ON TM_Invoice.AcNo =
TM_CompContact.AcNo
GROUP BY TM_CompContact.PK_Contact, TM_Invoice.AcNo,
IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
ORDER BY IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
DESC;

 

[Stapes]

Hi

I know when you run a cross tab query, you can force it to make the
columns you want by going in to Query Properties and setting the
Column Headings attribute.
However, I am trying to produce a dataset containing each Customers
purchases per year. I read the invoice file to do this, but I want it
to produce a record for every customer & every year. At the moment, if
no invoice exists for a particular year, no record appears. I need it
to show a zero spend for the year if that is the case.

Any suggestions?

Stapes

P.S. This is my query: -

SELECT TM_CompContact.PK_Contact, TM_Invoice.AcNo,
IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
AS Yr, Sum(TM_Invoice.TotalSpend) AS SumOfTotalSpend,
Sum(BookSum.SumOfNUM_Qty) AS Qty
FROM (TM_Invoice INNER JOIN BookSum ON TM_Invoice.SageInvoiceNo =
BookSum.SageInvoiceNo) INNER JOIN TM_CompContact ON TM_Invoice.AcNo =
TM_CompContact.AcNo
GROUP BY TM_CompContact.PK_Contact, TM_Invoice.AcNo,
IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
ORDER BY IIf(Month([InvoiceDate])<=10,Year([InvoiceDate])-1 & "/" &
Year([InvoiceDate]),Year([InvoiceDate]) & "/" & Year([InvoiceDate])+1)
DESC;

Forget it - I've cracked it!