Moving from Excel to Access

[Dinomike]

Hi All,

I have an Excel file with multiple worksheets using VLOOKUPS betweent he sheets and various formulas within the sheets, mainly basic arithmetic and also ROUNDUP.

I am wanting to transfer all this over to Access so that it can be edited by 2 computers at the same time and I need all the formulas to go with it. What's the easiest way to do this? I'm not a very experienced Access user and basically have no idea how to put in formulas in Access.

Any help would be greatly appreciated. I am happy to email to the excel file to anyone who wants to take a look.

Thanks,
Michael

 

[The_Doc_Man]

First, let me say that this is going to be an unsatisfying experience for you because Access and Excel are very different beasts. Transferring a flat-file layout (common to Excel) to Access will drive you nuts because Access is decidedly NOT a flat-file environment.

The lookups will not be hard because you can split those items into separate tables and use DLookup for almost the same purpose.

However, those forumulas have no place in an Access table. A formula can exist in a form or query or report but not in a table. So your raw data will always be exactly that - RAW.

As to the best way to go, don't post your spreadsheet. Talk to us. There is a method to my madness here... if you cannot tell us what you want in ENGLISH - or pick another language if you must - then we cannot tell you how to get there. The Old Programmer's Rule says "If you can't explain it to someone else, you don't know it well enough to do it yourself."

 

[Jakboi]

(Didnt see that DOC responded)

Well I was in your shoes not too long ago.

Basically, the first thing is going to be how your tables will be setup. Things will be group by tables, and items in tables will have some sort of relation to each other.

Search posts having to do with initial table setup or group, or better yet reply with what your excel sheet has or attach it, and I am sure some guru will help you get started.

Your formulas will be put in query's. This tells your tables what to do with the data. Then you must create forms for to have a interface of some sort.

Thats kinda basic but should help. I would attach your spreadsheet and see if someone can get you started.

It will take a while if you are new...took me 2 months to perfect my first database and it can be probably better...

The first thing is setting up your tables...

 

[Dinomike]

First, let me say that this is going to be an unsatisfying experience for you because Access and Excel are very different beasts. Transferring a flat-file layout (common to Excel) to Access will drive you nuts because Access is decidedly NOT a flat-file environment.

The lookups will not be hard because you can split those items into separate tables and use DLookup for almost the same purpose.

However, those forumulas have no place in an Access table. A formula can exist in a form or query or report but not in a table. So your raw data will always be exactly that - RAW.

As to the best way to go, don't post your spreadsheet. Talk to us. There is a method to my madness here... if you cannot tell us what you want in ENGLISH - or pick another language if you must - then we cannot tell you how to get there. The Old Programmer's Rule says "If you can't explain it to someone else, you don't know it well enough to do it yourself."

OK well I'm not really sure where to start so here goes...

The spreadsheet is for the scoring of a horse trials competition. I have a master sheet listing all the competitors and then individual sheets for each level, this is just for cosmetic purposes really, all input is done on the master sheet.

There are 3 phases... Dressage which has a score column and a placing column, Cross Country which has a Jump Penalties Column, a Time Penalties Column and a Total Column, and Showjumping which has the same as cross country. Obviously the Total column just adds up the Jump & Time Penalties for that phase. There is then an overall total column and overall placing column. The overall total is just the sum of the total for each phase.

There is then another sheet called times. This is where the cross country time penalties are calculated. The sheet holds the allowed time and I enter the competitor's start time and finish time and a column works out how much they were over or under the allowed time. If the competitor is over the allowed time, I translated the time they were over into seconds into the next column. The next column would then divide the seconds by 3, as this is how the time penalty points are calculated. The next column would round that figure up to the next whole number. This information is then posted into the Master sheet using a vlookup.

Each competitor has a number (ie 1-300), I guess this would be the primary key.

Any clues on how I should get it going?

 

[Jakboi]

This is guess but see what other people say:

Table 1 - tblcompetitors

Name
Horse
Etc..

Table 2 - tbldressage

Score
Placing or Rank or something

Table 3 - tblcrosscountry

Jump Penalties
Time Penalites

Table 4 - tblshowjumping

Jump Penalties
Time Penalties

Table 5 - tbltimes

Start Time
Finish Time

I think the rest can be done via query's. This is a guess as I am rather new as well but wanna see how close this would be. I would wait to see if you get other responses.

 

[KKilfoil]

