Picking a number by Season (1 Viewer)

[ConfusedA]

I've tried a few searches for something along these lines, but I cannot think of what to search for, if you read this and know of a previous conversation along these lines please pass along a link, or if you can help it'd be appreciated.

The data:
TableJobtype: I have a list of jobs that are my base set
Tablejobsdone: Table that records jobs on a weekly basis (year, month, job type, hours spent)
Tablejobprice: Has prices associated with each job
TablejobCode: has a list of jobs and their associated codes.

Problem: Some jobs occurr in summer only, some in winter only and some in both. For a set of jobs there aren't individual codes but codes for general summer or general winter.

What I need: A way to generate the appropriate job code for these jobs based on whether it is summer or winter.

Any help to point me in the right direction of how to do this would be appreciated.

My guess on what I'm looking for:
Something like GeneralWinter = Job1(Dec) + Job1(Jan) + Job1(Feb) + ...+ Job2(Dec) +... + Jobn(Mar)
But putting this into code and figuring out where is harder for me to understand.

Thanks in advance.
ConfusedA

 

[twoplustwo]

Could you set up a relationship with another table (titled tblSeasons or something) from the main table you are referring to? Then each job could have a flag/number against it and then queried easily rather than trying to do it all programatically.

 

[ConfusedA]

Thanks for the response!

Hmm, I have to think about how to do that. Because I'd have to create a togglable "Seasons" list box so that it could only come up on the input report for the jobs that can occur in both seasons...or maybe give 2 of the same values for a summer/winter option for the jobs that only occur during one season?

 

[twoplustwo]

I would go with:

1 - Summer
2 - Winter
3 - Both

Then in your main table a flag of 1,2 or 3 would be set. If you wanted to see the text you could create a query and pull in the main table and tblSeasons adding the appropriate fields.

I hope I have understood your post correctly!

 

[ConfusedA]

That makes sense...just for the 'both' option I need to be able to determine via the month of input whether it's associated with 'jobcode-winter' or 'jobcode-summer'

 

[twoplustwo]

Could you describe how the data is getting into the tables?

 

[ConfusedA]

Once the system is finished my hope is that there will be a form that has the following fields to be put in:
Month(listbox), Week, Year, Truck#(listbox), Jobtype(listbox), location(listbox), Hours spent. *All not tagged as a listbox are text boxes.

If I must I can add a summer/Winter listbox, but ideally(because I'm going to need to use this data for other parts of this project) I'd like to be able to sort summer (April-Nov) and Winter (Dec-Mar) based off of the month value given.

This information will go into a table of JobsDone, which will be used to calculate a monthly total of each job. But Each job has an associated jobcode that needs to appear on the monthly report, and some jobs have the same jobcode (but are separated by season). What I want to do is be able to report all jobcodes and their associated total cost and hours.

IE if theres 3 general summer jobs i'd lke them to all share the same jobcode and thus only have one value for the sum of their hours/total cost, along side the jobs with their own jobcodes. (So instead of having say...7 jobs and their prices, there'd be 4 separate jobs and then 1 that represents the 3 general summer jobs)/

 

[ConfusedA]

Is there a way to order a set of data to remain that way?
IE. I have Months (Jan-Dec) with weeks (1-5) in them.

If I could set it up so that by year it'd be from: Jan (week1), Jan (week2), etc. to Dec (week5). So that If I was looking for from May (Week2) to July (week1) it would show:
May(week2), May(week3),...,June(Week5), July(Week1).
If I can make that, then the search would just have to encompass Nov (Week1) to Mar(Week5) and Apr(Week1) to OCt(Week5).... being able to do this would make other parts of this database better detailed. And if this worked i could maybe add something to note all these weeks as 1 (summer) or 2 (Winter)

 

[ConfusedA]

Ok here's my solution as far as I can get, I've created a table called tblmonthinfo, for each month i've given a numeric value... (1 to 12) and with relationship i've connected the spelt months together between tables.

From here I believe I can query to associate the spelled month to its numeric value, but I am unsure of how to use an if statement to place the jobcode to the item based on it's month's numeric value...
something like If [tables]![tblmonthinfo]![fldmonthvalue] = 12, or [tables]![tblmonthinfo]![fldmonthvalue] = 1, or [tables]!<etc> = 2, or <etc> = 3
Then
[tables]![tbljobresult]![jobcode] = "Winter-3500"

Any thoughts on if this will actually work? and how the proper code would look?

 

[ConfusedA]

Ok...so just an attempt to get the job code working I made a query and tried to use the following as a criteria, it obviously did not work, but it's a step towards what I'm trying to do:

IIf(IsNull([tables]![tbljobcode]![jobcde]) THEN
If [fldMonthnumber] = 12 Or [fldMonthNumber] < 4 THEN
[Queries]![Query1]![jobcode] = "Winter 23"
Else
[Queries]![Query1]![jobcode] = "Summer 23"
End If
Else [Queries]![Query1]![jobcode] = [Tables]![TblGlnumber]![ GL Number]
End Iif

 

[ConfusedA]

* = [Tables]![tblJobCode]![jobcode]
...They're for GL numbers but it's simpler for explaination to call them jobcode because I will be doing similar things with other types of info.