Crosstab Query Excluding Data

G

Guest

I have a transaction table, a transaction detail table and a category table.

I have a crosstab query that gathers information from all three and
summarizes it for the user and it works great. The only problem is when there
are records that have the same date, amount and category, the query sees them
as 1 record instead of listing out both transactions. Here is the sql view of
the query:

TRANSFORM ([tblTransaction].[TotalAmount]) AS TotalAmount
SELECT tblTransaction.TransDate, Format([TransDate],"mm" & "/" & "dd") AS
MonthDay, tblTransaction.Description, tblTransaction.TotalAmount
FROM tblTransaction INNER JOIN (tblTransDetail LEFT JOIN qryCategoryList ON
tblTransDetail.fkCategoryID = qryCategoryList.CategoryID) ON
tblTransaction.TransID = tblTransDetail.fkTransID
WHERE (((tblTransaction.fkBankID)=15) AND ((tblTransaction.TransDate)
Between #11/1/2003# And #10/31/2004#))
GROUP BY tblTransaction.TransDate, Format([TransDate],"mm" & "/" & "dd"),
tblTransaction.Description, tblTransaction.TotalAmount,
tblTransaction.fkBankID
ORDER BY tblTransaction.TransDate
PIVOT qryCategoryList.Category In ("Amount from WA","Refunds From
Hotels","Receipt From Sales","Transfers From","Miscellaneous Inc","Hotel
Expenses","Motorcoach Expense","Tour Expenses","Concert Expenses","Office
Expenses","Communication Expenses","Bank Expenses","Bank to Cash
Account","Foreign Currency","Miscellaneous Exp");

Any help would be appreciated.
 
D

Duane Hookom

How would you expect the query to display more than one of similar records?
Have you considered adding the primary key from your detail table to the Row
Headings?
 
G

Guest

The primary key idea was perfect. Thank you! Never thought of that.

Greg

Duane Hookom said:
How would you expect the query to display more than one of similar records?
Have you considered adding the primary key from your detail table to the Row
Headings?

--
Duane Hookom
MS Access MVP
--

Greg said:
I have a transaction table, a transaction detail table and a category
table.

I have a crosstab query that gathers information from all three and
summarizes it for the user and it works great. The only problem is when
there
are records that have the same date, amount and category, the query sees
them
as 1 record instead of listing out both transactions. Here is the sql view
of
the query:

TRANSFORM ([tblTransaction].[TotalAmount]) AS TotalAmount
SELECT tblTransaction.TransDate, Format([TransDate],"mm" & "/" & "dd") AS
MonthDay, tblTransaction.Description, tblTransaction.TotalAmount
FROM tblTransaction INNER JOIN (tblTransDetail LEFT JOIN qryCategoryList
ON
tblTransDetail.fkCategoryID = qryCategoryList.CategoryID) ON
tblTransaction.TransID = tblTransDetail.fkTransID
WHERE (((tblTransaction.fkBankID)=15) AND ((tblTransaction.TransDate)
Between #11/1/2003# And #10/31/2004#))
GROUP BY tblTransaction.TransDate, Format([TransDate],"mm" & "/" & "dd"),
tblTransaction.Description, tblTransaction.TotalAmount,
tblTransaction.fkBankID
ORDER BY tblTransaction.TransDate
PIVOT qryCategoryList.Category In ("Amount from WA","Refunds From
Hotels","Receipt From Sales","Transfers From","Miscellaneous Inc","Hotel
Expenses","Motorcoach Expense","Tour Expenses","Concert Expenses","Office
Expenses","Communication Expenses","Bank Expenses","Bank to Cash
Account","Foreign Currency","Miscellaneous Exp");

Any help would be appreciated.
Click to expand...
Click to expand...
 
Top