Xcel to Access

[alcorjr]

Hi guys, although I'm quite adept at Excel, I'm just starting to learn Access & VB6.
As an exercise I want to convert the data I have in a spreadsheet into Access Xp. This sheet contains personal data on employees, as well as their two-week salaries for a 3-year period.
As I'm doing this, I realize that the field names are strings. In the case of the salaries, the field name would be the starting and ending date of each period in question.
In Excel I had this info in two separate cells (The first two of the column), so I could use the dates as pointers for subsequent calculations.
How can achieve this in Access, so when I try to write code in VB it will recognize the field names as two dates?
Thanks

 

[Limbofrog]

I don't know if I fully understand what you are doing or trying to do but...

In Access, you might create a form with a bunch of fields on it. In your case, you may have [date1] and [date2]. [date2] might have an event that sets it equal to ([date1] + 14) thus making it two weeks past [week1]

This is all up to you and how you want to use it on a day-to-day basis... or week-to-week basis. Anyways - when you create a field called [date1] for example and its in a "Form" called [EmployeeSal].

You can refer to this field from anywhere in the database by saying
[Forms]![EmployeeSal].[week1]

If your using/writing a macro, you may have to first open the form in question before refering to the field in this way.

I am still learning myself so I am sure there are many ways to refer to a field.

 

[mdmilner]

Just as a how to get started - What I'd do is go ahead and import the data as-is. Make sure you don't lose anything and force fields like your from/to date field to import as text.

Once you get it into Access, that's where the manipulation begins.
Given a consistent pattern (perhaps a hyphen between the dates?) you can use functions like 'InStr' to match a string and return a character position while using 'len' (length of string) to identify the total character length.

A combination of the two would allow you to identify characters in each portion which you can run as a query. For example:

SELECT Left(fld1,InStr(fld1,"-")-1) As dteBegin, Mid(fld1,InStr(fld1,"-")+1,len(fld1) - InStr(fld1,"-")) As dteEnd FROM tblName

You can look for just about anything you'd like with the InStr function, including multiple characters (it returns the position of the first character in the matching string).

After working up the basic select query, you can convert this to an UPDATE query to update fields that you might add to the table.

Not sure you really need to do this, of course, since you said you already had the dates in separate fields.

Really, this is just a beginning and doesn't discuss how to design a relational database; the main advantages of which is speed.

Mike

 

[alcorjr]

Md, thanks for your quick reply. I understand what you say, and even understand your code although I'm quite far from being able to program in Access in the same way that I do in Excel.

Speaking of which, I'm still not sure if building a database in Access (of the characteristics I mentioned in the post), would allow me the ease of operation and ability to manipulate data as Excel does.

Another question, could you point me to a good tutorial for Access Xp ?
I've reviewed some of the links already posted in the forum, and to my neophite eyes they appear quite dry to digest...


Could you please advise on this ?

Cheers :wink:

 

[mdmilner]

There are a couple of the sites that I first found when looking for help.
I'm not saying they're the best that were ever setup - just that they helped me considerably.

http://www.mvps.org/access/

http://www.fabalou.com/

I also spent a little time on usenet.

My experience with the differences between Excel & Access is this. Access frequently requires more technical knowledge to do the same thing. Although, I personally find it easier to use to do things like form building.

Mike