troubleshooting Question

crosstab query help, runtime error 3270-Property not found

Avatar of j2jake
j2jake asked on
Microsoft Access
14 Comments1 Solution1055 ViewsLast Modified:
Need big help guys!

I am trying to have an output from my query wherein the row is going to be just the merchant_customer_id. With the columns, I need it to be the sum of daa_total_gms but it is just according to the last 4 months from the month picked by the user (check the WHERE syntax). I also need it to be arranged based on the top 50 daa_total_gms from the months picked from the interface.

I have tried to run the query below but gave me a runtime error 3270.

This is how I was looking for the output.

From the interface: the user chose Year:2009 and Month:January

Rank | Merchant Customer ID | 2008 Sep | 2008 Oct | 2008 Nov | 2008 Dec | 2009 Jan

TRANSFORM Sum(tblMSales.[daa_total_gms]) AS SumOfdaa_total_gms
SELECT tblMSales.[merchant_customer_id], Sum(tblMSales.[daa_total_gms]) AS [Total Of daa_total_gms]
FROM tblMSales
WHERE (((tblMSales.dates) Between DateAdd("m",0,CDate([Forms]![frmMerchants]![cboMonth] & "/1/" & [Forms]![frmMerchants]![cboYear])) And DateAdd("m",-4,CDate([Forms]![frmMerchants]![cboMonth] & "/1/" & [Forms]![frmMerchants]![cboYear]))))
GROUP BY tblMSales.[merchant_customer_id]
PIVOT Format([dates],"yyyy mmm");

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros