SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Iimmigration db

  1. #1
    SitePoint Enthusiast Nasser's Avatar
    Join Date
    Jul 2001
    Location
    Qatar
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Iimmigration db

    Hi folks
    I’m managing the immigration section at HR department; my employees are responsible to process all immigration activities for our company, we have about 1000 employees.
    At present they give me a monthly report about their activities. Its shows that information:
     Staff Number
     Name
     Department
     E-mail
     Passport Number
     Expiry Date
     Nationality
     Expiry Date
     Residence Permit Number
     Expiry Date
     Date Of Birth
     Receiving Date
     Submission Date
     Submitted By
     Finished Date
     Fees

    The activities list are :
     ENTRY PASS
     DRIVING LICENSE (new renewal)
     house maid visa
     six months extension
     VISAS THROUGH EMBASSIES
     SPONSORSHIP CHANGE
     ID RENEWAL
     RP CANCELLATIONS
     FAMILY PASSPORT
     EMPLOYEE PASSPORTS
     VISIT VISA
     FAMILY VISAS
     MULTIPLE EXIT PERMITS & DEPENDENTS EXIT PERMITS
     MULTIPLE ENTRY VISA
     MEDICAL COMMISSION AND FINGER PRINT
     NEW EMPLOYEE VISA
     BUSINESS VISA (BY AIRPORT)

    Immigration User:
     A
     B
     C
    Now I want to use Ms Access to monitor their activities online instated of Excel reports, here are my required reports:
    1. What are the activities per date?
    2. For which employee, what activity? Submitted by whom? Finished when? How much it cost?
    3. Which employee’s passport will be Expiry next month (e.g. October)?
    4. How much employee X cost us for all his immigration processes (activities)?
    5. Who is birthday next month?
    6. How many X Nationality we have?


    Since I’m new to MS Access I have some confusing about setup the table’s relationships in order to get the above reports.
    Note: The activities & Immigration User should be as pull down menu selection.

    I’ll be highly appreciating your help guys.



  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this:

    ** nation
    nation_id PK
    name

    ** department
    department_id PK
    name

    ** title
    title_id PK
    name

    ** employee
    employee_id PK
    department_id FK
    title_id FK
    staff_number
    date_of_birth
    firstname
    lastname
    email

    ** passport
    passport_id PK
    nation_id FK
    employee_id FK
    passport_number

    ** passport_renewal
    passport_renewal_id PK
    passport_id FK
    date
    expiry_date

    ** residence_permission
    residence_permission_id PK
    employee_id FK
    residence_permit_number

    ** residence_permission_renewal
    residence_permission_renewal_id PK
    residence_permission_id FK
    date
    expiry_date

    ** activity_type
    activity_type_id PK
    name

    ** activity
    activity_id PK
    activity_type_id FK
    employee_id FK
    registered_by FK (employee_id)
    date_start
    date_end
    (or maybe just "date")

    ** activity_fee
    activity_fee_id PK
    activity_id FK
    registered_by FK (employee_id)
    date
    fee
    Last edited by jofa; Oct 5, 2002 at 10:40.

  3. #3
    SitePoint Member
    Join Date
    Apr 2002
    Location
    Amman, Jordan
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so you are from qatar!
    thats nice....iam from jordan!!
    it is nice to see arabs participating here>

  4. #4
    SitePoint Enthusiast Nasser's Avatar
    Join Date
    Jul 2001
    Location
    Qatar
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thank you jofa;
    I’m truly appreciate your input , do you think if I creates the db as per your structure I'll be able to get the required reports ? if yes , any further advises ?


Bookmarks

Posting Permissions

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