Excited to work with Access.. but hitting some bumps

[clarke]

Hello,

Before I start I want to say that I only have a little bit of access experience. I am no computer wiz, infact I am PC illiterate most of the time! I will be writing this post in easy english because this is how I can best describe my goals for the project and the issue that I have run into.

The goals for this project: The intention is to design a program that does two things. It must act as document managing software for the department's files, AND act as inventory managment so I can monitor item quantities and shipping info (which to me seem like two different projects). I will discuss the first part at this time.

First, I want the program to be able to keyword search through files that date back to the 70's, display a list of all matching results and the location of the results, so that the files can physically be pulled. This is simply a document managing function.

Where am I at now? All the files and folders the department has have been entered into an excel spreadsheet. The spreadsheet looks as follows:

File Location..... Document Name .........Keywords
xxxxxx............. xxxxxxx ................. xxxxx
yyyyyy ............. yyyyyyy ................. yyyyy

etc...

After spending time reading microsoft's tutorials, I imported this spreadsheet to Access and inserted a new comlumn to this list titled, FILE_ID which became the Primary Key. All good so far.

Why did I do this? For instance, if I type Product X 1993 in the search field, I would like Access to search through the KEYWORDS field in the excel spreadsheet and display all files with those keywords including the corresponding DOCUMENT NAME and FILE LOCATION. I will then physically find all the hard copies and decide which copy I need. All good so far.

Why did I add a primary key? OK, this is where things get confusing for me. While taking the tutorial courses on microsft, it was suggested that all vital information in a list be seperated to several lists for security reasons. Meaning, have a list that has FILE LOCATION linked to a list that has DOCUMENT NAME linked to a list that has KEYWORDS. The reason for this is that if I decide to delete PART of a record, it could delete the WHOLE record row. So my idea is that having several lists prevents this and adds flexibility in the future.

So I create more lists and create relationships. But they do not relate like I intended. Problems Begin! I will now display what my relationships look like and designate them with markings.

Note: *= Primary Key.
Note: f= foreign key
Note: File Location list is my original master list.
Note: R#= relationships

File Location............ Item Name ..........List Keyword List
File_ID(R1)*..............Item Name_ID(R2)*......Keyword_ID*
File Loc....................Item Name..................Keyword
Item Name................File_ID(R1) f Item.........Name_ID(R2) f
Keyword

Question: After I make the Item Name List (see above) I cannot add the 'Item Name' column or the File_ID column from the File Location List. And I certainly can't sink all three columns up. I wanted to link the File_ID's and automatically have the Item Name column pulled, but I cannot figure this out. If anyone could help me with a solution it will be greatly appreciated.

****** I also want to make sure there is even a point to doing this. Do my intentions make sense? Do I have the right train of thought? Any suggestions on the next step? Is there other guides that are tailored towards my goals?

I want to implement the ability to edit the lists in my program so that when I decide to edit let's say... a keyword, it edits in the Keyword list, and through the relationships ---cascades back to the File Location master list which is safe at the end of the chain.

Eventually I want to add a searchfield too. Anyone have ideas on this function?

I am also curious if someone has created or obtained something similar that could be used as a template. I downloaded the inventory template from microsoft but it isn't quite what I am looking for.

I am having a lot of fun learning about Access but am having trouble getting the ball rolling. Any and all help is most greatly appreciated. I thank you for taking the time to read this.

[RuralGuy]

Have you considered starting with that Inventory template as a base and modifying it to suit your needs?