Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: how to get report on many conditions based on date

    i have attached database which has two tables

    table1 contains the details of instrument which will come in calibration report and is used for printing certificate

    table1 name ) instrumentmaster

    the important fields by which the database should work

    instrumentnumber ( it contains the identification number by which instrument is traced for calibration )
    regdate ( registered date)
    monthofcal ( which month it is calibrated it will be same till the instrument is dissused or scraped)
    plant( which company uses )
    shop ( which shop uses)
    mfgdate ( year of purchase )
    freofcal ( frequency of calibration when it should be calibrated like 6months, 1year, 2year, 3year, 5year, )


    table2 name ) calibcertificate
    the important fields by which the database should work


    instrumentnumber ( the identification number by which instrument certificate is stored )
    caldate (when it is calibrated)
    statuscer ( what is the result of calibration ( ok, notgood, repair, scrap)
    nextcalduedate ( when it should be calibrated next )

    for this Database as i wish and every thing is working with the help
    SINNDHO and HEADLEM AND MISSINGLINQ AND MYLE AND JERRYDAL and MIKETHEBYKE AND PBALDY

    The problem iam facing now is when i want to see the result

    if i select from reportform ( form which report are generated )
    and click " calibration month status "

    plant (p1)
    shop (qd)
    month(jan)

    in the result i get all the detail result of calibration of this number
    ---- ok---- ok---- ok---- cal date
    Q09427F51 ---- ok---- 01/jan/2010
    Q09427F51 ---- ok---- 01/jul/2010
    Q09427F51 ---- ok---- 01/jan/2011
    Q09427F51 ---- ok---- 01/jul/2011

    which has been calibrated atleast 4 times ( report of past year which should not happen)


    now i want the report like this

    click " calibration month status "

    year(2010)
    plant (p1)
    shop (qd)
    month(jan)

    it should find the above records and

    1st calculation

    1) it checks for freofcal ( frequency of calibration when it should be calibrated like 6months, 1year, 2year, 3year, 5year, )
    example
    if regdate is 01/jan/2010 and frequency is 6months it will come twice in 2011 year in jan2011 and jul2011

    if regdate is 01/jan/2010 and frequency is 12months it will come once in 2011 year in jan2011

    if regdate is 01/jan/2010 and frequency is 24months it will come once in 2012 and not this year jan/2011
    in remarks column it should show jan/2012 so that the user will under stand it is calibrated in jan2011


    2) if statuscer Result is "OK" , "NOTGOOD" , "REPAIR", )

    it should come in report

    the inside issues are

    3)if statuscer Result is "SCRAP" it instrument should not come in report )


    4) if for this year(2011) calibration is not done there is no record it should give as " NOT CALIBRATED " IN REMARKS COLUMN in report

    5) counting based on statuscer

    how many "OK"
    how many "NOTGOOD"
    how many "REPAIR"

    excluding 2012, and instruments

    now iam confused how to do this and not able to find the sequence in solving the problem

    i know this can be done but i dont know access properly

    can you tell me B]HEADLEM[/B] what changes i need to do in design and how to get result based

    year
    plant
    shop
    statuscer
    frequency

    tomorrow i will attach my database with changes so that it will be easy to go
    Attached Files Attached Files
    Last edited by eddi; 10-19-11 at 07:27. Reason: for attaching databasefile

Posting Permissions

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