Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 23 Posts

·
Registered
Joined
·
161 Posts
Discussion Starter · #1 ·
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.
 

·
Registered
Joined
·
21,334 Posts
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.
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #3 ·
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.
 

·
Registered
Joined
·
2,016 Posts
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
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #5 ·
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.
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #6 ·
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.
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #7 ·
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.
 

·
Registered
Joined
·
2,016 Posts
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
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #9 ·
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.
 

·
Registered
Joined
·
2,016 Posts
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
 

·
Registered
Joined
·
2,016 Posts
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.
 

·
Registered
Joined
·
21,334 Posts
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.
 

Attachments

·
Registered
Joined
·
2,016 Posts
Okay, Rockn, point well taken, but having seen this database/spreadsheet, there is much more to it.

It is a survey, of sorts, with each question having a point value. The survey tabulates the results of ten surveys. Yes/No/NA are the possible answers; at the end of the 42 questions, each survey is totalled. After the ten surveys, each question's answer is totalled - then results are given (the equations are in a spreadsheet).

The first problem I encountered was the fact that the questions can be long, very long. So I've numbered them 1-42, then just changed the labels on the form (tables have 64 char limit, labels do not).

At this stage we are going to start working with the values of questions...because each question is worth different points, it is easier to put this info into a table and retrieve it from there when it's needed....

MBN
 

·
Registered
Joined
·
21,334 Posts
YOu don't have to put the questions as field labels at all. Question one's field label can be q1 and so on. You can also do the list like sub form in a crosstab type format query and have the formula set up to assign a seperate value for yes, no or N/A. Is each question given a different value or is each yes, no or N/A assigned a different value for each question? Sounds like he should have used an option group for each question instead of the pulldown list. That way you can assign a value for each question without having to tally it later.
 

·
Registered
Joined
·
161 Posts
Discussion Starter · #18 ·
Rockn, to answer your question, each question is given a different value. Each question is worth either 1 or 2 points. I was contemplating using an option group, but I didn't because I thought the drop down list would be easier. Please give more detail as to why the option group would be easier to calculate.

Also, I wanted to clerify that even though the survey should be calculated after every 10 surveys, there is a possibility that there will not be 10 everytime. Sometimes there may be less than 10. I really hope this isn't too confusing.

Thanks for all of the advice.
 

·
Registered
Joined
·
21,334 Posts
Perhaps the option group is not the best idea for this application if a given question is assigned a single value.
Why is there a need to calculate the surveys after each ten? Are you looking for an average score? What are the point of 1 or 2 dependant on??
 

·
Registered
Joined
·
2,016 Posts
Rockn, no direct dependency on the questions....

Most surveys I've seen (hell, all of them) gave a point system based on the answer; this one has points for question (no indication of why one is more than another), then percentages given on Yes, No, and NA. Over the total number of surveys (standard being 10), percentages/statistics are calculated for each particular question over the 10 surveys as well as overall percentage on all questions for each survey....looking at it as a spreadsheet, think "accross and down"....

I can see why this was originally set up as a spreadsheet.

MBN

PS: The reason I changed the label is so that the person filling out the survey knows what the "question" is....in the table they are simply numbered.
 
1 - 20 of 23 Posts
Status
Not open for further replies.
Top