Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Unanswered: conditional query

    hi,

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    ms access 2003

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    splendid

    i have moved your question to the Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Vague. Horribly horribly vague.

    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?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2009
    Posts
    27
    "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.

    not sure if this helps.......
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Mar 2009
    Posts
    27
    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.......

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.

    Have e nice day!

  10. #10
    Join Date
    Mar 2009
    Posts
    27
    hey Sinndho...

    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...
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

    One of these is a process called Normalisation. There are several stages of normalisation - your table design fails the very first one.
    The Relational Data Model, Normalisation and effective Database Design

    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2009
    Posts
    27
    i kno...it's not what an expert would call perfect...but works for me...if you could help with the query with keeping the desig as it is..it would be great....

    thnx

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by orajat
    i kno...it's not what an expert would call perfect...but works for me...if you could help with the query with keeping the desig as it is..it would be great....
    *Sigh*
    That's the point.
    This sort of thing is right or wrong. There is no perfect vs "right for me".
    You didn't even read the link did you?

    Are you only going to ever have Nov 1st to Nov 8th in your table? If so we can do it with a query. Otherwise, this will not be possible in a query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you absolutely insist on violating first normal form, there is a better way to do things. As you have it now is just about the worst way you could possibly design this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Mar 2009
    Posts
    27
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •