Hi, 1) I have figured out my n

[Bob500]

Hi,

1)
I have figured out my necessary tables after lots of great help from people here.

An applicant can have many sites and a site has one form of a specific type

So I have:
-------------------------------
Table Applicants
applicantID (Primary Key)
applicant_name
applicant_address

Table Site
siteID (Primary Key)
applicantID (foreign key)
site name
site address

Table form
SiteID (foreign key)
misc. info

-------------------------

The form table is actually about 10 seperate tables of various data that occurs only once for each site, they are all referenced by the foreign key SiteID. is it ok not to have a primary key in these?

2)
Also I believe to make a foreign key in access you just create a field with the same name and data type as the PK and then link them in relationships. Is this correct?

3)
Any ideas where I can find tutorials on how to create queries to search and list specific criteria?

Many thanks again!

 

[Bob500]

Actually thinking about it, it there any point in splitting all the form tables, what benefit is there from doing this?

 

[willir]

Bob500

First the From table
You are correct; it does make sense to split out the form table for each site. One table should meet you needs. What is missing is a unique identifier for forms, say FormID.

And the question is does an applicant have anything to do with a form?? Or is the form just a document that the applicant fills out? And will you need to track the information filled out by the applicant?


Next linking the Foreign key
I have seen people link fields several different ways.

The most common way I have seen is to use a combo box in a form -- Although this works, I disagree with using this approach.

Access provides a "Relatioships" design tool. You can set your design and (recommended) enforce referential integrity. The graphical interface is cool. Click on the "many" side of the relation, and drag it to the "one" side. If you enforce referential integrity, you should see an infinity sign appear on the "many" site.

One tool which I do not see used very often, but I find useful is in the table design view. At the bottom of the design view, you assign specific properties for a field -- number -- what type of number, text -- how many character.

There is also a "LookUp" tab which defaults to text box. I find it very useful to change this to a combo or list box and link the foreign key at this level. This way, when I create my form, the combo box is automatically created instead of a text box.

In your example, you have a one-to-many, where one applicant can be at different sites. On the site table, for the applicant ID, you can have the combo box point to the Applicant lastname (sort), firtname (sort) and applicant ID, and bind the third column, applicant ID.


As for books to read.
Check out a respectful book store and preview. For myself, I found that I first needed a "hands-on" book. This book was very helpful in getting me started, but I outgrew it as my skills developed. Find a book that is easy for you to understand, and be ready to buy a new one when you advance to the next level.

You will eventually need a reference book -- Access Bible type of thing. Microsoft's documentation is pretty good. My current favourite is "Access Cookbook" (publisher: O'Reilly. Authors: Getz, Litwin, Baron, ISBD: 0596000847)

Oh yea, when buying books, make sure it supports the version of Access you use.

Richard