Query within Query within Query Results not table related

[Delid4ve]

Hi All

Ive got all my tables sorted now for a work database project but am struggling a little with the query side.

Im calculating holiday entitlement for employees based on numerous factors and would like some help combining queries

Today is my first day ever writing SQL so please bear with me. I initially started writing the combined query but couldnt get anywhere, so i broke it down and have got the result that i require from multiple queries strung together, however du to the amount im going to require id rather get these merged into one if possible. The single queries are:

*Selects the latest employee contract and the amount of months duration
SELECT DATEDIFF("m",StartDate,NOW()) AS ContractLength
FROM CONTRACT
WHERE ContractID = (SELECT max(ContractID)
FROM CONTRACT);

then we have
*Selects Additional Days Service holiday entitlement based on the contract length
SELECT IIF (ContractLength BETWEEN 0 AND 11.99,'0',IIF(ContractLength BETWEEN 12 AND 23.99,'1',IIF(ContractLength BETWEEN 24 AND 35.99,'2',IIF(ContractLength BETWEEN 36 AND 47.99,'3',IIF(ContractLength BETWEEN 48 AND 59.99,'4','5'))))) AS AdditionalDaysHolidays
FROM LatestContractQuery;

then we have
*Total Holiday Entitlement

SELECT (AdditionalDaysHoliday + 20) AS HolidayEntitlement
FROM AdditionalDaysServiceQuery;

Now ive been playing for hours but am struggling and just cannot get it to work. Ive tried UNION, Nested SELECT, but just keep getting to :banghead:

As the result of each query does not output to a table the main stumbling block i come accross is FROM errors

Please help

 

[Minty]

I would create the holiday entitlement as a function and then use it in a simple select query. It will work quicker and if the requirements/rules change you are only having to modify your function once not remember where the queries where and how they were set up.

http://www.fontstuff.com/vba/vbatut04.htm

If you need a hand with creating the function let us know.

 

[plog]

Agree with Minty, however you are still going to need to work on your queries. Those queries you posted will produce 1 value. I assume you have multiple contracts, don't you want a query to give you the HolidayEntitlement for every Contract? Probably for every employee right?

Can you post ample data? Include 2 sets:

A. Data from all relevant tables and fields. Be sure to include table and field names and enough sample data to cover all cases.

B. Expected results. Show what you expect to end up with based on the starting data in A.

 

[Delid4ve]

Hi guys, thanks for the comments.
maybe I'm being dumb, maybe I'm missing the whole point here

Just adding a bit of data to the db then I'll upload.

To explain:
The point of the db is to calculate employee holiday entitlement for a given year. This can be very complex due to:
pro rata part time staff not getting a bank holiday if they don't work that day
Different reserved days over the Christmas period when we shutdown
Different holiday entitlements based on years of service

As Im completely new to access(very computer literate but lack code skills and as I spoke to you before plog I have some access knowledge but cannot recall much at all from college) I'm trying to familiarise myself with different aspects of the program and build as I go. Basically I have 5 years until I inherit the family business, my time at present is taken up by manually having to do a lot of what a database could manage, calculations, forms, duplication of information across different documents.

My aim is to develop this over the course of the next 5 years so that I can then concentrate on growing an already successful business. Although we have a crm that manages our jobs/invoicing/purchases/even staff to a degree, it, and the company that supply it dont accommodate the other side to the business and although they do integrate some parts it is buggy and does not work as required, hence the project.

I am currently working on(when I have time) two sections, logs for our refrigerant cylinders as I previously spoke to you about plog, and this one. By looking at two systems, eventually to be linked, it's helping me understand the workings, different methods, and each is complementing the other as something maybe clear on one db but not the other and then I can relate the two.

You'll probably see a lot of me over the next 5 years, sorry about that , but as I learn I will also help others when I can. So any pointers and logical explanations are greatly appreciated.

I think I now have a clearer understanding of the relationship of tables, thank you plog, no spider webs anymore . Thanks very much for suggesting vb minty, I've just learnt that the code can be quite different to sql and am already struggling just trying to add an iif between statement.

Anyway guys, enough of the essay, got a db to write upload coming, just wanna make sure got enough info in there

 

[Delid4ve]

