Field argument too complex HELP needed to simplify

[seenfresh]

Greetings,

I am receiving a "Query too complex" error when I am attempting to run one of my queries and it is a result of a complex argument passed to a field in my query via vba. Below are two examples of my data. "Example 1" is a simple example of the original data that lies in one of my data tables. "Example 2" is how I would like to report this data via a query.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #FF0000;;">EXAMPLE_1:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Advertiser</td><td style="font-weight: bold;;">Start Date</td><td style="font-weight: bold;;">End Date</td><td style="font-weight: bold;;">Booked Revenue</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Ford</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">3/15/2011</td><td style="text-align: right;;"> $ 10,000 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Toyota</td><td style="text-align: right;;">2/5/2011</td><td style="text-align: right;;">10/15/2011</td><td style="text-align: right;;"> $ 30,000 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Audi</td><td style="text-align: right;;">2/1/2011</td><td style="text-align: right;;">4/30/2011</td><td style="text-align: right;;"> $ 25,000 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FF0000;;">EXAMPLE_2:</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Advertiser</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Start Date</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">End Date</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Booked Revenue</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">1/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">2/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">3/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">4/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">5/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">Ford</td><td style="text-align: right;border-top: 1px solid black;;">1/1/2011</td><td style="text-align: right;border-top: 1px solid black;;">3/15/2011</td><td style="text-align: right;border-top: 1px solid black;;"> $ 10,000 </td><td style="text-align: right;border-top: 1px solid black;;"> 4,189.19 </td><td style="text-align: right;border-top: 1px solid black;;"> 3,783.78 </td><td style="text-align: right;border-top: 1px solid black;;"> 2,027.03 </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"> 10,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-left: 1px solid black;;">Toyota</td><td style="text-align: right;;">2/5/2011</td><td style="text-align: right;;">5/30/2011</td><td style="text-align: right;;"> $ 30,000 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 6,260.87 </td><td style="text-align: right;;"> 8,086.96 </td><td style="text-align: right;;"> 7,826.09 </td><td style="text-align: right;;"> 7,826.09 </td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"> 30,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">Audi</td><td style="text-align: right;border-bottom: 1px solid black;;">2/1/2011</td><td style="text-align: right;border-bottom: 1px solid black;;">4/30/2011</td><td style="text-align: right;border-bottom: 1px solid black;;"> $ 25,000 </td><td style="text-align: right;border-bottom: 1px solid black;;"> - </td><td style="text-align: right;border-bottom: 1px solid black;;"> 7,865.17 </td><td style="text-align: right;border-bottom: 1px solid black;;"> 8,707.87 </td><td style="text-align: right;border-bottom: 1px solid black;;"> 8,426.97 </td><td style="text-align: right;border-bottom: 1px solid black;;"> - </td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"> 25,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">Total</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 4,189.19 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 17,909.82 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 18,821.85 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 16,253.05 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 7,826.09 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 65,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;;">*** example of how we need to convert the data via a query into the above format… booked revenue broken out by month.  This is done by pro-rating based on days in month and length of campaign</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

In "Example 2" ws cell E11 the following excel formula exists:

=IF(E$10="","",$D11*(IF(AND(DATE(YEAR($B11),MONTH($B11),DAY(1))=DATE(YEAR($C11),MONTH($C11),DAY(1)),MONTH(E$10)=MONTH($C11),YEAR(E$10)=YEAR($C11)),1,IF(OR($B11>DATE(YEAR(E$10),MONTH(E$10)+1,0),$C11<E$10),0,IF(AND(YEAR(E$10)=YEAR($C11),MONTH(E$10)=MONTH($C11)),DAY($C11),(DAY(EOMONTH(E$10,0))-DAY(MAX(E$10,$B11)))+1)/($C11-$B11+1)))))

This formula is used to determine if the order start date and end date applies to the given month (header) and if so pro-rate the total cost of the order to the month based on the number of days.

I have build an Access Query that dynamically builds out a table similar to "Example 2" above based on "Example 1" data table, however I need help simplifying my formula noted above... it seems to be too complex, it works in Excel but too complex for Access to handle. Below is the same code for one of my data fields but configured for VB and SQL which is passed to each field in my query:

