1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Creating an MS Access database with many fields

Discussion in 'Business Applications' started by sardaukar, Aug 4, 2019.

Advertisement
  1. sardaukar

    sardaukar Thread Starter

    Joined:
    Dec 23, 2006
    Messages:
    29
    Hello everyone. before we start: I am total newbie to Access (used few times, exited).
    Situation: i am trying to analyze data that has too many fields for Excel. Like - 10 right now and it could be NOT enough. Entering data may bring errors. And sometimes I require field connections.
    My thought was (unrealistic?) to create a MS Access database with many fields. An abstract of fields:
    1. Numbers, entered manually.
    2. Numbers, drop list + choice of "x" (no data)
    3. Letters, drop list with "x"
    4. Letters, drop list with "x"
    5. text, entered manually
    6. text, entered manually. An option to leave unfilled.
    7. text, entered manually
    8. text, entered manually
    9. text, entered manually
    10. text, entered manually
    11. text, entyered manually
    12. text, entered manually
    13.text, entered manually
    14. text, entered manually
    15. Complicated field. It checks field 5 versus fields 7, 9, 11 and 13.
    If field 5 corresponds with, say, field 7 and 9 - database automartically marks with, say, A
    If field 5 corresponds with, say, field 11 and 13 - marks as B
    If nothing, itchecks field 2 versus values entered into database (let us say, 200 positions)., if corresponds - marks with C
    If still nothing - marks X
    16. Check or uncheck. Only one choice. Like - "Is this product forgery?" if yes, user must check, if no - just leave unchecked.

    Question:is it possible to create such thing by myself or I should hire some programmer?
     
  2. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    974
    First Name:
    Morten
    I should think it'd be possible to do yourself, if I understand the fields correctly. But i'd advice you to make some kind of flowchart first to make sure you get everything you need in.
    I'd be curious to see the product when done, just because I have a hard time determining what this could possibly be. None of my business, I know.:)
     
  3. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    418
    First Name:
    Jack
    Hello sardaukar,

    What you are looking for should be easy to accomplish within MS Access.

    As a precaution though, you should evaluate field 15 carefully before designing your database. It is not a good idea to have an Access field (within a table) conduct a "complicated check." Doing these type of operations can cause some nasty data corruption issues.

    It is always best to scan data within the tables and then conduct the "complicated field" checks on the the Forms and Reports where values can be customized as desired. Plus, this way we avoid those quirky memory issues. ;)

    And by all means, feel free to ask more questions if needed. Also, If you keep a second database with dummy data, you can post that when you have problems which will make it easier for us to trouble shoot.

    Best of luck!
     
  4. sardaukar

    sardaukar Thread Starter

    Joined:
    Dec 23, 2006
    Messages:
    29
    Sorry for derailing (?) topic, but...
    I have tried MS Excel. Again. Some person adviced to use IF function and I struggled to make it work. Now, here is a simple sample of data at 001.jpg (using Excel 2007).
    Over-simplified version of what I am trying to accomplish is at 002.jpg.
    Now, up to what I need. To check if:
    B1 equals "01_01"
    E1 equals "John".
    G1 equals "John". If yes, mark field L "Good"
    Check if I1 equals "John. If yes, mark field L "Worse"
    If all failed, check combination :B1+E1 equals to "01_01 John" at field K. If yes, mark field L "Almost good"
    If everything failed, mark field L with "x".

    Any solutions?
     

    Attached Files:

    • 001.jpg
      001.jpg
      File size:
      27.3 KB
      Views:
      3
    • 002.jpg
      002.jpg
      File size:
      37.7 KB
      Views:
      3
    • 003.jpg
      003.jpg
      File size:
      45.3 KB
      Views:
      3
  5. lunarlander

    lunarlander

    Joined:
    Sep 21, 2007
    Messages:
    11,406
    Well, back onto your Access attempt. It is not good database design to have calculated results as a database field. Calculations should be done when doing reports to show the intended result. Just design it so that the fields holds 'raw' data.
     
    Chawbacon likes this.
  6. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    418
    First Name:
    Jack
    I am a bit confused here. Are you looking for Access help or Excel help? We can help with both; however, the formula language differs for each application?
     
  7. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    418
    First Name:
    Jack
    I am a little bored tonight, so I decided to look at this from an Excel view, which I think is what you are looking for at this point.
    Now, IF I am reading this correctly you will need a formula for cells "L" and "K". "Nested If" statements should solve this for you. :)

    Cell "L" =IF(AND(B1="01_01",E1="John",G1="John"),"Good",IF(I1="John","Worse",IF(AND(B1="01_01",E1="John"),"Almost Good","x")))

    With this example, you should be able to figure out Cell "K". ;)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1230998

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice