Selecting row sections from a table

[dmol]

I need to create some VBA code that will create a table for each employee containing their corresponding information. This information is retrieved from a section of rows from a table (that contains all employees data) where the row value for that particular field is the word “Employee”, and the section selection ends in a row under that same field but with the characters “% Completion”. See attachment.

So my first table will be for Employee Sam and it will go from row 1 to row 24. Second one will be for David from Row 25 to 39, etc…

[jzwp11]

Welcome to the forum!

First off, VBA code will not be much use; your database is not structured properly. If you have things that are related to an employee, they go in a separate but related table and linked back to the employee. It looks like you are tracking courses taken by the employee perhaps?

If an employee can take many courses, that describes a one-to-many relationship. If the same course can also be taken by other employees, you have a one-to-many relationship going the other way. Having 2 one-to-many relationships between the same two sets of data (employees and courses) describes a many-to-many relationship. That type of relationship is handled with a junction table.

tblEmployees
-pkEmpID primary key, autonumber
-txtEmployeeName

tblCourses
-pkCourseID primary key, autonumber
-txtCourseCode
-txtCourseName


tblEmployeeCourses (this is the junction table)
-pkEmpCourseID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkCourseID foreign key to tblCourses
-dteCourse (date the course was taken by the employee)


To get a better understanding of the rules that apply for relational databases like Access, you should check out this site on normalization.

[dmol]

You are right about my database not being structured properly and about the content of the database, and forgive me for not mentioning this before but this data is being imported from BusinessObjects from which I don't have much control over. So basically that's how it is imported into excel. I had to delete some fields just for security reasons, but I left some information so people can get an idea of what I'm talking about.

[jzwp11]

If it were me, I would go to my Business Objects people and work with them to see if you could get a file that is a little easier with which to work. If you have absolutely no alternative...

I'm not really sure that doing a VBA solution would be worth your time especially if the format of the data changes from one import to the next which might be the case if the number of rows between employees vary.

If I had no other alternative, I would probably clean up the Excel file as best as I could before bringing it into Access. From there, I would use append queries to get the data into the appropriate table structure (or use code once the file has been cleaned up to some "standard" format that you decide)