Can export data to Access, now what?

[SilverSN95]

Hello,



I just finished linking and excel spreadsheet to an access database. I took the route of a custom macro for exporting which I developed the last couple days. The point of having the database was to have a central place for all of the "form" spreadsheets to submit their information for tabulation later.

The problem is, now that I have everything working with the entry/export, I am at a loss for how to manipulate the data the way I had intended. To be honest, I'm pretty new to Access and didn't give it much though until now. The problem is I need a kind of interactive form, similar to what an excel pivot table can do. For example, the user should be able to select certain data ranges to tabulate fields over etc.

In short:
It doesn't seem like access give much help as far as an 'interactive' report. Can I manipulate with vba to achieve this result without too much work?

Would it be better to just export the entire db back to excel, and do the work there? I realize access has a pivot view, but I need information from multiple tables in access, which I can't figure out how to do barring just joining all the data to one table.

Your advice is greatly appreciated. Thanks!

[TheShabz]

This where forms and queries come into play. It all depends on exactly what information you want inputted by the user and what information you want as your output. There are very few things that excel does that Access cant do just as well if not better. It also all depends on how your data is arranged in your Access tables. There are so many variables involved here. Give us a real life example of what you have, and what you want done, and let's see if we can help you out.

[SilverSN95]

Thanks for the reply. I guess some background information would also help. The excel spreadsheet I am using is basically a "skill profile" that an employee would fill out with their manager, giving numerical values (1-5) on a variety of skills.
By interactive report, I mean that the results from a large set of these should be able to be tabulated to see where training is needed and where skills are lacking/exceeding. Having a report on scoring averages for a certain time-frame and skill level chosen by whoever is looking at the time are things I have in mind.

I'll try to post an example database to help out, unfortunately I can't post the excel, there would be too much to modify, but I don't think that would really be necessary anyways.

[TheShabz]

This sounds like a relatively simple task. I would set up 3 tables.
tblEmployee:
EmpID (PK)
First Name
Last Name
etc

tblSkills:
SkillID (PK)
SkillName

tblEmployeeSkills
EmpSkillID (Autonumber, PK)
EmpID (FK)
SkillID (FK)
SkillLevel

the relationships would be as follows:
tblEmployee 1 --- M tblEmployeeSkill M --- 1 tblSkill

Your employee table would contain the Employee info. 1 record per employee.
Your Skill table would contain skill information. 1 record per skill.
Your EmployeeSkill table would have 1 record for each skill for each employee. So if an employee has 3 skills, you would have 3 records. You can import this all from your Excel if it is formatted well. If not, it could turn into a data entry nightmare depending on your number of employees and skills. Your queries would then be done off of this third table (junction table)

For example, to list all of the employees you have that have skills of less than a 3 rating,

SELECT *
FROM tblEmployeeSKill
WHERE SkillLevel <3

Hope this gets you started.

[SilverSN95]

Thats actually close to what I have now. An employee_Table, a Data table, and Skills table.

The way it works is each employee is rated on the same static number of skills (around 70) so thats not an issue. Again, tomorrow I'll try to post the access file, and give a better idea of what I've tried so far.
Thanks.

[SilverSN95]

It looks like the database is too large to attach, even after trimming down on the records. Is there any other way to post it here?