A few issues for you to mull (over and above the critical ones about doing lots of planning and a little research into understanding of how relational databases such as Access works. The previous posters were certainly correct that you can't treat this like just another kind of speadsheet)...

Are you doing this as a one-off for a particular event, or is is a multiple-event tracking system? Do you want to track performance over multiple events?

Does each horse do its thing one-by-one, or do you have batches or heats, where relative performance within a heat is important?

.
.
.

Generally, think in terms of what things you have that are like other things, and what things are different, when designing your data structure. Probably 80% or more of all problems with access databases turn out to be due to bad structure. For example, your crosscountry and showjumping records COULD be stored in the same table, with an additional field to tell you which type of record this particular one is. Filtered queries can easily crunch your numbers for you later when you want to examine the results.

If you are serious about using databases, a little homework will go a long way.

 

[Dinomike]

A few issues for you to mull (over and above the critical ones about doing lots of planning and a little research into understanding of how relational databases such as Access works. The previous posters were certainly correct that you can't treat this like just another kind of speadsheet)...

Are you doing this as a one-off for a particular event, or is is a multiple-event tracking system? Do you want to track performance over multiple events?

Does each horse do its thing one-by-one, or do you have batches or heats, where relative performance within a heat is important?

.
.
.

Generally, think in terms of what things you have that are like other things, and what things are different, when designing your data structure. Probably 80% or more of all problems with access databases turn out to be due to bad structure. For example, your crosscountry and showjumping records COULD be stored in the same table, with an additional field to tell you which type of record this particular one is. Filtered queries can easily crunch your numbers for you later when you want to examine the results.

If you are serious about using databases, a little homework will go a long way.

Basically what I do now is I have a template spreadsheet with blank data, just the formulas, that I just save as a file for a particular event. Once the event is over the data isn't used again.

Each horse goes one-by-one.

Where would be a good place to start looking for info?

 

[The_Doc_Man]

Look within yourself first and foremost. DESIGN what you are going to do ahead of time. THINK about how you generate the results you get. Your Excel tables ARE a starting place but because they are forced (by the nature of the beast that is Excel) to be flat files or darned near it.

In ANY non-trival Access application, you must decide what entities are part of this thing that you are tracking. You see, sometimes it helps to think of Access as a MODELING tool. A BUSINESS or DATAFLOW modeling tool. So step number 1 is to think about what it is you are modeling. From the brief description you gave, I firmly believe this CAN be done in Access and you will like the result, perhaps. But you won't like getting there because Access is so new to you that you are going to face issues of wrapping your mind around the path as opposed to the goal. (It is normal, trust me. Many of us who came from Excel origins go through the mind-shift to Access.)

OK, look for things that can be grouped together logically. Try to decide who or what naturally goes together, what COULD go together, and what things are so different from each other that it makes absolutely no sense for them to be together. You are identifying model entities. Obvious one: Horse. Another obvious one: A competition event.

I'm going out on a limb here, but you might be able to do this with two tables and a little faith. In Access it is OK to be slightly wasteful. Not MASSIVELY wasteful...

I see two or three tables and a couple of special queries.

Table #1 - Competitors
This will hold data about the horse. Use any identification scheme you want as the primary key, including a manually entered OR automatically generated entry number. Search this forum for articles on primary keys to read much more than you wanted to know on the topic.

IF persons can enter more than one horse or a horse can have more than one owner, and if you WANTED to track this level of detail, another table for Owners would be appropriate. But this would be an ADJUNCT table and it might even be optional.

Table #2 - Competitions
Here you put each event's results. This table would identify the competitor through what we call a foreign key - essentially a pointer back to the entry in the first table. The record would contain more data than is appropriate for some events, but it is OK to have blanks now and then. I'm thinking like this:

tblCompetitions
CompID - ID number of the competitor being recorded.
EventName - C, D, or J (for cross-country, dressage, or jumping)
Possible data for each event follows...
Score - whatever score is appropriate for the event. You will compute this via some update queries.
Placement (Placing?)
Jump Penalties
Time - but could be blank for dressage
Time Penalties
Other contributors to scoring.

Now, normally you do not store a computed value, but here you might violate the rule very slightly. 'cause it's not really a rule, it's more of a guideline. You would have some update queries that you would use to compute scores based on the data in each competition record, but the queries would be very selective. Since you have the event type in the record, each query could select for a single event and update the score based on record contents.

Then a simple aggregate query could be used to show the final score.

Search Access help for the terms I have used such as "Aggregate Query" and "Update Query" and any other that you don't recognize.

One more thing, and you might hate me for this... but if you are truly a beginner, this is productive in the long run. Google-search the web for keywords "DATABASE NORMALIZATION" and look at a few article from .EDU sites you recognize. Have this information in mind when you start to identify your entities.