Crosstab query based on another query problems (1 Viewer)

[DBnub11]

Hi all

Ran into a brick wall with the following cross tab query.

TRANSFORM Sum([Top 5 Unpaid by Month].[AMT]) AS SumOfAMT
SELECT [Top 5 Unpaid by Month].[VENDOR_NAME], [Top 5 Unpaid by Month].[INVOICE_NUM], [Top 5 Unpaid by Month].[INVOICE_DATE]
FROM [Top 5 Unpaid by Month], MonthConv
GROUP BY [Top 5 Unpaid by Month].[VENDOR_NAME], [Top 5 Unpaid by Month].[INVOICE_NUM], [Top 5 Unpaid by Month].[INVOICE_DATE]
PIVOT [Top 5 Unpaid by Month].ACCT_MONTH;


The query the cross tab is being based off of is the following

SELECT Ledger.VENDOR_NAME, Ledger.INVOICE_NUM, Ledger.INVOICE_DATE, Ledger.AMT, Ledger.ACCT_MONTH, Ledger.ID
FROM Ledger
WHERE (((Ledger.AMT) In (SELECT TOP 5 Sum(T1.AMT) AS AMT
FROM Ledger AS T1
WHERE ((([Ledger].[ACCT_MONTH])=[T1].[ACCT_MONTH]) AND ((T1.ACCT_Year)=2011) AND ((T1.STATUS)="UNPAID"))
GROUP BY [Ledger].[ACCT_MONTH], T1.VENDOR_NAME, T1.INVOICE_NUM, T1.INVOICE_DATE, T1.ACCT_Year, T1.STATUS
ORDER BY Sum(T1.AMT) DESC)));

When i run it I get the message the microsoft office access database engine does not recognize [Ledger].[ACCT_MONTH] as a valid field name or experession.

I am stumped since it am selecting data from a query and not the based table anymore so why is this coming up. I been surfing the web and common theme is the need for parameters for Crosstab queries being explicitly defined. Everyone does a [Form]![blah].[blah] but this is nota form and I do not want promting. I just want to take the existing query and cross tab it. When I tried doing it in one fell swoop Access balks at the fact taht i have a criteria on the value term in the cross tab.

Any way around this would be much appreacited. Thanks in advance.

 

[vbaInet]

GROUP BY [Ledger].[ACCT_MONTH], T1.VENDOR_NAME, T1.INVOICE_NUM, T1.INVOICE_DATE, T1.ACCT_Year, T1.STATUS

If this GROUP BY falls within the subquery, then the highlighted should be T1.[Acct_Month]

 

[DBnub11]

i updated the query with your suggetions but still no dice ? any other ideas?

 

[vbaInet]

In that case this is where your problem is:

WHERE ((([Ledger].[ACCT_MONTH])=[T1].[ACCT_MONTH]) AND ((T1.ACCT_Year)=2011) AND ((T1.STATUS)="UNPAID"))

That doesn't make sense to the subquery if you're using the IN operator. You would normally write it that way if you're using EXISTS. However, fields from the main query used within a subquery is not visible to the crosstab query. You need to get rid of that condition.

 

[DBnub11]

Sorry if I am dense, I followed an example from Rogers access world and the query itself works to get the TOP data. I am not sure why this would affect the crosstab query which is completely separate.

 

[vbaInet]

Sorry if I am dense, I followed an example from Rogers access world and the query itself works to get the TOP data. I am not sure why this would affect the crosstab query which is completely separate.

It just does. Maybe something to ask Microsoft about

 

[DBnub11]

lol good old MS

Anyways took your advice and edited the query and took that clause out. Now the query itself is long and ugly with unions in the form of

SELECT * FROM (
SELECT TOP 5 Sum(T1.AMT) AS AMT, T1.VENDOR_NAME, MonthConv.[Display Month], [Subcode Descriptions].Class, T1.INVOICE_NUM, T1.INVOICE_DATE
FROM [Subcode Descriptions] INNER JOIN (MonthConv INNER JOIN Ledger AS T1 ON MonthConv.[MONTH ID] = T1.ACCT_MONTH) ON [Subcode Descriptions].SubCode = T1.ACCT_SUBCODE
WHERE T1.ACCT_MONTH = 1 AND T1.ACCT_Year=2011 AND T1.STATUS="UNPAID"
GROUP BY T1.ACCT_MONTH, T1.VENDOR_NAME, T1.INVOICE_NUM, T1.INVOICE_DATE, T1.ACCT_Year, T1.STATUS, T1.VENDOR_NAME, MonthConv.[Display Month], [Subcode Descriptions].Class, T1.INVOICE_NUM, T1.INVOICE_DATE
ORDER BY MonthConv.[Display Month], Sum(T1.AMT) DESC ,T1.INVOICE_NUM DESC) as A1

the cross tab works now go figure!

One last point any easy way of putting a rank on each record since I am only taking the top 5 ?

 

[vbaInet]

It depends on whether every record has a unique ID? Also, what is the ultimate goal of doing this, to use it in a report? It's much easier to do in a report.

 

[DBnub11]

eventually this is going in a report but I wish to display a value for each month by rank and not have it take over the whole page. If I have a rank column I can use that as my row heading, month for my column heading and the desscription field as the value. Then I would make a report form the cross tab

 

[vbaInet]

Ok, using this crosstab query as the record source of your report won't work because the fields are dynamic. In any case you're better off performing the ranking in the report and use the report's SORTING & GROUPING feauture to group by Month.

 

[DBnub11]

ok one last question

any reason why i am getting a syntax error on this
TRANSFORM Max("$" & [Top 5 Unpaid by Month]![AMT] & " " & [Top 5 Unpaid by Month]![VENDOR_NAME] & " " & [Top 5 Unpaid by Month]![INVOICE_NUM]) AS ITEM
SELECT (SELECT Count([Top 5 Unpaid by Month]![Display Month]) As HowMany
From [Top 5 Unpaid by Month] as Dupe
WHERE (Dupe.AMT > [Top 5 Unpaid by Month].AMT) and Dupe.[Display Month] = [Top 5 Unpaid by Month].[Display Month]
) AS RANK
FROM [Top 5 Unpaid by Month]
GROUP BY (SELECT Count([Top 5 Unpaid by Month]![Display Month]) As HowMany
From [Top 5 Unpaid by Month] as Dupe
WHERE (Dupe.AMT > [Top 5 Unpaid by Month].AMT) and Dupe.[Display Month] = [Top 5 Unpaid by Month].[Display Month]
)
PIVOT [Top 5 Unpaid by Month].[Display Month];

 

[vbaInet]

I cannot tell you with the format it's in. Indent the code properly and wrap it inside code tags to preserve the indents. Here's how to put code tags:

http://www.access-programmers.co.uk/forums/showthread.php?t=200247