AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
QUESTION: Please help me connect the tables so i can use the forms to enter data to run queries and reports. Thank you again for taking the time!
Please email me for a screen shot. I was not able to turn it into a jpg :(
ANSWER: First, you do NOT need to connect the tables (using the Relationships Windows) to use forms, queries and reports.
Forms should be bound to a single table or a single table query. If you need to enter data into a related table on the same form, use a subform. The Subform wizard will allow you to connect the tables and offer suggestions as to the best connections.
For queries, you make the connections by dragging the parent field onto the childe field in the related table. Generally the parent is the Primary key of one table and the child is the Foreign Key in the related table. I try to name my PKs and FKs using the same name, this helps Access find the relations automatically.
Reports should be based on queries so the queries already have the connections.
Finally, Windows Paint will make a JPG. Just paste the Screen shot into Windows Paint and save as a JPG.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: I cannot have multiple forms related to multiple tables?
1 form per table connect to one table? I'm sorry I have been having a heck of a time with this. IMAGE: tables
ANSWER: First, the screen shot shows your tables have relationships set. Unfortunately the screen shot is very blurry and I can't read it enough to judge if they are correct or not.
Second, you CAN have multiple forms and each form can be related to different tables. But each form should have, as its recordsource either a single table or a single table query. If you try using a multiple table query, the query may not be updatable. You get around this restriction by using subforms.
For example, it looks like you have a Caller table and a Therapy table. So that each caller may have multiple diagnosis. So you would create a main form bound to the Caller table and a subform bound to the Therapy table. The form would then show all therapy records for the caller and allow you to enter new ones in the subform.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Thanks again, Scott. Is this screen shot clearer?
You are correct.
One caller can ask many questions re: many claims and speak to many pharmacists.
one pharmacist can speak to many callers, answer many questions and have many claimants.
My auto number fields seem useless...I'm just not connecting the dots. Do you have an idea of a better field to use as a primary and a forein key? The Claims table has a foreign key in the Caller table.
Answer Sorry that is not much clearer, but your additional info helps.
The three tables that are at issue here are Callers, Claims and Pharmacists, correct? A caller can submit many claims and a pharmacist can deal with many claims. But can more than one pharmacist deal with a claim? If its one pharmacist per claim, then your relationships should be like this:
A claim can have one caller and one pharmacist. So the PK of the Caller and Pharmacist tables are linked to their respecitve FKs. I would envision at least two forms, one bound to the Caller table and the other bound to the Pharmacist table. Each form would use the SAME subform, but the linking would be the the respective key on the main form. In this way you can show all claims for a caller or all claims for a pharmacist.
I recommend that you use Autonumbers as your PKs. Your FKs should ne Number/Long Integer datatypes.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
)
){kind=link}