Query for Two Tables of Months

[bballhermit]

I am new to Access, so bear with me, please. I have the following database setup:

tblMonths
MonthID (unique ID for a specific month instance)
Year (year of specific month instance)
MonthNumber (1-12 representing actual month of specific month instance)
Hours (working hours in specific month instance)

tblMonthNames - Static
MonthNumber (one of each 1-12)
MonthAbbr (abbreviation of each of the 12 months)
MonthName (full name of each of the 12 months)

I am trying to make a simple "Add New Month Form". In this form I have a text field for the year and the working hours, but for the month, I want to have a drop down based on the 12 possible months. How do I set up the control source and row source (i'm assuming using a query) so that the drop down selects and saves the MonthNumber but displays one instance of each of the 12 month names? I want the MonthNumber of whichever of the 12 month names the user selects in the dropdown to be stored in a new unique MonthID entry in tblMonths.

Thanks.

 

[John Big Booty]

Why not simply store the date in full and then break it down, using the various date manipulation functions, to extract the various date parts that you require?

 

[MarkK]

Time is not an object. It is a common error to think that a month merits a row in a table, but this is almost never the case. Time is always a dimension of something else, like weight or height or depth or amount.
You would not, for instance, have an amount table if you were writing an accounting system. You would not link a transaction to a value in a separate table. The value is unalterably a dimension of the transaction; a field in a table, not a row. Time is like that.
A person has a birthday. An order has an order date. An employee has a hire date and a termination date. A shift has a start time. See how time is a dimension of something else?
And if you need to keep track of a specific value like WorkingHours, it may also have a date attribute, but note that in this case the object is the working hour total and the month is just a dimension of that value.
And check out the VBA.Strings.MonthName() function. VBA does it already.
Cheers,

 

[bballhermit]

@John Ok, thanks for those functions, I wasn't aware of those.

@lagbolt Thanks, I think I'm tracking with you. That MonthName function looks like what I need, and, as far as time being a dimension, I agree with you, I think. So, basically I would just need my tblMonths storing MonthID, date_value, and WorkingHours? Ultimately, I'm developing a project management system in which I need to store the number of working hours in any given month.

 

[MarkK]

I'm developing a project management system in which I need to store the number of working hours in any given month.

Then I think you need a table called tWorkingHours.

tWorkingHours
WorkingHoursID (PK)
Month
Year
WorkingHours

I think the object is the 'working hours' and the date is a dimension of that object.
And this object isn't really something you're going to link to other objects. This is like a lookup for a rate that is time sensitive. I have tables like this for tax rates and labourbenefits rates that are effective on a certain date, and maybe that makes sense to describe here, a rates table...

tRate
RateID (PK)
RateType (maybe WorkingHours, maybe sales tax)
EffectiveDate (if monthly then day always = 1)
Rate

So the effective rate is the most recent rate of the given type with a date <= the date in question...

SELECT TOP 1 Rate 
FROM tRate
WHERE RateType = 'WorkingHours' 
  AND EffectiveDate <= #DateInQuestion#
ORDER BY EffectiveDate;

So if you open a recordset with this SQL then the current Rate is in the first record, or if there are no records use a default. See how your WorkingHours concept is a kind of time sensitive rate?

 

[bballhermit]

Ok, thanks. So, in my add new month working hours form I wan the user to be able to select the desired month from a list of the twelve in a dropdown. How can I display all 12 possible months without having them stored in a table? Thanks.

Also, with those date functions, can I just use that in the Row Source property field? i.e., Row Source: MonthName(Month)

 

[MarkK]

Change the rowsourcetype of the combo to value list, and create a rowsource which is just a delimited string, like this...

dim i as integer
dim tmp as string
for i = 1 to 12
  tmp = tmp & ";" & i & ";" & MonthName(i)
next
[COLOR="Green"]'here you need to drop the leading ";"[/COLOR]
me.cboMonth.rowsource = mid(tmp, 2)

You can do this when the form opens.
Also, out of curiousity, what is this concept working hours per month? Is this a sum of the working days in a specific month and the hours in each of those days? What do you use this for?
Is it possible that you can automate the calculation of this value so the user doesn't have to do it?
Cheers,

 

[bballhermit]

Yeah, it's just going to be the amount of standard working hours per month (based on 40 hrs/week minus holidays). Yeah, if I could straight-up automate it, that'd be best! I'm just too new to Access to know how to do that. Could you point me in the right direction to do that?

 

[MarkK]

Well, it's a pretty common topic around here to count the working days in a period of time. If you search around I bet you could find code. One maintenance issue is that you need a table with the holidays that might occur in your time period, so instead of manually entering months and the workinghours value, you need to manually enter holidays. Maybe this just shifts your workload around?
In either case though, you certainly don't have to do either of these tasks very often. Like counting the working days this month and mulitplying by 8 isn't that taxing.
Cheers,