Access query by non standard month. (1 Viewer)

JahJr

Andy
Local time
Today, 17:44
Joined
Dec 3, 2008
Messages
93
I have a db with a couple of tables.
Production
ID, Date, Bucket, Revenue
Production Calendar
ID, Start Date, End Date, Business Month, Calendar Year

Our monthly business does not go on calendar months. It ends on the next to last Friday of most months. I created the Production Calendar table to try and query the production table. I'm not having any luck.

Assume there are 10 unique Buckets. I would like to create a report that shows me the total of the 10 unique buckets individually by each month for a calendar year.

I have no problem doing this if i just enter a start date and an end date, it goes beyond my knowledge when i want to do this by month and bucket with multiple months on the report and that the month i need to use is not a regular calendar month.
 

stopher

AWF VIP
Local time
Today, 22:44
Joined
Feb 1, 2006
Messages
2,396
Don't use a start and end date in your calendar table. Just use a single date field and enter all dates for your calendar. This becomes an easy to implement join. Also, it means you can time slices for any date e.g. quarter.

Make sure you don't name any date fields "Date" as this is a reserved word.
 

JahJr

Andy
Local time
Today, 17:44
Joined
Dec 3, 2008
Messages
93
Just to confirm, remove the Start Date field and use > and <= on the end date?

Still don't know how to get the rest of this done.
 

plog

Banishment Pending
Local time
Today, 17:44
Joined
May 11, 2011
Messages
11,612
I disagree (or more accurately can't see how stopher's path leads to a solution) and think you should have Start and End date fields in your production calendar.

I do agree that you need to name your fields better. 'Date' is a poor choice--you should prefix it with what that date represents (ProductionDate). Also, only use alphanumeric characters--that means no spaces. Life will be easier down the line if you do both of those.

As you what you want to accomplish, you sure did a good job of using contradictory terms:

...report that shows me the total of the 10 unique buckets individually by each month...

Total, but individually? You lost me. Perhaps you can demonstrate what you hope to achieve with data. Provide 2 sets:

A. Sample data from your tables. Be sure to include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with based on the data you provide in A.
 

JahJr

Andy
Local time
Today, 17:44
Joined
Dec 3, 2008
Messages
93
Thank you for your help, the date column is named TradeDate.

Assume January 2016 is 12/16/2015-1/15/2016 and
February 2016 is 1/16/2016-2/13/2016
Trade Date Bucket Revenue
12/16/15 1 $50
01/10/16 1 $25
01/14/16 2 $30
01/15/16 2 $10
01/16/16 1 $150
01/20/16 1 $125
02/12/16 2 $130
02/13/16 2 $110

Results

January 2016 1 $75
January 2016 2 $40
February 2016 1 $275
February 2016 2 $240

I hope this is a better explanation.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:44
Joined
Jan 20, 2009
Messages
12,849
A calendar table is not required. Date periods based on "Next to last Friday of the Month" can be derived from the date using a custom function. This strategy avoids the chore of having to add records to a calendar table and eliminates the potential for errors and omissions while making these entries.

Do not use separate separate fields for the ProductionMonth and ProductionYear, but store a date that represents the month and format it to display as required.

Querying could be done by calculating the ProductionMonth from the production date for each record. However this overhead would slow down all queries that required the ProductionMonth. If this were done, it would be important to use Between dates criteria directly on the indexed ProductionDate field to prefilter the records. Definitely do not put the only criteria on the ProductionMonth calculated in the query because this would require every record in the table to be processed before the join could be implemented.

The ProductionMonth could be stored but that would technically be a normalization issue. I don't think Access can use a custom function in a table calculated field. (I don't use calculated fields.)

Personally I would use an SQL Server backend and create a Table Valued Function that returned records for ProductionMonth along with the maximum and minimum dates that would be used in query joins. The input parameters of the function would be the date range required to be covered by the returned table.

A good design would do as much as possible on the server but, if required, the table function can be connected to Access using a PassThrough query and joined into the main query using a Between condition on the join. Use VBA to modify the SQL of the query to set the parameters before running the query that uses it.
 
Last edited:

stopher

