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.

Microsoft Access Experts

Discussion in 'Business Applications' started by hotdiamond, Apr 16, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Hi Everyone. I have an Excel spreadsheet that I would like to put in Access. I would like to make it look pretty in Access, like in a form, but I'm not sure how to get it there and then be able to calculate the same results that Excel calculates for me. I know this may sound confusing, but I'm trying to explain the best I can. If anyone would be willing to help me, please let me know and I'll be happy to send the excel file to you. Basically, it's a form that was put into Excel so that each component could be calculated easily. The spreadsheet basically calculates all the "yes's", "no's", and "na's". The spreadsheet was created by my coworker and is not very user friendly. Someone could very easily make a mistake using it. So if anyone would like to help me, just let me know and I can try to give you more detail with what I'm talking about.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Would this be like a survey form? If it is you would need to make a form in Access with all of the questions in your survey and option buttons for each question. This can get kind of lengthy but it is pretty simple to do. The only problem is that you will need to differentiate between each person taking the survey if that is important. If you just want a aggregate of all of them it's very easy.
     
  3. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Yes, it is similar to a survey. So if I develop a form, will I still be able to calculate the results that I need accurately and easily?

    The form would be the same, but I will need to be able to make it specific to a certain person and also I would like to know who actually filled out the form.

    Is all of this possible? I'm not too Access literate. It confuses me at times.
     
  4. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Yup, this is all possible.

    First step is getting the data into Access. The easiest way to do this is by first making a copy of your spreadsheet (NEVER use an original). Now arrange the data so that the first row has your field names (if they are longer than one word, then delete the space and capitalize: First Name becomes FirstName).

    In order to get the data into a table, get rid of the calculations for the Yes/No/NA...you will be able to recreate these in queries.

    Open Access and give your new database a name. Now see that a window is open with Tables/Queries/Forms/Reports (and so on). While you have Tables visible, click File>Get External Data>Import. At the bottom, make sure you have chosen "Files of type" Excel Spreadsheet xls and browse for your spreadsheet.

    When you find it, highlight it, and click Import button. Go through the wizard carefully, it will verify that the first row is the field name/column headings, then if it will go into a new table, then whether to import each field, and finally, assign a key.

    Just for clarity, a key is a unique identifyer for that particular record, first names, last names, are terrible, because they are easily duplicated. If you are unsure or if the data is easily duplicated and is not problematic as duplicates, then don't assign a key.

    Then you name the table, et voila, finis!

    Now that you have your data in there, you can play around with the Access wizard to see what kind of forms you can create.

    If and when you get this far, feel free to post an update for us and we can help you with creating the queries that will pull your totals, etc.....and, of course, we can answer any questions about tables/forms/queries/reports.

    Hope this helps (as a start),

    MBN
     
  5. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Thanks so much MBN! I haven't attempted to create the table in access yet, but I will try to do it before the day is over. I started creating the form Friday. The only thing I have left to do with the form is insert the table with yes, no, and na. Would it be easier to calculate if I created a list box with yes, no, and na? I think it would look prettier, but I want it to be easy to calculate. Please continue to help. This is something I really want to accomplish. We just got new laptops and this will make data entry so much easier while offsite. Thanks a bunch for helping me.
     
  6. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Okay I have the form completed with the actual table in a subform. Now I need to know how to develop the "yes", "no", and "na" list boxes within the table. I cannot think of what to do. I've done it before, and I know it's something simple. I know I go to Design view, but then I get confused. Please help.

    Thanks so much for ya'lls advice.
     
  7. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Well I figured out how to make the list box, but now my subform is too big. All of it won't show in the form. Is there anything I can do to make all of it visible in the form? I have 42 columns with text. Please tell me it's possible. Thanks a bunch.
     
  8. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Okay, I'm a bit confused. (not an unusual event in and of itself! :D)

    Anyway, I'm not sure why you would have created the form first, then added the table....usually it's the other way around.

    What is in the main form?

    Do me a favor....could you provide a bit more information...like what fields you need/have and which ones are being stored in your form....?

    Also, regarding the listbox, try a combobox (dropdown box), this will probably handle the size issue better.

    Your other question is rather simple. You are trying to approach your form as if it were an excel spreadsheet. Instead, think of your form as a way to view each record in the list....You could show more than one form on the screen, but an individual form should reflect only one Yes/No/NA answer.

    Post back with field names and I can help you get this organized.

    MBN
     
  9. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    MBN, my fields are the actual questions that I'm looking for. There are a total of 42 questions, which can be answered "yes", "no", or "na". That is why I want a combo box that has "yes", "no", and "na". I did creat a table that I'm using to finish the form, but I'm really confused now. Even if can get the form to look pretty and user friendly, I'm afraid it won't give me the calculations that I need it to.

    I don't know if I can explain it better. But I'd be willing to email you the original Excel spreadsheet and calculations and the Access database that I'm attempting to build.

    Thank you so much for input so far.
     
  10. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Yup, go for it...I emailed you earlier about my post, so you should have my address. Let me take a look - also send the dbase you've created so far, that way I can see what you have in mind....

    MBN
     
  11. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    MBN, I just emailed you the excel file, but the access database is too large.
     
  12. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Okay, I just attempted to email the access database. Hope you can open it.
     
  13. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Okay, have had a look. It appears that there will be several tables needed.

    First, each item that requires a Yes/No/NA has a point value; the item has a value, not the answer (it ISN"T a yes=2, no=1, na=0 type of thing). So the first step is to create a table with each of the items and its corresponding value.

    From here I need more info about the other fields (Name, address type fields). and we will work from there.

    MBN

    PS: working with hotdiamond via email so I'll try to keep this thread up-to-date.
     
  14. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Okay, so you're saying I need to create a table for each of the questions? And how do I apply the corresponding value?
     
  15. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You do not need a seperate table for each question. Make a seperate field within a single table to hold each question. You can even put the users name in the same table if they are only taking one survey. Set up the form as data entry olny with each of your questions corresponding to the field in the table. Add an update button that will enter the new record. Create a query that will group the results and sum them up. You can then use this query to fill in a listbox and will refresh every time you add a new record to the table.

    All it takes is one table and one query to make it work and a little form refresh event.
     

    Attached Files:

    • app.jpg
      app.jpg
      File size:
      28.9 KB
      Views:
      153
  16. 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...
Thread Status:
Not open for further replies.

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

  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