New to Access -Way to add/update data one table with data from a different table?

[CSebak]

Greetings all! I could really use some guidance,

I've been learning Access for about a week (spending a couple of hours a day), and I am stuck on intermingling two tables together and updating/expanding the data in one table from the other. In my situation, I have two tables (table primary, and table secondary), table Secondary breaks down and adds to the detail of the information in table Primary.

IE, table Primary has a column titled 'FullCode', which consists of an alphanumeric entry (IE, 1C4D5P1)where the first number ('1' in the example) increments for every entry (1, 2, 3...999 etc.) and each alphanumeric pair (C4,D1,P4, etc.) refers to a state (Pennsylvania, etc), and the number of geographic features of the desired type (IE, Pennsylvania has 4 lakes of interest).

Table Secondary is mainly a break-down of the 'FullCode' column of table Primary; it has the 'FullCode' column (which takes the data exactly from table Primary), but it also has columns for each of the states I am looking at (Delaware, etc.) which accept the numeric value, (IE, C4 would add the value '4' to column 'California', in the same row as '1'), and do the same with the rest of the states.

I have tried messing with macros, forms, queries, and VBA coding (I have a Java background and thought it would be easy; while the logic is simple enough (I did the pseudo code in-between frustrating failed attempts), I cant figure out how to apply it to Access' environment in VBA! (I think I could export the data to a .txt file and write a java program to break it down in the desired way, then re-import the data, but I really want to keep everything within access).

Ideally, I would like to know a way where I could convert my current data into the second, more detailed table, and every time I entered more data into the primary table, access would update the secondary table automatically without me manually entering all of the data. Also, I would love if there is a way to list if an area is sterile of the desired feature (if an area only had the identifier number in the 'FullCode' and no alphanumeric pairs, another column 'sterile' would indicate the entry as being sterile).

So I guess my question is, can anyone (or several) of you empathetic and truly generous souls please guide me in any direction(s) that would help me learn



a) If any or all of these options are possible? (1,To have access break down an alphanumeric sequence into different sections; 2, to have access enter data from one table to another; 3, for access to update one table after data is input into another table; and 4, (I did not talk about this earlier), is it possible for VBA coding to pull data from, and input data into a table? and if so, do you know a good guide/tutorial in how to do this? (preferably, from a java standpoint, but I honestly don't expect that much))

b) What methods I could use to accomplish my goals? (Macros, queries, forms, VBA coding, etc.)

c) Can you direct me to an area where I can learn how the methods in b can be applied to my situation?

I have posted on enough of these types of forums when I was going through my Java growing-pains to feel the desire to let you know that I feel I have exhausted my other options before admitting defeat and posting on this forum, so please be nice and don't assume I am being too lazy to search for solutions myself (or whatever). I am a brand new access user and this wall is rather frustrating at this point and I could really use some guidance from those more knowledgeable.

Thank you,

-CSebak

[June7]

All of that is possible but what really needs to be addressed is the data structure.

Why do you have a field (FullCode) with compound data?

How long could that string possibly be?

How do you determine which state is correct for those letters that have more than one (Alabama, Alaska, Arizona, Arkansas)?

A column for each state? Not normalized but at least the number of columns is finite (until we acquire a new state - Puerto Rico?)

Get any introductory book to learn the basics of Access functionality then move on to programming.

Start with review of: http://office.microsoft.com/en-us/ac...010341717.aspx

This book might help: 'Access™ 2007 Programming by Example with VBA, XML, and ASP' by Julitta Korol, Wordware Publishing, Inc.

And don't forget Access Help utility.

[CSebak]

June 7, thank you for your reply.

I learn new systems in a very personalized way, I always have and I know what methods tend to work best for me. I am very project-oriented and hands-on, and by learning the functions I am interested when I am interested in them in a way that I think personally fits the way I learn, I learn a system in what appears to be a hap-hazard and potentially inefficient way, but there really is a method to my madness that has been tested over decades to have me tinkering away in all happiness in a surprisingly short amount of time.

This is not an actual database in that it is not meant to be a true, useful, comprehensive database; I created it for the purpose of learning the functions I mentioned earlier, but I will keep your data structure notations in mind when I actually do create real databases. Those are concerns I would not have known to watch out for without your input.

I think the using the compound data would make manual data entry easier for me if I could build the supporting structure in access. It would allow me to enter a single alphanumeric sequence and have it populate data from there, which is something I feel I would use when I finally do start creating real databases. (especially considering I use compound data for just about everything already, it would allow me to stay stuck in my ways)

I actually looked at the link you sent me before posting (I spent about 6 hours searching before posting), and I will see if I can find a copy of Access™ 2007 Programming by Example with VBA, XML, and ASP'. Thank you again.

If you can think of anyway to narrow my search for the functions I am interested in, I would be greatly appreciative if you could limit which sections of access to look into, (IE, I don't think a macro will be able to do the trick for any of the functions I am looking at [1,To have access break down compound data; 2, have access enter data from one table to another; 3, update a secondary table when the primary table receives new data], but actually 'knowing' where to look from someone who actually knows this stuff will save me hours of searching for something that may not even exist in the area I am looking at)

I spent the hours since writing the original post looking into VBA syntax and coding, but I just cannot find a way to take data from specific cells / input data into specific cells. If anyone could send me in the direction of where to look to learn how to do this, I think it would be very helpful.

Thank you again;

-CSebak

[June7]

Explore string manipulation functions.

Mid
Left
Right
InStr
InStrRev
Split

Explore UPDATE and INSERT queries.

Macros or VBA procedures can have code that will run queries.

[CSebak]

Thank you so much! This is exactly what I was looking for!

[June7]

And I forgot Len function in that list as well as Trim.

Learning along with practical application is good, I did that, but need a grounding in the basics of database principles, programming concepts, Access functionality, VBA and/or macros.