AWF VIP
Local time
Today, 22:44
Joined
Feb 1, 2006
Messages
2,396
A calendar table is not required. Date periods based on "Next to last Friday of the Month" can be derived from the date using a custom function. This strategy avoids the chore of having to add records to a calendar table and eliminates the potential for errors and omissions while making these entries.
I would strongly advised against this approach. Hard coding business rules like this is not good design. For one thing, the OP says the rule applies "for most months". Also, given the OPs example I can't see a rule as such. The first period of 2016 seems to start on a Wednesday. But more compelling is the notion at such a rule could change (like product code numbering could change). I've had several calendar changes imposed on me during my career (business takeovers/merges etc). The tabular approach is the way to go - either the way I suggested or using a range. SAP do it for good reason.
 

stopher

AWF VIP
Local time
Today, 22:44
Joined
Feb 1, 2006
Messages
2,396
Example of calendar approach attached. The calendar takes only a few minutes to create in Excel (and then copy/paste) and is a one-off exercise.
 

Attachments

  • customPeriods.accdb
    420 KB · Views: 52

CJ_London

Super Moderator
Staff member
Local time
Today, 22:44
Joined
Feb 19, 2013
Messages
16,553
For one thing, the OP says the rule applies "for most months".
just wondering if the rule is a 13 month year or a 4,4,5 quarter No strong views as to which is the better way to go if there is a business rule that can be applied.

On business change I too have experienced a number over the years - but with the requirement to report both ways of a period of time for comparison purposes (e.g. calendar month and 13 month year as well as changes to year end). This requires a change to the db whichever way you go
 

Simon_MT

Registered User.
Local time
Today, 22:44
Joined
Feb 26, 2007
Messages
2,177
Picking up on what is considered normalised. I use Start Dates and End Dates and store the Year, Period or Week in the transactional tables. Everything is measured aganst Weekly, Monthly or Yearly performance so I don't think it is abnormal to store these values. Afterall all we are doing is store byte size fields for Week and Period and an Integer for Year.

Besides there is a commodity price every week that needs to be stored somewhere, so we are having to create a Weekly table anyway why not with Periods and Year as well. We are orienteering data to match the business requirements or put it another way being practical.

Simon
 

plog

Banishment Pending
Local time
Today, 17:44
Joined
May 11, 2011
Messages
11,612
First--Jah here is your solution:

Code:
SELECT ProductionCalendar.BusinessMonth, ProductionCalendar.CalendarYear, Production.Bucket, Sum(Production.Revenue) AS totalRevenue
FROM Production INNER JOIN ProductionCalendar ON (Production.TradeDate <= ProductionCalendar.EndDate) AND (Production.TradeDate >= ProductionCalendar.StartDate)
GROUP BY ProductionCalendar.BusinessMonth, ProductionCalendar.CalendarYear, Production.Bucket;

I used the field names of your Production Calendar table from the first post (without spaces in the table or field names). The real key is the JOIN, it makes sure that your two tables only match when the TradeDate falls between the StartDate and EndDate of a record in Production Calendar.

Second--to join the discussion. I would normally agree with Galaxiom, that a function should be used. But from this part of Jah's post, I don't think his calendar is based on logic:

...It ends on the next to last Friday of most months...

'Most' is a horrible word in the world of databases (right up there with 'should', 'basically' and 'usually'). If he had used the word 'every' then I think a custom function would be the tool to use. Since he didn't, I vote a table to store the specifics of when periods start and end.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:44
Joined
Feb 28, 2001
Messages
27,001
The solution (in my feeble mind) is to build a table that lists your key dates. While it might seem wasteful to some, the programming of it would be trivial.

Table KeyDates:
StartDate: date, PK
EndDate: date
MonthNumber: long

Then if you have a form where you enter a transaction, you can put code in the LostFocus of the transaction date something like below to fill in a value for a MonthNum field on the form to show a unique month number by having that table predefined to show you the monthnumber you want to use for each date range. It will be a very short table for a given year.

Code:
Private Sub XActDate_LostFocus
Dim CompDate as String

    CompDate = "#" & Format( Me![XActDate], vbShortDate) & "#"
    [MonthNum] = DLookup( "[MonthNumber]", "KeyDates", _
        "[StartDate]<= " & CompDate  & " AND " & _
        "[EndDate] >= " & CompDate  ) 

