Microsoft Access Experts

[hotdiamond]

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.

 

[Rockn]

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.

 

[hotdiamond]

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.

 

[MustBNuts]

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

 

[hotdiamond]

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.

 

[hotdiamond]

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.

 

[hotdiamond]

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.

 

[MustBNuts]

Okay, I'm a bit confused. (not an unusual event in and of itself! )

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

 

[hotdiamond]

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.

 

[MustBNuts]

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

 

[hotdiamond]

MBN, I just emailed you the excel file, but the access database is too large.

 

[hotdiamond]

Okay, I just attempted to email the access database. Hope you can open it.

 

[MustBNuts]

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.

 

[hotdiamond]

Okay, so you're saying I need to create a table for each of the questions? And how do I apply the corresponding value?

 

[Rockn]

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.