How could I get Summary of Crosstab Query?

[abpatind]

I am trying to extract data from a table where, I need final out put as summary of a field. I am using Crosstab query and pivoted by Duedate field. The date range is entered by user everytime. This crosstab query is giving me detail amount - not summary. So, I created another query where I used this Crosstab query as source. but If I entered different date range next time, the summary query won't change.

Is there any way I could get summarized of this crosstab query??
I highly appriciate any help.

I have following Crosstab query.

PARAMETERS [Forms]![tt_frmGarberDue]![txtsdate] DateTime, [Forms]![tt_frmGarberDue]![txtedate] DateTime;
TRANSFORM First(GPR_FmtTranTbl.InvoiceAmount) AS FirstOfInvoiceAmount
SELECT GPR_FmtTranTbl.CustomerNo, GPR_FmtTranTbl.InvoiceDate, GPR_FmtTranTbl.InvoiceNo, GPR_FmtTranTbl.InvoiceAmount, GPR_ActMastTbl.StoreNo, GPR_ActMastTbl.StoreName, GPR_ActMastTbl.CreditPeriod
FROM GPR_FmtTranTbl INNER JOIN GPR_ActMastTbl ON GPR_FmtTranTbl.CustomerNo = GPR_ActMastTbl.CustomerNo
WHERE (((GPR_FmtTranTbl.InvoiceDueDate)>=[Forms]![tt_frmGarberDue]![txtsdate] And (GPR_FmtTranTbl.InvoiceDueDate)<=[Forms]![tt_frmGarberDue]![txtedate]))
GROUP BY GPR_FmtTranTbl.CustomerNo, GPR_FmtTranTbl.InvoiceDate, GPR_FmtTranTbl.InvoiceNo, GPR_FmtTranTbl.InvoiceAmount, GPR_ActMastTbl.StoreNo, GPR_ActMastTbl.StoreName, GPR_ActMastTbl.CreditPeriod
ORDER BY GPR_ActMastTbl.CreditPeriod DESC , GPR_ActMastTbl.StoreNo
PIVOT GPR_FmtTranTbl.InvoiceDueDate;

 

[dhookom]

If you want summarized values, get rid of one or more of your row headings. If you get rid of all but the customerNo, you will have an invoice summary of each unique customer.

I would also recommend that you decide how many days you want to return and don't vary from this. For instance, if you want the 7 days ending on the txtedate:

PARAMETERS [Forms]![tt_frmGarberDue]![txtedate] DateTime;
TRANSFORM First(GPR_FmtTranTbl.InvoiceAmount) AS FirstOfInvoiceAmount
SELECT GPR_FmtTranTbl.CustomerNo, GPR_FmtTranTbl.InvoiceDate, 
   GPR_FmtTranTbl.InvoiceNo, GPR_FmtTranTbl.InvoiceAmount, 
   GPR_ActMastTbl.StoreNo, GPR_ActMastTbl.StoreName, 
   GPR_ActMastTbl.CreditPeriod
FROM GPR_FmtTranTbl INNER JOIN GPR_ActMastTbl
   ON GPR_FmtTranTbl.CustomerNo = GPR_ActMastTbl.CustomerNo
GROUP BY GPR_FmtTranTbl.CustomerNo, GPR_FmtTranTbl.InvoiceDate, 
   GPR_FmtTranTbl.InvoiceNo, GPR_FmtTranTbl.InvoiceAmount, GPR_ActMastTbl.StoreNo, 
   GPR_ActMastTbl.StoreName, GPR_ActMastTbl.CreditPeriod
ORDER BY GPR_ActMastTbl.CreditPeriod DESC , GPR_ActMastTbl.StoreNo
PIVOT "D" & DateDiff("d",GPR_FmtTranTbl.InvoiceDueDate,[Forms]![tt_frmGarberDue]![txtedate])
   IN ("D6","D5","D4","D3","D2","D1","D0") ;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]