Access beginner in dire need of rescuing (1 Viewer)

[BigAL]

Hi all,

I'm sure this is really simple but I've just picked up a copy of Access 2010, am completely out of my depth and I really don't know where to start.

Background: I have managed to move a table I was maintaining in Excel to Access, but would still like to use Excel as the front end interface for my users. The table is currently growing down and wide (wide being my problem). Let's say the table looks something like this.

Person     Year 1   Year 2   Year 3
Person1      a        b        c
Person2      x        y        z

I am regularly tasked with fetching data for a new Person, so I assemble the row in Excel and I have now managed to cobble together some basic VBA code that then sends it off to Access (INSERT INTO tblTest etc.), and then later rerieves what I want (SELECT * FROM tblTest WHERE etc.) using ADO (great!).

However, every year I also get sent an Excel column of every person's data and I have no idea how to handle this (year end is fast approaching ). Ideally, I'd just add a new field in Access and copy/paste the new data in but I don't think I'm allowed to do that, am I?

Is there a simple way to handle this? I am right in thinking it is bad practice to have tables growing in two directions? I have heard of splitting tables and database normalisation but neither know how to do the first, nor understand the second...sorry (though I am willing to learn).

Any help on how best to handle this would be greatly appreciated.

Many thanks,

(p.s. let's say this is a 15 year project with many 1,000s of persons)

 

[Alansidman]

Here is a white paper on data normalization and relational databases that will help you to understand how to structure your db.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

You would be better served to have your table in Access set up similar to this.

YourTable
-----------
RecordID autonumber (PK)
Person ID number (FK)
Year
Data

PersonTable
---------------
PersonID autonumber (PK)
PersonData (ie. employee number, address, phone, etc.)

 

[BigAL]

Hi Alan,

Thank you for your response. It will take me some time to go through all this, but the table structure you suggested looks very promising.

Alex

 

[jdraw]

Alex,

see my post #2 at this site for some videos related to database design etc.
http://www.access-programmers.co.uk/forums/showthread.php?p=1212452#post1212452


Good luck with your project.

 

[BigAL]

Hi Alan/jdraw,

This is really great stuff so thanks for your contributions. I've split up the tables as per Alan's suggestion and can already see that, come year end, I can loop through tblYearlyData and update records with:

INSERT INTO tblYearlyData [Column1], [Column2], [Column3]
VALUES ('abc', 'def', 'ghi')   etc.

What's not clear to me, however, is how I would add a new record in this proposed format. Would I have to first add the person to tblPerson and then loop through tblYearlyData or is there an easier way to do it? I see that I can't add to tblYearlyData without first adding to tblPerson which makes some sense.

I'm also not sure how to modify my code if I want to return all the data for, say, Sam/Tom/Bob. Previously, I just had something like:

SELECT * FROM tblBlahBlahBlah
WHERE [Person] IN ('Sam', 'Tom', 'Bob')

Would you be able to offer any indication as to how to do that?

Thank you very much,

Alex

(ps - I'm too new to post links but here's my progress
http_____://i50.tinypic.com/25p07tf.jpg
without those underscores)

 

[Alansidman]

I think that your SQL statement to return all data for Sam, Tom and Bob would remain the same. An alternative but would be to change your WHERE statement to

WHERE [Person] = "Sam" or "Tom" or "Bob"

but that is really the same as what you have just another presentation.

You will have to add the person to the Person Table before entering data for them.

 

[Severin]

Just a comment...

This is an excellent opportunity to show the difference between data in Excel and data in Access...

This is how you would use the data in Excel:

Person     Year 1   Year 2   Year 3
Person1      a        b       c
Person2      x        y       z

This is how you would do it in Access:

Person      Year     Value
Person1    Year1      a
Person1    Year2      b
Person1    Year3      c
Person2    Year1      x
Person2    Year2      y
Person2    Year3      z