Access / Excel Help Required

[Ashley]

Hey folks!

 

I'm trying to make my life processes at work easier and hoping to move the way in which we handle dissertations away from a mish mash of badly managed spreadsheets and feedbacks on inconsistent word documents to a neater more standardised database created in Access.

 

Spent a while reading over normalisation and what not and I think I've done that correctly. I now have the following tables:

 

1) Students (ID, Surname, First name, Email)

2) Attendance (Attendance ID, Mode (i.e. full time, part time, deferred))

3) Programmes (Programme ID, Programme of study, module code)

4) Markers (Marker ID, Marker name)

 

There will be a fifth that contains the marks/comments but at the moment I'm trying to link these all up correctly.

 

I know if I was setting this up from scratch it would be easier but its not really plausible at this stage. However, how do I get the data to 'talk' to each other.

 

Basically is there an easy way for me to link it all up so data is automatically filled in. The comments sheet will contain the ID, Programme ID, Marker ID etc but how do they know what is what? For instance how can I tell it that student 95 is on programme 8 and has marker 20?

 

The original Excel spreadsheet I have contains all that data (each row has a student and info on all the data) but I'm trying to normalise this but I'm stumped on how to do this easily. I could go through each student and personally fill in the data but that seems ridiculous when it's already been filled in, but I essentially just want to replace commonly used terms with numbers.

 

I hope that makes sense as I think I've confused even myself...

[Will]

Sounds like you've got your database set up nicely.

 

If I were you I would get all the markers and programs put in the database so that they are allocated IDs, once that is done copy those tables into excel and use lookups to apply the right set of numbers for each students situation. This should be able to give you three columns at the end of your excel table with the ID, Programme ID and Marker ID (or whatever else you need) which you can just import straight into the database which will link everything up for you.

 

Hope that makes sense.

[Ashley]

It does make sense and I was trying that earlier but I seem to fail at Vlookup. I can figure it out logically but I just couldn't seem to be able to apply it. I was just being stupid.

 

The markers and programmes all have ids assigned to them already so it should be good to go. So just to check I need to:

 

1) Populate the programme and marker tables with their IDs

2) Copy those tables into Excel

3) Go back to the original spreadsheet that had the data and add a new column after the marker and programme names.

4) Use Vlookup to assign their relevant numbers

5) Copy that data back into Access.

 

Yes?

 

Also how do I copy long lists of data into Access? Again I could be being dense but it only seems to let me paste one at a time.

Edited December 4, 2011 by Ashley

[Will]

Those steps look good to me.

 

There is an Access Macro function called ImportExportSpreadsheet, create one of those referencing the table you have and it should sort it all out for you.

[Ashley]

Cheers. Will work on it tomorrow during actual office hours.

 

Much appreciated!

[Ashley]

How are you for forms and relationships? I want to create a form for data input. I have the following tables for reference:

 

Programmes Prog ID, Programme, Module Code

Markers Marker ID, Marker

Attendance Attendance ID, Mode of Attendance

 

The input will be limited in these fields to the options given (combo box). However, the form needs the student (or staff, whoever does it) to input the student's ID (which will be the primary key), Name, Email and Proposal.

 

The proposal will be fed into its own table (as its quite long) and then there will be a student table that will feature the following: Student ID, Name, Email, Attendance ID, Proposal ID, Programme ID (with the Marker ID being added later by the head of Postgraduate who decides them). The Attendance ID, Proposal ID and Programme ID should all be foreign keys.

 

However, I'm having trouble making this work. Primarily making the data captured from the right place (either input or selected from a list) and fed through to the right table. It throws up "different type" errors at me but I want students to be able to select programmes and attendance from a list of the actual titles, but we will see it as the foreign key number.

 

I could just be being stupid but it keeps stumping me. May buy a book on Access, or see if the department will pay