Search results

Please try again using the attached file. This one is Excel 2010 but the previous one was downgraded to 97-2003 version and probably lost some features. Thanks.

Hello JHB, as discussed, attached is a sample Excel pivot table that will pretty much resemble the final report. The requirement for this report is to compare the two latest fiscal years (in this case Fiscal 2013-2014 vs. Fiscal 2014-2015). The sales guys want to see the quantity and dollars...

I am not familiar with VBA but tomorrow I'll prepare sample data in Excel sheet and the steps on how to reproduce the error. Thank you!

Yes, all reporting needs to be done in an Excel pivot table. I use the custom 'calculated fields' option to get the differences between the sales columns. When attempted to sort the report by 'this year sales' column, Excel returns a warning message (see attached) that it couldn't do it. None...

Great, thanks a lot JHB! My report is a bit more complex; I initially submitted a simplified version of it, to make it easier to understand. 1. My source table will also include the ‘quantity sold’ column. In the report I need to show the quantity columns for both fiscal years along with the...

Hi JHB, this is great for start, thank you very much for your time and effort. I was wondering if I can ask you to help me 'fine tune' it? If so, let me know and I'll follow up. Thanks again!

No, I haven't resolved it yet.

As I was working on an Excel pivot sales report based on MS Access records, I came across an Excel conflict between its ‘Top10’ ranking (sorting) functionality and the ‘calculated fields’ functionality that I also need to use in this report in order to find the differences between the sales...

Thank you, CJ. FYI: Changing the date data type didn't help, sorry. The data set represents 2 years of sales detail invoice records, needed for a year to year comparison, since January 2012. Once grouped into the product categories , the final report is 40 lines, one page only. I need every...

Hello CJ, You are right, the 500K records is waaay too many to display on a report and of course I never do that. I attach the product table, group them by product categories and summarize the records by sales amounts, weight, cost, etc... The way the tables are linked now by Invoice Number, I...

Nope. No luck my friend. The same performance issue as with the previous one. I tried removing the 'invnum' indexes but no difference. Sorry.

Alright, I've got a mixed results this time: - Using the new code from above, the query would display in about 1 minute - Scrolling through the query is sooo slooow...I can scroll one page per minute, no way to scroll to the bottom or to see the total number of rows, it runs constantly in...

O.K., here is the requirement: The final result is a simple sales report that should show up any field from either the ‘salejou4’ (invoice header table) or ‘saledet4’ (invoice details) table. The ‘exhist4’ is just a helper table that we need to use in order to get the daily CAN/US or CAN/EURO...

No luck yet. :( Now access shows 'Running Query' on the bottom but no progress bar. FYI, here is the latest statement, as per your suggestions: SELECT DateValue([Salejou4]![date2]) AS NewDate, EXHIST4.newrate FROM Salejou4 INNER JOIN EXHIST4 ON Salejou4.curtype = EXHIST4.curtype WHERE...

It depends how many records you are talking about and how are you defining '60% runtime'? Not too many records: Salejou4 table: 51,000 Saledet4 table: 510,000 Exhist4 table: 1,000 Normally, it takes less than a minute to run the same query without the new subquery. When I said the 60%, I was...

The Bank of Canada is our source, exactly. The issue is with our bean counters who occasionally skip a day or two posting the rates in our internal system. This report needs to match our internal reports the way they are.

Appreciated a quick response, CJ_London ! Tested it on the sample database and it worked fine. :) However, the query gets stuck at approximately 60% runtime in the real database. I replaced the "date" wording with the "date2" in the source linked tables, as suggested. Here is the new code...

Hi there, I’ve got stuck in preparation of this sales query. The primary sales table contains a mix of Canadian and the US detail invoice sales records. All sales records are in their native currencies The secondary lookup table contains daily US/CAN foreign exchange rates (FX). I need to...

Solved. Apr Pillai, truly appreciate your time and effort in helping me with this challenge! Thank you.

I am working on a typical sales query and I need to generate three new fields based on the Date field. For each Date record, I need to calculate its pertaining: - fiscal accounting month - fiscal accounting quarter and - fiscal accounting year the date belongs to. Fiscal Months: Unlike...