Need Expert Help in Access - Excel

[JohnD]

I have a situation. In completing our Annual Report for our Accrediting body for my school, it requires us to complete a 'Completion and Placement Chart'. This chart was provided to us in Excel. The problem was that we needed a system to automaticaly update this chart as the year progressed so we can visually see our completion and placement rates. With this, I created an Excel sheet with the Completion and Placement Charts and made it so it can be updated as I entered a students information in.

Although it does calculate the data properly, the process is extremely ugly. It is terrible in appearance, function, and by all means, far from user friendly (I understand it, but it takes some explaining to do for someone else). This Workbook that I created houses almost all the information I need with the exception of it being entirely unsafe and far from being secure and data entry is a nightmare.

So, this brought me to Access. I created a database for this purpose (first time using access as well). It works like a dream in storing all the data that I need and keeping it safe. I needed to have a 'Placement Tracking Document' for each student outlining the students information for the accrediting body. This was done wonderfuly in Access and could not be accomplished through Excel (that I know of). However, I NEED to have Access tally all the information in the 'Completion and Placement Charts' like the Excel Workbook does. I need to keep the exact format of the 'Completion and Placement Chart' because it is a standard form in Excel and I can not steer away from it.

How can I get my DB in Access to comunicate with the 'Completion and Placement Charts' and have it automatically tally like my workbook???

I have attached some screen shots of my database and the entire Excel Workbook (Its edited because of confidential data) so you can understand a little of the chaos.

I rarely used Excel and never used Access until now. It took me nine months to create what I have because I was learning as I went. The books that I purchased to help me out do not touch this subject nearly enough (Access Cookbook, Access for Dummies and VB & VBA in a nutshell).

My point is, any help or direction in where to get thorough information on how to acomplish this is more then welcome. Any comments on what I have as well is more then welcome (negative or positive).

Thank you thank you thank you thank you in advance!

Oh, and use the buttons in the Excel Workbook to navigate.

John D

 

[JohnD]

Okay, I referenced this website:

http://support.microsoft.com/default.aspx?scid=kb;en-us;193039

and I understand the concept, but how is this tied into Access - it appears it is working in Excel only.

I apologize for the questions

John D

 

[JohnD]

This might help in understanding what I need...

Each student is one or the other:
1) Terminated
2) Terminated but employed in the field
3) Graduate employed in field
4) Graduate Furthered Education
5) Other Unavailable (Death, Medical, Prison and so on)
6) Graduate employed in unrelated field
7) Graduate Unemployed
8) Graduate Unknown

Once this is defined in access, I want it to add that person to the appropriate place in the Completion and Placement Chart that is located in Excel.

Pat, wouldnt Excel not accept the OLE code because once one record sends a student to the appropriate Cell, how would that cell know to tally (return sum) of all the students from access. Another words, say I have 5 grads employed in the field per Access. Wouldnt Excel only read 1 person in the appropriate cell or would it know to tally all 5 students thats being sent from access? I hope that makes sense

 

[Bat17]

Do you have to return the report as an excel file or print out?
If it's a print out then you could try to recreate the layout in an access report.

If you are only trying to make "CJS-ONL" type pages then I would look at getting rid of all the other stuff in the spreadsheet and start again using a single data table that you can import from Access. A bit drastic but probably the easiest way.
You can get Access to do a lot of the pre-processing for you to make it easier to build the SumIf's in you report by concatenating fields.

In fact I would look at having just one XL template that does just one page of the report and to create a separate book for each report.
You can always combine the after using Paste special>values to break links.

If you can post a sanitised version of your DB it would hep.

Peter

 

[JohnD]

If you are only trying to make "CJS-ONL" type pages then I would look at getting rid of all the other stuff in the spreadsheet and start again using a single data table that you can import from Access. A bit drastic but probably the easiest way.

Peter, I will be eliminating all worksheets except the Completion and Placement Charts (CPC) if I can get Access to update these only.

Do you have to return the report as an excel file or print out?

The form is submitted as a print out. The problem is the Accrediting body. They are very adament about using there form and last time I changed one of there forms they had a fit. It also would be a difficult form to reproduce. I attached a blank CPC.

If you can post a sanitised version of your DB it would hep.

Is there an easy way of eliminating all the confidential data with out having errors pop up all over the place? I have SSN, Criminal History, Names, Tel #'s and all kinds of things that need to be taken away..

I believe that if you populate these "tables" with the data from Access, they will be summarized on the other sheets. Look at the places where you are currently manually entering the data. That is where you want Access to add data.

The problem with this, is the next Annual Report will contain all new students and the amount per month will change entirely. If I have Access enter the data there, it WILL calculate the information and place it in the CPC. Im trying to get away from this because it takes an enormous amount of time to 'recreate' this spreadsheet (essentially what I woul be doing).

I will take a look at those links you gave me as well. Im sure I will learn something from them. Thank You again!

John D

 

[JohnD]

Pat, websites are really good, I think I can work off of what you provided. Thanks a million.

John