End Sub

In the event code of the form, "Me" will work as a substitute for the longer formal reference of Forms("myformname") that would otherwise be required to bind this to a given record number. This also assumes that the text box is actually stored in Date format, not in raw text. If this is a common type of translation, though, you could make the DLookup a one-line function where you would pass in a date and get back a date code.

Note also that date codes might actually need multiple parts depending on your business rules. If the date code - the "offset month number" - has to start over again for a given year, the table can reflect that but you would then have to be aware that you could have duplicated month numbers that aren't the same month (because they were in different years). If this is an issue, you could still use the manual table method and just add another field so show what codes apply to each critical date range.
 
Last edited:

Simon_MT

Registered User.
Local time
Today, 22:44
Joined
Feb 26, 2007
Messages
2,177
Hi Doc_Man,

The same applies to Stored Procedures. If you think about it everytime you want want the Month you need to loop through a Function or Procedure. I know this is not normalised but it you store Month you only have to loop through once and never again.

Simon
 

plog

Banishment Pending
Local time
Today, 17:44
Joined
May 11, 2011
Messages
11,612
it you store Month you only have to loop through once and never again.

The weakest part of any database system is people. They mistype data, they insert invalid records, the make untrue assumptions, they forget step 2 in a 5 step process. The more you can eliminate humans from the system the better that system is going to be.

That's why you should use drop downs instead of text inputs, that's why you create forms instead of letting them directly interact with tables and people are why you should use logic and functions to determine values instead of relying on people to keep tables up to date.

Note the next 2 words---In general you should not store periods that are calculable. A table that relies on people to maintain it that does the same thing will become incorrect.
 

Simon_MT

Registered User.
Local time
Today, 22:44
Joined
Feb 26, 2007
Messages
2,177
Yes I agree, but there will be somwhere in the system where Financial Periods are stored. If they are wrong that is not our problem. But we can ensure that when a date is entered the correct Financial Period is assigned. The chances of making an error on 365 records as opposed to 12 are far greater. Besides it is easy to validate data than to try and create a system that caters for every type of user error.

Simon
 

JahJr

Andy
Local time
Today, 17:44
Joined
Dec 3, 2008
Messages
93
Thank you all for your help, Plog had the best solution for me.
I changed some of my Field names to help me.
I also setup a user form where I can select dates, the user form runs a Query called Monthly Production which runs another query called Production Query

Code:
SELECT DISTINCTROW Production.ID, Production.[B/S], Production.Bucket, Production.[Bucket Desc], 
 Production.[Gross Prod], Production.[Trade Dt], ProductionCalendar.BusinessMonth, 
 ProductionCalendar.CalendarYear, ProductionCalendar.EndDate
FROM Production INNER JOIN ProductionCalendar ON (Production.[Trade Dt] <= ProductionCalendar.EndDate) 
 AND (Production.[Trade Dt] >= ProductionCalendar.StartDate)
GROUP BY Production.ID, Production.[B/S], Production.Bucket, Production.[Bucket Desc], 
 Production.[Gross Prod], Production.[Trade Dt], ProductionCalendar.BusinessMonth, 
 ProductionCalendar.CalendarYear, ProductionCalendar.EndDate
HAVING (((Production.[B/S])<>"Z") And ((Production![Trade Dt]) Between Forms!
 [Production Query Date Selection]!txtBeginDate And Forms![Production Query Date Selection]!txtEndDate))
ORDER BY ProductionCalendar.EndDate, Production.Bucket;
Code:
SELECT [Production Query].Bucket, [Production Query].[Bucket Desc], Sum([Production Query].[Gross Prod]) 
 AS SumOfGrossProd, [Production Query].BusinessMonth, [Production Query].CalendarYear, 
 [Production Query].EndDate
FROM [Production Query]
GROUP BY [Production Query].Bucket, [Production Query].[Bucket Desc], [Production Query].BusinessMonth, 
 [Production Query].CalendarYear, [Production Query].EndDate
ORDER BY [Production Query].EndDate, [Production Query].CalendarYear, [Production Query].Bucket;
 
Last edited:

Users who are viewing this thread

Top Bottom