close

Privacy guaranteed - Your email is not shared with anyone.

OT: Microsoft Office Access

Discussion in 'Band of Glockers' started by Kiddo, Feb 11, 2006.

  1. Kiddo

    Kiddo

    Messages:
    560
    Likes Received:
    0
    Joined:
    Jun 14, 2003
    Any BOG familiar with MS Access? I'm trying to create a simple database system and I have a couple of questions. Thanks. :)
     
  2. mc_oliver

    mc_oliver

    Messages:
    3,499
    Likes Received:
    0
    Joined:
    Feb 21, 2002
    Location:
    Philippines

  3. Kiddo

    Kiddo

    Messages:
    560
    Likes Received:
    0
    Joined:
    Jun 14, 2003
    I'm trying to create a database for our ROTC unit. We are currently using excel as our cadet information and grading database and I am thinking if going to access would make the work easier than browsing through spreadsheet after spreadsheet. Hehe. :)

    I have already created a simple form where I could encode the basic personal info such as name, address, contact number, course, etc. I am looking for ideas on how to create (clueless really!) a form on encoding grades/attendance/merits/demerits. Each cadet is allowed 3 absences per semester, and on the 4th absence, the cadet will be dropped. Also, each cadet starts with 100 merits. when his merits goes down to 50, the cadet may be dropped from the course.

    I would like to start with a form which would allow you to encode if the cadet has been absent or if the cadet received demerits that day, then maybe access will then automatically (if its possible) show a report of cadets which have reached the 4 absences/50 demerits mark.

    I hope its not too confusing. Access newbie here. Hehe. Thanks mc_o! :)
     
  4. Kiddo

    Kiddo

    Messages:
    560
    Likes Received:
    0
    Joined:
    Jun 14, 2003
    Update:

    I'm looking for a way where a database user could be able to increase the number of demerits/absences of a cadet (for example), but would not be able to decrease it. Is this possible? I tried experimenting with the different user groups and found out that the update user group can add as well as subtract the number of demerits/absences, while the new user group could only add new entries to the database.
     
  5. mc_oliver

    mc_oliver

    Messages:
    3,499
    Likes Received:
    0
    Joined:
    Feb 21, 2002
    Location:
    Philippines
    So what you're trying to do is set User levels/access to your system, right? That way you can control which modules they have acess to.

    That being the case, dre, I think you should go straight to using Visual Basic for your interface. Use only the Access DB as your repository and extract your data using SQL queries from inside your VB code.

    You can then push that data into excel to print your report or do other stuff, away from your actual data. You can code all these in VB and the PC doesn't even need to have Access installed.

    But are you familiar with VB and SQL query strings?
     
  6. Kiddo

    Kiddo

    Messages:
    560
    Likes Received:
    0
    Joined:
    Jun 14, 2003
    I have no experience at all with running Visual Basic but if its really advantageous over Access, I may give it a try. Is VB similar to user friendliness of Access?

    Thanks!
     
  7. darwin25

    darwin25 Make your move

    Messages:
    1,354
    Likes Received:
    0
    Joined:
    May 16, 2003
    Location:
    Sa ibabaw..minsan sa ilalim
    Hi, Im a Database Admin at ABS-CBN and and part-time DB programming instructor at Informatics SM North Edsa. I teach MS Access, VB6, VB.Net and SQL Server programming.

    Here's what you should do:
    1. Check if you MS Access has HELP installed. Kapag wala, you should find an MS Office Installation na may HELP na kasama.
    2. These keywords may help you:
    Tables: What are they and how they work
    Queries: What are they and how they work
    Forms: What are they and how they work
    Macros: What are they and how they work
    Reports: What are they and how they work
    VBA, Event Procedure

    3. Learn table Design first. The topic Tables: What are they and how they work should cover these topics.
    table design, fields, datatype, data vs. information.

    Kapag nabasa mo na yung about tables then pwede ka na magtanong. I will guide you hanggang makagawa ka ng application mo. Dito na lang sa forum para makapagtanong din yung ibang interested. I can also send you my MS Access study guide. You can e-mail me at da_pasco@yahoo.com You can also use these tutorials
    http://www.officetutorials.com/accesstutorials.htm

    http://www.aspfree.com/c/a/Microsoft-Access/Getting-to-Know-MS-Access-2003/

    Hindi ka namin matutulungan hanggan't wala ka kahit basi knowledge about tables so read first.
    mc_o: I dont think kaya nya na sa VB6, kasi kelangan pa nya matutunan yung mga data access technologies at the least DAO or ADO 2.0. MS Access has its own VB Editor. It uses VBA which is built on VB6 technology so pwede din sya mag event programming sa MS Access. at least di nya problem yung mga connection strings and SQL statement or stored procedures. Very similar yung VB Editor ng MS Access at VB6. In fact if a person knowledgeable in MS Access, one can create appliactions that you can compare with apps built with VB6 or even VB.net. But if anybody wants we can transform this thread into MS Access, VB6 or even VB.Net tutorial.
     
  8. mc_oliver

    mc_oliver

    Messages:
    3,499
    Likes Received:
    0
    Joined:
    Feb 21, 2002
    Location:
    Philippines
    O ayan, libre consultation pala kay Prof. Darwin. ;f

    Kiddo, go with what's easiest to you. I also suggest you bug Darwin with all your questions. Importante malinis yung initial design ng system mo. Otherwise, 'di na matatpos yang ka co-code mo ng "enhancements.";)

    Btw, if you have the installer for your MS Office the Access help files are there. Baka kelangan mo lang i-custom install so you can install it. Also, if you know somebody who has the MSDN Help Installer CD's okay din yung pang-complement sa Help ng Office.

    Good luck.
     
  9. Kiddo

    Kiddo

    Messages:
    560
    Likes Received:
    0
    Joined:
    Jun 14, 2003
    Thanks mc_o and darwin! I'll be reading up on those help files this afternoon and will ask questions maybe later. :)
     
  10. toxic

    toxic

    Messages:
    1,491
    Likes Received:
    0
    Joined:
    Jan 15, 2005
    Location:
    doin' time
    ba ayos to ah, mapag aralan nga.
    Tuloy lang diccussions guys very informative.
     
  11. darwin25

    darwin25 Make your move

    Messages:
    1,354
    Likes Received:
    0
    Joined:
    May 16, 2003
    Location:
    Sa ibabaw..minsan sa ilalim
    Tables
    A table is a collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once. This results in a more efficient database and fewer data-entry errors.

    How data is organized in tables

    Tables organize data into columns (called fields) and rows (called records).

    [​IMG]

    For example, each field in a Products table contains the same type of information for every product, such as the product's name. Each record in that table contains all the information about one product, such as the product's name, supplier ID number, units in stock, and so on.

    Types of Data

    Text - Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers. Up to 255 characters or the length set by the FieldSize property, whichever is less.
    Memo - Lengthy text or combinations of text and numbers. Up to 65,535 characters.
    Number - Numeric data used in mathematical calculations. Can either be Integer Long Integer, Single, Double, Percentage or Decimal.
    Date/Time – Used to store date and time values for the years 100 through 9999.
    Currency - Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. Accurate to 15 digits on the left side of the decimal separator and to 4 digits on the right side.
    Autonumber - A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated.. Used to uniquely identify records
    Yes/No - Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off). More commonly known in programming parlance as Boolean.
    OLE Object – Object Link and Embed. Used to embed or link an object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) in a Microsoft Access table.

    You can only set datatype properties in table design view.

    It is important to correctly identify the datatype for each field. One cannot perform mathematical operations on fields that are declared as TEXT even if the values stored in the fields are number. For example:

    Field Datatype Value
    Num1 Text 1
    Num2 Text 2
    If one tries to perform arithmetic operations on these the fields using this formula
    expr1 = Num1 + Num2 (1+2)
    The result will be: expr1= 12.

    The program simply concatenates both values because it is treated as text. Thus in order to get the correct arithmetic result one has declare the correct datatypes.

    Field Datatype Value
    Num1 Number 1
    Num2 Number 2
    expr1 = Num1 + Num2 (1+2)
    The result will be: expr1= 3.

    Primary Key : One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

    Each record must be identified by its unique value. For example you have a table for your list of employees. The table has fields for every single information that you have about your employees. Every employee has to be identified by its unique identifier such as Employee ID, SSS ID, etc.

    Tips in creating tables

    Table names: Should be easily identifiable. The sould be no spaces. One should use underscore instead of a space. For example, Product Table should instead be named as Product_Table or ProductTable. This is important if you plan to do VBA, VB6, or .Net programming. The name Product Table is read as 2 words instead of one entity. In VB programming, every word are read as one entity, thus Product Table are read as Product AND Table.

    Field Names: the same principle in naming tables apply in naming fields for the same reason.

    Creating fields

    In creating a table, the programmer should meticulously determine all the fields that will be necessary and determine all the correct datatypes. For example, in an employee table one should have these ff fields:

    Employee_Number, First_Name, Last_Name, Middle_Name, Birth_Date, Address1, Address2, LandPhone, CellPhone, Gender, Civil_Status, Date_Hired, Date_Resigned, SSS_Number, PAGIBIG_Number, Bank_Account_Number, Basic_Salary, Allowance, Employment_Status, etc, etc.

    Important : All information regarding the entity should not be discriminated and should be included as much as possible. It is much better to have the data that you do not need than to realize later that your collection of data is lacking. You should however, make sure that your table is normalized, i.e., there should be no duplication of fields.