Query for Report

[missippi]

I have four table. The first table has three fields [planID] , [state] and [plan]. The next three tables are related to this one by [planID] field. What I want to do is a report where I get the information from the other tables where the [planID] matches in one report. I can do this for the first table and any one of the other three tables seperatly but nottogether. Is there away to do it?

Here are the three seperate queries:

--------------Query Program Contact-------------------
SELECT [State Plans].[planID] AS [State Plans_planID], [State Plans].[state] AS [State Plans_state], [State Plans].[plan], [Program Contact].[programcontactID], [Program Contact].[planID] AS [Program Contact_planID], [Program Contact].[name], [Program Contact].[title], [Program Contact]., [Program Contact].[phone], [Program Contact].[fax], [Program Contact].[address], [Program Contact].[city], [Program Contact].[state] AS [Program Contact_state], [Program Contact].[zip]
FROM [State Plans] INNER JOIN [Program Contact] ON [State Plans].[planID] =[Program Contact].[planID];
-----------Query Notes-----------------------
SELECT [State Plans].[planID] AS [State Plans_planID], [State Plans].[state], [State Plans].[plan], [Notes].[notesID], [Notes].[planID] AS Notes_planID, [Notes].[today's date], [Notes].[your name], [Notes].[note]
FROM [State Plans] INNER JOIN Notes ON [State Plans].[planID] =[Notes].[planID];
-------------Query State Contact---------------
SELECT [State Plans].[planID] AS [State Plans_planID], [State Plans].[state] AS [State Plans_state], [State Plans].[plan], [State Contacts].[statecontactID], [State Contacts].[planID] AS [State Contacts_planID], [State Contacts].[name], [State Contacts].[title], [State Contacts].[email], [State Contacts].[phone], [State Contacts].[fax], [State Contacts].[address], [State Contacts].[city], [State Contacts].[state] AS [State Contacts_state], [State Contacts].[zip]
FROM [State Plans] INNER JOIN [State Contacts] ON [State Plans].[planID] =[State Contacts].[planID];


My ideal Report:
State - Plan Name
State Contact info
Program Contact info
Notes on program

Thanks,

MAO

 

[grnzbra]

Why are you selecting so many fields if you only need 4?

 

[JoyInOK]

I don't see why you couldn't join all four tables. What error are you getting when you add all tables to the query design window and drag down the needed fields?

 

[missippi]

I only have 4 field in the main table I need to see all the information in my report.

I get nothing when I try to join all the tables. Some of the tables could have more then one contact for that state.
This is an example of what I am looking to get:

New York - New York Plan
State Contact
Fred Smith 2551 Lake Place NY NY 01200
Plan Contact
Fred Adams 21 Sea Ave NY NY 01203
Read Alot 11 Drive Way NY NY 01231
Notes
Dont reall have much to say
Contact Fred Adams first

Thanks,

MAO

 

[JoyInOK]

I will look at your queries above and see if I can find the problem with joining all the tables.

 

[JoyInOK]

Perhaps your tables were joined incorrectly. All of them should link to the one main table through plan ID. This query pulled all the records:
SELECT [State Plans].PlanID, [State Plans].state, [State Plans].plan, [Program Contact].name, [Program Contact].phone, [Program Contact].address, [Program Contact].state, [Program Contact].programcontactID, [Program Contact].title, [Program Contact].email, [Program Contact].fax, [Program Contact].city, [Program Contact].zip, [State Contacts].name, [State Contacts].title, [State Contacts].email, [State Contacts].phone, [State Contacts].statecontactID, [State Contacts].fax, [State Contacts].address, [State Contacts].city, [State Contacts].state, [State Contacts].zip, Notes.planID, Notes.[today's date], Notes.[your name], Notes.note
FROM (([State Plans] INNER JOIN Notes ON [State Plans].PlanID = Notes.planID) INNER JOIN [State Contacts] ON [State Plans].PlanID = [State Contacts].PlanID) INNER JOIN [Program Contact] ON [State Plans].PlanID = [Program Contact].PlanID;