Results Grouped in Accounting Month, not Calendar

[BHill]

I am trying to be able to group columnar results by accounting month or by calendar month in reports/queries.

User gets popup form and clicks on start and end month, as well as 'Accounting' or 'Calendar'. The trade date on each record is compared to the underlying calendar table of the pop-up form to determine if it falls within the predefined start/end dates. eg Feb 2002 accounting month is 1/29 - 2/25. If a trade date is 1/30, and 'Calendar' reporting is requested, the tradedate on the record is compared to the calendar table, and the record should group in Jan. If 'Accounting was selected, the data should group in Feb.

What happens is Feb data falls into the Feb grouping and Jan data into Jan grouping. The correct records are reported but 3 days of Feb Accounting is grouped in Jan column and 25 days of Feb Accounting is in Feb column.

The query format is Expr1: Format([TradeDate],"mmm").

M'soft help-line told me it couldn't be done. I know this isn't impossible, half of corporate america reports on an accounting calendar. Been struggling for months on this. Working alone. None of the m'soft knowledge base articles (that I know of) address this issue. HELP! Please.

 

[KKilfoil]

Assumptions:
1) [TradeMonths] table with date fields for [FirstDay] and [LastDay] and another field for [TradeMonth], displaying values like '2002 Jan' and '1998 Apr' (or whatever).

2) You want to match a [TradeDate] value in your [Trades] table with the correct [TradeMonth] value.

A Solution:

In a query, add the appropriate fields from the Trade table, including [Tradedate]. Add a calculated field like:

TheMonth: DLookUp("[TradeMonth]","TradeMonths"," (#" & Str([TradeDate]) & "# >= [FirstDay] ) and ( [LastDay] >= #" & Str([TradeDate]) & "# )")

Organize any queries or reports by [TheMonth] from this query.

 

[BHill]

Thank you so much. After 40-50 hours working on this, alone, I got to the point where syntax (if this the right word)was half my problem. I followed yours and I was able to complete.
If you would be so kind... when I got it working, my queries go from <1 second to 14 seconds, on a 1.6GHz pc. Users are running much slower PC's. Would it be smarter going forward to generate an extra calculated field (acctg month) at time of entering data, based on a similar lookup. I'm thinking the lookup time to generate the calculated Acctg month would be negligable for 1 record, but the negative is extra stored data (one extra field / record). Then instead of doing a lookup and comparing each record between my begin and end report dates to the date table to determine the correct month, I would have an "if statement" such as: if "Calendar" use month from {TradeDate]else, use the precalculated "accounting" month on the record.
How would a professional tackle this? I really appreciate your response. I can think out logically what I want to do, but find 90% of my problems are figuring out what command to use, and what syntax. eg I spent at least 20 hours before finding out Access doesn't have a "roundup" function like excel. I waste much time looking for things that don't exist.
Thanks again in advance for your advise on the last issue. Your help meant a lot.

 

[KKilfoil]

I'm not sure if I would describe myself as an Access professional, but...

Ordinarily, storing values that can be calculated is a no-no. However, reducing excessive processing times is something for which I have used 'redundant' stored data. (I had a function that took 10 minutes to run for all of the records in a table, so I stored them instead.)


If you want to get fanatical about not storing data, you COULD create another table to hold 366 records, one for every day of the year, with a field for [Trademonth] (or a FK to your trademonth table), and then connect the new table to your trades table with a relationship in your query.

Dlookup's are notoriously slow, and this would eliminate it (with the trade-off being managing another table). It might speed up things quite a bit.


If it were me, I'd store the trademonth value in every record. Disk storage space is pretty cheap. (Apologies to the db purists)

[This message has been edited by KKilfoil (edited 03-10-2002).]

 

[BHill]

Thanks for the response. I've been out of town for a week, thinking about this on and off. I think for now, I'll let the query calc the trade month. If trades are always entered in a form, I'd calc the month upon data entry and store it. But we will be imported from excel directly into the trades table, and I don't want to mess with that right now. It works, albeit slowly, and I need a break.

Last question. Is it possible to compile (right word ?) the DLookup and IIF statements that ultimately surrround it and save it as a "function" ? I don't know if this is possible, or if it is, if it would run faster.
Thanks K
Very much appreciated.

 

[Chris RR]

I don't know if this will help, but I have certainly worked often with accounting months. I've found that it is sometimes a lot simpler to put two hidden fields (fiscal month begin, fiscal month end) on the form. When the user has made their date selection & hit whatever button, I fill in these fields, doing only 2 dlookups. Then I use these fields as parameters in the query.

 

[tony33r]

this thread is a little old so maybe the people that posted it is not around anymore but I'll give a try.
I tried using it for an application that I have and I get the following error message:

and I use the following code:
DLookUp("[MoYr]","tblDCloseCal"," (#" & Str([AsmRDat]) & "# &>;=[MoStart]) and ([MoEnd] & >;= #" & Str([AsmRDat]) & "# )")
where MoYr is the date field containing the Mo and Year, AsmRDat is the date of my underlying table and MoStart and MoEnd the date fields for the starting and end date for the calendar.
Any help is appreciated