i have a table where I mark attendance, 1-mar, 2-mar, 3-mar, 4-mar and so on. how do i create a query where if for 2 continious days the attendance marked is "Leave", then the query pulls the names out in a different table and shows the days also and if after that if the attendance marked is "present" then the name does not show up.
How are you going to handle public holidays, weekends etc? What are the fields you have and what is your table structure? What are you actually trying to produce as a result. Are you limited to only queries?
"Vague. Horribly horribly vague."...thank you for your appreciation.....
lets not get into wkends/holidays..that's a different part...which makes not sence explaining to handle the task at hand.....i am attaching a screenshot of the table. there is a form through which the attendance is marked. W off's/ pre approved leaves and holidays are entered at the beginning of the month.
what i want is...
1) emp Id- 125 should get picked up in the query on 8-Nov
2) emp id - 124 should not get picked up in the query on 8-Nov
3) Emp ID - 126 should not get picked up in the query on 5-Nov
this query should get refreshed every day, for future dates the fields will be blank and should not cause a problem in calculation.
So, do you intend to put every future date in as a field in your table? Or just all the dates for a year?
I refuse to give people guns for which they can shoot themselves in the foot with. Your table design is in need of serious attention and I refuse to help you dig yourself into an even bigger hole by solving this "one problem". With that design you are going to encounter a great many more.
Somewhere in your table design, you should have an Attendance table with EmpID, AttendanceDate and AttendanceCode. This should relate to your AttendanceCodes table and to your Employees table. Something along those lines anyway.
Don't hate on me for this, believe it or not, I am actually trying to help you.
as i said...all that stuff is separate...i have a database table, codes table linked to this table.....the point is, to run this query this table needs to be understood...everything else is secondary.......thank you for no help......n i don't think ur on that level that u have the guns urself.....don't hate u....just pity....u've got all the time to blabber...than find a solution.......
Without having a more precise idea of the structure of your table it's almost impossible to help you and a screenshot is not enough to understand how your system is organized. You said you have a database table, codes table etc. Why don't you explain that clearly so we can try to help you?
No need to be rude to people here either, StarTrekker was really trying to help you, believe it or not.
did'nt mean any harm either..but...let's forget abt it.....let's see if i can explain it....
i am attaching a screenshot of the relationship and the form thru which the attendance is marked. Now, at the beginning of the month, thru an excel sheet, all planned wk off;s and leaves are imported in the db file. attendance is marked on a daily basis, and is restricted thru a drop down (codes) for each day alligned to the particular date.
now, as and when the atendance is marked...i want that quesry to give me an output thru the Table 1....
does this help..in explainaing....caus i was wondering, even if i have a simple table and i want to run this query on it, why would it require the relationship to be explained....but as they say....a novice is a novice....:-)...lemme kno...
Just to reinforce the posting of the other esteemed contributors, this table design is an example of probably the single most common error in relational database design. This is why both felt able to say this. There are many principles to produce properly designed databases.
If you absolutely insist on keeping the design, there are ways to get what you want. But it requires ten times the code and solves this one problem only - you will hit other problems again in the future.
Please read the article and come back when you have done so. We can help you design this correctly. I'm afraid I don't have time to help with the code as your design stands.
believe it or not...i did try and run through the normalisation....but....cud not stick for very long.....if you were to create this....how would you do it.....please use this as an example so that i can understand it..caus it is very difficult to read and understand a dry column rather than seeing a live practical example....
also as i said, every month, the excel sheet is imported in the db file, which has new data with the dates, w off's and aproved leaves for the month in advance. the reason it is done in this way, every month, the w off's change for everyone and which are decided in the last week of every month for the next month...so to answer your question...yes....when i import the table, it is loaded from 1st to the 31st..that's fixed.