What tables would I need to create?

[Big Monkey]

Hi! This forum has been a god send. I learned tons of Excel stuff here and actually got a promotion thanks to the things I've learned and implemented at work.

That said, I know zero about Access and am wanting to learn it. I'm definitely going to invest in that book on the sticky thread next week.

Anywho, here is what I am trying to do:

I pull data from a source and it is downloaded in CSV format. The fields are as follows:

Date, E-mail Address, Q1, Q2, Q3, Q4, Q5, Reference Number

The Q's stand for "Question" This is survey information and I have to compile weekly results. I've been using Excel fine to generate averages and weekly/monthly graphs.

However, now I've been asked to create Year To Date information. I get about 30,000 records every month and Excel can only handle 65536 rows per worksheet.

I wanted to see if I could somehow convert this process in to Access and work from there instead.

-I have macros that convert the question replys from text to numbers. Is it possible to import from a CSV file and add a record in Access with the conversion already? Or once the records are imported, can a macro be run on certain columns to convert the text to numbers? Say if the text is "Average", change that to a "3."

-If the first part is doable, does Access do graphing at all?

-I have 3 separate workbooks for this. I have two vendors I keep track of and then a 3rd workbook that combined both of their data for an overall average. I suppose I would need to keep two separate tables with data, but what would I need to do to get a report that combines both? I think I've read somewhere about relational databases or tables or something. Like I can link one table to another. Or would that be part of the report...or part of a query?

I guess these must be really simple questions if anyone experienced reads them. What did you guys do to teach yourselves more about Access? Any help will be appreciated.

 

[MyBoo]

Without seeing additional information identifying what information you keep on the vendors and such, it's almost impossible to tell you what tables to create and how to set relationships up.

As for your first table... Date, Email, Q1, Q2, Q3, etc... that could be one table... but again, need to know more information. Do you expect more than one survey response from each email address? Do you want to limit each email address to one survey entry per month?

Will you be storing the questions used in the survey(s) in Access? If so, then you would need two tables... one for the questions, one for the responses.

Before you go too far... get an Access book that talks about table normallization. What you learn here will take you a long, long way. What you don't learn here, will come back and bite you in the ****....

Can Access convert text to numbers.... yes
Can Access create graphs.... yes... It also can do pivot tables and crosstab queries.... you'll find this useful with a survey database.

You can import CSV and lots of other formats but I don't think you can use the import process to convert text to numerics... without creating code to do that for you (something you'll maybe learn much later on). You could import the CSV into a temp table and then create a query that converted and appended the data to your primary table(s). This would be the simplest beginners way.. there are other ways... a better understanding of Access is required first.

Access is a true relational database. You create tables that hold specific (like) data and then you use the Tools/Relationships to link these tables together. Simple Example: Table1 called Names... Contains FName and LName fields as well as a RecordID (primarykey) field. You then create Table2 called Addresses... Address, City, State, Zip and a NameID (may or may not be the primarykey field). The Relationship tool is used to link the RecordID to the NameID, linking the two tables together. You need to understand the different types of relationships also. The above example could be set up as a one-to-one relationship.... one name to one address or a one-to-many... one name to multiple addresses.... This and table normalization are very, very important.

If you don't learn this first, you will be redoing your database in the very near future, or giving up totally. It's the foundation for all Relational databases.

 

[Big Monkey]

Do you expect more than one survey response from each email address?

Yes, it's possible that more than one survey can come from one e-mail address. But I need to limit it to only allow one survey for each unique reference number. When I download the data, sometimes they answer the same incident more than once and there are duplicate survey records. I have a macro to eliminate duplicates currently in Excel.

Do you want to limit each email address to one survey entry per month?

Nope, the e-mail addresses can be duplicated at any time.

Will you be storing the questions used in the survey(s) in Access? If so, then you would need two tables... one for the questions, one for the responses.

Nope. The questions are static and I have no real use for them. All I need to know is how they were answered on a scale from 1-5.

Before you go too far... get an Access book that talks about table normallization. What you learn here will take you a long, long way. What you don't learn here, will come back and bite you in the ****....

I've done some reading on normalization. The jist I picked up is to not duplicate data across multiple tables. All fields must be unique to the primary key. That is the extent of what I learned. I know there is plenty more to it though.

Can Access convert text to numbers.... yes
Can Access create graphs.... yes... It also can do pivot tables and crosstab queries.... you'll find this useful with a survey database.