1/1/2008: [Total_Net_Revenue]*IIf((Month([Start_Date])=Month([End_Date])) And (Year([Start_Date])=Year([End_Date])),1,IIf(([Start_Date]>DateSerial(Year(#1/1/2008#),Month(#1/1/2008#),0)) Or ([End_Date]<#1/1/2008#),0,IIf((Year(#1/1/2008#)=Year([End_Date])) And (Month(#1/1/2008#)=Month([End_Date])),Day([End_Date]),(Day(DateSerial(Year(#1/1/2008#),Month(#1/1/2008#),0))-Day(IIf(#1/1/2008#>[Start_Date],#1/1/2008#,[Start_Date]))+1)/([End_Date]-[Start_Date]+1))))

Does anyone have any suggestions regarding how to simplify this formula/code or other methods of obtaining my desired results evident in "Example 2"?

Thanks in advance for your help... I've hit a road block LOL

 

[xenou]

My first choice in case like this would be to push the raw data to Excel and analyze it there - pretty much, what you have here. It's working in Excel ... can you not continue this way? Although if I did want to go with Access all the way I'd probably script a series of queries to transform the data or push it to another table broken out into periodic allocations (maybe even using vba to handle some of the logic).

 

[SydneyGeek]

I've had to do a bit of reporting like this and tend to use code to write out data to a reporting table, then run the queries on that.

Or, as xenou says, look at using Excel. That looks like a classic pivot table layout.

Denis

 

[seenfresh]

Thanks for your suggestions

I had similar thoughts.

The problem with doing this in Excel covers basically all the reasons why Access is better at managing large amounts of data acting as a database. Excel is great for flexibility and data manipulation but Access is easier for large data sets and reporting...etc In addition, Excel get's slower as my data sets increase.

I think I'm going to attempt to break out the queries/calculations into steps and see if that works, if it doesn't I'll revert to code which will take me longer since I'm not the greatest at writing code.

Was wondering if my actual formula (evident in Example 2 cell E11) to pro-rate the $ could be simplified? This would also help prevent too much data processing.

SydneyGeek any chance I could see a simple example of something you have done along these lines or even the formula/code to pro-rate the $ into their respective months? Would really help me out.

Thanks again for your suggestions and help!

Cheers

 

[xenou]

How much data are we talking about here?

For a simpler formula I would:

1) Calculate the daily rate
2) in each cell simply multiply the number of days * the daily rate.

You'd probably have to add one column that provides that daily amount. Be sure to prove it out with a checksum. You may end up with rounding errors - it's useful if the last column can include the correction for "pennies". Or maybe it won't matter in your situation. I don't really know if this would perform better but it's worth a try.

Some think it's beneath them but I would use helper columns and rows. A helper column to compute daily rate. A helper column for a check figure. Across the top below the dates have a hidden row to calculate the days in the period that apply to that advertiser. Maybe a second helper row with a 1 if the period is the last period (for capturing the pennies). Now you just multiply daily rates time the helper row (1's and 0's). And if the second helper row is "true", capture your lost pennies.

 

[seenfresh]

Thanks Xenou I'll give it a go and see what happens lol

Thanks again!

 

[xenou]

When you say large datasets what is your definition of large? I mocked up a sample worksheet similar to yours. 50,000 rows of data (advertiser, start-date, end-date, amount) took only a few seconds. Doesn't seem *too* bad ... but all the periodic values where in a single year in this case.

 

[seenfresh]

Your mock-up was in Excel? 50,000 rows/records is plenty I have less. Although, the start and end dates span roughly 4 years (2010 to 2014) = lots of columns/fields.

Using Excel works pretty good, but would prefer to use Access because I will be incorporating other data sets (data tables) and reporting relationships to the data... for example reports by advertiser, sales rep, location...etc Also will be creating user forms to allow for an easier user experience and flexible custom reports by selected criteria...


I could also send you a copy of my database which maybe easier to see what I constructed so far?

I have a feeling that because I used vba to dynamically build out the fields in my query in relationship to the earliest and latest dates in my data table this may also be adding to the process complexity... maybe I can just manually build a table, but would be nice to have something dynamic.

Hope this makes sense.

Thanks again for you help. You Rock!

 

[SydneyGeek]

You can still run most of the queries in Access. Just avoid the last one, which seems to give the problems.
At that point, switch to Excel. Build a pivot table in a blank sheet. At the first step of the wizard select External Data, browse to your Access db and select the last working query in the chain. Pull all the fields across and create the pivot table.

Pros:
Dataset size doesn't matter much, unless it gets big (like over 500K rows). I have done this on a query with 800K rows and around 25 fields; refreshing the pivot cache takes maybe 20-30 seconds initially, then is quick for subsequent changes.
You get to do all the manipulation in Access, taking avantage of its better capacity

Cons:
If you leave the workbook open you can't make changes to the Access db, and vice versa. They will lock each other out. So, just make sure that the db is closed when you use the pivot tables.

If you're still interested in the code-driven approach I could put something together for you but try the pivot first. It's simpler to set up.

Denis

 

[xenou]

My mock up is here:
<a href="http://northernocean.net/etc/mrexcel/20110617.zip">Sample Workbooks</a>
sha265sum (zip file): 480f17e9d90545432264e20b99ec3afd420e4de4ab20fa0b6b39bcdc4ffb0e06

It's 3.47 MB because I included a sample file with 50,000 rows of test data. My periods at the top were all in the same year but the formula is extendible to more years without any changes needed - just add the new rows. I think, anyway.