For now im just focusing on the basis of
a fulltime employee,
a constant 20 days standard hoilday entitlement
factor in years of service (1 day extra for every full year employed (max 5)

just to learn how to write a query, a function(thanks minty)
Once i am confident on the basics then i will extend to bank holidays, reserved days, pro rata etc, add in user input for an employee, year, and so forth and will hopefully need less help, i do learn pretty quick

I think i have my relationships pretty sound, maybe a few stray ones that need altering/developing and extra or not needed fields that require adjustment but one step at a time.

db attached

 

[plog]

The good news is you read up on normalization. The bad news is, you over did it. You have a lot of unneeded tables. A table with only 1 real field of data isn't necessary. An autonumber primary key doesn't count as a real field of data (it's a field internal to the database, used to make relationships).

Take HolidayYear for example, this table serves no purpose. Why not just store 2014 in HolidayEntitlement instead of using 1 and having to link to HolidayYear to get 2014? Every table with 1 real field should operate that way--instead of using a foreign key and linking to an unneeded table, just store the value of the unneeded table in the table linking to it. ShutdownPay, ContractDay, DateRequested, ChristmasCallOutDate

Once remove all those tables, you still probably have too many tables. I understand the Contract-Employee relationship, but can't figure out what all those branches coming off of it do: ChristmasCallout, HolidayForm, ContractDayDetail. Can you explain what those branches represent?

 

[Delid4ve]

haha, cant win lol.

I can see what you say for the year, i can create a lookup list for the field itself.
What about this situation:
ShutdownDay, ContractDay, DateRequested, ChristmasCalloutDate
These are all many to many relationships.
Take for instance ChristmasCalloutDate:
We will enter these dates yearly once agreed when we are to shutdown via form.
Each holiday allocation for each employee will have different shutdown days coming off of their allocation for the year.
Say part time employeeA works thursdays and shutdown days for that year fall on wed thurs and friday. The shutdownday for thursday would need to come off their holiday allocation.
Whereas EmployeeB works Monday and tuesdays, so none of the shutdown days are required to come off of their allocation. Hence my theory of needing a transaction table
(Bankholidays the same)

Contractday - well surely(imo) in order to evaluate which shutdowndays, bankholidays are to be deducted i need to know what days the employee works? also a many to many relationship to contracts, or not? many days can be on many contracts.

Holidayform - when an employee gives us a request for hoilday it can have numerous dates on, many holiday forms could have many dates, or not?, hence again the transaction table.

The above should also explain a little better what the branches are.

Is my thinking wrong?
If all we had were full time employees that got 20 days per year, had all bank holidays, and were off all over the shutdown period it would be so simple, but, it has to work for partime employees too, under prorata holiday allocation regulations, ie the normally work when a bank holiday falls they get paid, they dont normally work they dont get paid.

If you know of or have a simpler way or if im completely mad and barking up the wrong tree then enlighten me please. Simpler the better after all, easier to maintain, less corruption etc etc.

Thanks for the input and feedback.

 

[Delid4ve]

Oh, am i on the right track here for a function:
How would i then use the result of the If into another caluclation within the function?

Public Function Holiday(ContractLength As Integer)

If [ContractLength] / 12 < 1 Then
result = 0
If [ContractLength] / 12 >= 1 < 2 Then
result = 1
If [ContractLength] / 12 >= 2 < 3 Then
result = 2
If [ContractLength] / 12 >= 3 < 4 Then
result = 3
If [ContractLength] / 12 >= 4 < 5 Then
result = 4
If [ContractLength] / 12 >= 5 Then
result = 5

 

[plog]

I vaguely understand your concepts. Shutdown days sounds like when you close up shop completely and nobody works. No idea on ChristmasCallout. HolidayForm exactly the same as a Shutdown day. Am I close?

ContractDays is an unnecessary table (1 real field), ContractDayDetail isn't (2 real fields). You use ContractDayDetail to determine what days each contract works, but you don't need a seperate table to tell you 1=Sunday, 2=Monday, etc.

 

[Delid4ve]

That is correct, shutdown days is when we close up for the xmas period, normally three days per year. If these days fall on a partime employees working days then they form part of their already used allocation of holiday.

christmas callout is for our engineers. an engineer is on call for one day over the shutdown period, the day that they are on call they dont use a days holiday, the other days they do use a days holiday, hence the need.

Ah ok i see what you mean with the contractdays now, delete the day table, ill also rename the detil table to shorten and field, put the day into this table and just, ammended attached.. .this what you mean..

Would it be best to change the shutdowndates etc as i have the contract days? These need to be updated all the time, how easy is it to update lookup lists from a form? better method of checking these dates if they are in a seperate table? i feel like ill suffer on functionality by moving them also to a list as they are not predetermined days like the 7 of the week

 

[plog]

I would move all your special days (ShutDown, ChristmasCallOut, etc.) into 1 table. You would store the date along with a new field called DayType which would tell you if it was a ShutDown, ChristmasCallOut, etc. date). That will make it easier to manage and use them.

 

[Delid4ve]

Ive had a change about after speaking to you yesterday, alot of tables gone, i also see what your saying about the day types, ill have a play with this later.

How do i go about the attached pictures:

Form created for entering holiday information with subform for the dates requested (now a one to many relationship)

When i try to add a date to the form i get the error message in picture attached

What am i doing wrong here? this is why I originally had the transaction table as I couldn't find how to fix it. Its probably something really basic or im doing something wrong.

As you can see, massive reduction in tables, mainly due to the fact that i had tables for data that can be calculated on the fly, just didn't see it from that perspective and sometimes its easy to overcomplicate things.

 

[plog]

Forms are a ways away. Focus on your tables. Like I said in the previous post, I think you should combine tables. Specifically, all the branches that are similar (Shutdown, ChristmasCallOut, EmployeeReserved). Those 3 branches should be consolidated.

After you do that, I think that data should be assigned to the Contract, not the Employee. The Contract is what has Start and End dates, not the employee. The Contract defines what days are worked, not the Employee. Right?

 

[Delid4ve]

like this?

I see what your saying, your educating me thanks

 

[plog]

That looks better. 1 question: What do you intend for your Day field to hold? Is it the day of the week DayDate falls on? If so, you don't need it because its calculabe with the Weekday function (http://www.techonthenet.com/access/functions/date/weekday.php).

Also, thinking about it now, I don't think Days needs a ContractID field and it may not be related to any of your other fields. Days is going to contain a list of days applicable to all contracts, right? I understand that if the DayType=Engineering or something like that it will only apply to Engineers, but it will apply to all engineers and not based on their Contract.

If that's correct, I don't think you should relate Days to anything. Also, if I am correct about certain DayTypes being applicable to certain postions, you are going to need to define positions somewhere. How do you know which contracts are for Engineers? Is it in ContractType field?

 

[Delid4ve]

1: yes day field has the day of the week as a set lookup list, so no need for this then.

2:

Different Table_Days will apply to different contracts:
For instance:
three different christmascalloutdays(daytype) this year will apply to three different engineers(employees) or as we have now changed, the contract that they are on.

Two of three(changing per engineer) shutdowndays(daytype) this year will apply to three different engineers(employees) or again their contract

Three of three shutdowndays(daytype) this year will apply to other employees.

thats just a few combinations, there are many more.
These days will also change each year
So i do believe the relationship needs to be there.
Daytypes are: shutdown(days company closed)christmascallout(days engineers on call over christmas)bankholidays(what it says, although not all apply to all contracts)reserveddays(days that employees are required to save over christmas, also different per employee/contract)

im now doubting whether this is correct

part time employeeA works monday tuesday wednesday as defined by their contract. They do not have any christmas callout as they are office based, this year reserved holiday days would be tues 29th dec and wed 30th dec as defined by their contract working days falling on tues 29th and wed 30th and taking into account the shutdown days of 29th 30th and 31st.

engineer employeeA works every day as defined by their contract. They have christmas callout day of tues 29th december(this is a company assigned date/variable user input), this year reserved holiday days would be wed 30th and thurs 31st as defined by their contract working days falling on the shutdown days of tues 29th wed 30th thurs 31st minus their christmas callout day of tues 29th.

Havnt worked out the formula yet, shouldnt be too complex in maths terms, as a function

 

[plog]

I only vaguely understand your concepts. Your explanations are assuming I know the intimite detail of each type of day, I do not.

Right now, we are hammering out how much data you want to store. I think it should be less than what you are headed toward. Let's assume 2/6/2016 is Plog Day, a national bank holiday that your shop will be closed for. As it is, you will need to enter that into your Days table once for every contract. Contract=171 gets a Plog Day record, Contract=299 gets a Plog Day record, Contract=41 gets...etc. etc.

I think that's overkill. I want it in your Days table only once. This makes it easier for you to add it and edit it if needed (if my shady past gets found out and Plog Day gets rescinded). That's why I don't think Days should have a ContractID. You make one entry in Days for Plog Day and it gets fed to everyone.

Now, for days specific to certain contracts, I think it would work better to create a new table that groups Contracts together. You could assign a set of Engineers to 'Group1Engineers' and then in Days you could have that as a type (or possibly a new field) to show who its applicable to.

With that said, you allocating days by contract would alleviate both those situations I described above--however, its just a ton of unnecessary data that needs to be maintained.

 

[Delid4ve]

Right, after just writing a whole post and then the iPad battery going flat and me losing it all I'll keep it short.

Shutdown day and reserved day I have realised are the same so that's gone
I agree these should just be a standalone table to make input easy I do however need to add an engineer on call column which will apply to bank holidays and christmas callout for a formula to work, everything can be calculated between the two tables via a formula and output to a report


Which will resemble something along the lines of:
User input - year
User input - employee

Holiday allocation including bank holidays =
latest contract.standardholiday +
extra days for each full year of service to maximum of 5 days(diffdate(employee oldest contract startdate and 31/12/for year, nested iif statement for diffdate result to determine extra days)) +
(day.daytype =bankholiday for year where weekday = a contract day) -
(day.daytype = reservedday for year where weekday = a contract day) +
(day.daytype= Christmascalloutday for year where weekday = a contract day and day.employeei = employee) +
(Day.daytype =bankholiday weekday for year and day.employee = employee)
*engineers get an extra day off in leui if they are on call on a bank holiday

The whole lot then needs to be put through another formula so that if a whole years service has not been completed, ie they started part way through the year or left part way through the year then the amount is reduced

This figure then rounded up to the next integer unles it already is an integer

Now I just gotta work out how to write that in vb
That's just an explanation with some field names thrown in above - I know it won't work as is and is complete jargon

Thanks for all your help plog, it's becoming clearer and clearer in my head, trying to post back what I've changed to show that I am learning slowly but surely.