Sweet !

You can import CSV and lots of other formats but I don't think you can use the import process to convert text to numerics... without creating code to do that for you (something you'll maybe learn much later on). You could import the CSV into a temp table and then create a query that converted and appended the data to your primary table(s). This would be the simplest beginners way.. there are other ways... a better understanding of Access is required first.

That sounds perfect. That is pretty much what I do now. Format data and then append to other worksheets.

Thanks for the help guy. As a beginner, what advice can you give me to start teaching myself? I suppose I can buy some books, and I plan to..but are there any tutorials or other reference material you would recommend? I like learning by example. When I was learning Excel, I had no clue how to implement a simple macro or what the heck a user form was. I learned everything just by reading the excel forum, seeing peoples code, playing around in excel trying to recreate things I saw. It taught me a lot.

I was thinking I could do the same here, but all the questions seem so complex and I don't understand them let alone the solutions.

No clue how to write a query or make a report. Total noob here!

Thanks for your help bro!

 

[VicRauch]

I would first like to say, MyBoo, GREAT JOB! There is no way to over emphanize normalization. I don't think I've ever heard it said better than, "If you don't learn this (normalization) first, you will be redoing your database in the very near future, or giving up totally."

That said, I would like to suggest to Big Monkey, that you look at all the data needs that you will have in this system, look over the rules of normalization, and make a first cut at normalizing your tables for this system. Then, give us a chance to help you with that normalizing by posting your data needs, and how you think you should create the tables for your data needs in this system. Believe me, this will be a great exercise for you, and you will get great help from this board. The best part of this exersize is you will really learn the rules of normalization and you will have the foundation for a system that you will not have to fight with.
HTH,

 

[Big Monkey]

Cool.

Thanks for feedback. I'm swamped at work this week, but I'll see if I can play around and try to create something this weekend and post back here so you guys can check it out.

 

[Andrew Fergus]

Hi

Following on from the previous suggestions, search this forum for 'normalisation' and 'normalization' - there are plenty of useful threads and links within this forum.

HTH, Andrew

e.g. http://www.datamodel.org/NormalizationRules.html

 

[MyBoo]

Thanks for the help guy. As a beginner, what advice can you give me to start teaching myself? I suppose I can buy some books, and I plan to..but are there any tutorials or other reference material you would recommend? I like learning by example. When I was learning Excel, I had no clue how to implement a simple macro or what the heck a user form was. I learned everything just by reading the excel forum, seeing peoples code, playing around in excel trying to recreate things I saw. It taught me a lot.

I was thinking I could do the same here, but all the questions seem so complex and I don't understand them let alone the solutions.

No clue how to write a query or make a report. Total noob here!

Two things I would suggest to EVERY beginner to intermediate user of Access... those above those two levels should already know this:

Learn how to use HELP in Access... it's one of the best tools available to you at all times! It contains examples of many of the things you can do within Access. It's a bit difficult at first, but over time, it should become your best friend. I'm currently using Access 2002 and find that when I'm in forms, queries or modules, the Help will present me with information based on the particular portion of Access that I'm currently using.... so, if you're in a form, using help and can't find an example of what your looking for, open the module section and search Help from there..... whatever you do USE THE HELP OPTION

The second suggestion is to create another Access database, seperate from what you're working on now... Call this TESTBED or something. When you run upon something that you can't work out, open this database and try different things... sometimes you'll figure it out and can then apply it to your application. Other times, you'll stay stumped... at that time, post it here or on your favorite forum. By testing your "stuff" in a seperate db, you avoid making some serious mistake that can crash your application. This isn't necessary, but it is prudent. (or make a copy of your application db first).

Aside from those two suggestions:

Open up the query section and teach yourself how to create a query.
Open up the macro section and teach yourself how to create macros.
What you teach yourself will stay with you for a long time.

Don't be afraid to ask for help... anyone who puts you down for asking questions is an ****... they were in your shoes at one time.

Put as much information as possible in your questions.. without writing a book (as I am).... if you say "I created an Access db and it doesn't work... can you help me"... then people WILL give you a hard time and this time around... it's deserved.

I too, taught myself everything I know about MS Access. I started out with v1.0.... a long time ago... it's changed a lot since then. Do I know everything? Absolutely not! I still ask questions all the time.

Hope this helps you out.

 

[VicRauch]

MyBoo,
EXCELLENT!!!!