Unable to pull data from several tables 1

[germain2]

Remou
Thanks for the tip on saving the record and moving to a new page! It worked. I am now having problems pulling data from several forms into one query. My tables are set up as follows:
Main form/table
Linking table
Program tables linked to the linking form

All are linked by program ID.
If I run a query from the main form/table I am able to pull the data but not if I try to combine the main table with tables in the program files. Can you help me? Does this have anything to do with the master child fields? I also have those linked by Program ID. Thanks

 

[Remou]

It was PHV who helped you

Please post the SQL for the query.

 

[germain2]

This is the query that I ran:
SELECT tblAgency.PROGRAMID, tblAgency.AGENCYNAME, tblAgency.AGENCYREGION, subtblserved.Servednow, subtblserved.Licensecapacity, subtblserved.Receivetherapy, subtblserved.Internally, subtblserved.Referred, subtblserved.Contracted
FROM (tblAgencyProgrammes INNER JOIN subtblserved ON tblAgencyProgrammes.ProgramID = subtblserved.ProgramID) INNER JOIN tblAgency ON tblAgencyProgrammes.ProgramID = tblAgency.PROGRAMID;
Thanks for your help.

 

[Remou]

Data does not come from forms, it comes from tables. In what way is the query above not working? It seems fine on the surface.

 

[germain2]

Remou
The query runs fine and I see the variables in the datasheet view but the cells are blank so there is no data in the cells.
Thanks

 

[Remou]

Please post a small section of sample data from each of the three tables. Blank cells seem a little odd.

 

[germain2]

From the Main table:
PROGRAMID
235
387
402
705
SURVEYNO:
10
11
12
13
AGENCY NAME
ABJ Community Services Inc.(235)
ABJ Community Services Inc.(387)
Ada S. McKinley Community Services Inc.(402)
Ada S. McKinley Community Services Inc.(705)
STREET
235 Grove Ave
45 russell Ave
34 welcome ave
678 green ave
COUNTY
Cook
DuPage
burke
lasalle
From the subtable:
ProgramID
235
387
402
705
SurveyNo
10
11
12
13
Servednow
200
50
500
350
Servedannually
550
100
1000
500
Licensecapacity
600
150
1500
1000
Bear in mind that the table populate correctly when I run them individually but not when I join them. Thanks for your help!

 

[Remou]

I expected something a bit like this:

tblAgency
[tt]PROGRAMID AGENCYNAME AGENCYREGION
1[tab] The Agency[tab]55
2[tab] An Agency[tab]66[/tt]

tblAgencyProgrammes
[tt]ProgramID
1
2[/tt]

subtblserved
[tt]ProgramID Servednow Licensecapacity Receivetherapy Internally Referred
1[tab] 1[tab] 1[tab] 1 [tab] 1 [tab] 1 [tab] 1
2[tab] 2[tab] 2[tab] 2 [tab] 2 [tab] 2 [tab] 2[/tt]

That is, a little data from each of the three tables that you mention in the query.

 

[germain2]

Remou
I understand. My query is not pulling any data in the tblAgencyProgrammes table. I think that is where the problem lies. Any ideas on fixing it?
Thanks

 

[Remou]

That depends on the results that you want. You can play around with the joins, for example:

SELECT tblAgency.PROGRAMID, tblAgency.AGENCYNAME, tblAgency.AGENCYREGION, subtblserved.Servednow, subtblserved.Licensecapacity, subtblserved.Receivetherapy, subtblserved.Internally, subtblserved.Referred, subtblserved.Contracted
FROM (tblAgencyProgrammes RIGHT JOIN tblAgency ON tblAgencyProgrammes.ProgramID = tblAgency.PROGRAMID) LEFT JOIN subtblserved ON tblAgency.PROGRAMID = subtblserved.ProgramID

 

[germain2]

Remou
Thanks the SQl statement did populate the linking table but is there something I can do so that I can run the query directly without having to do the SQL myself, I am not very good at it.
Remember my main table - Agency is linked to the linking table by Program ID and all the other subtables are linked to the linking table by program ID also.
Any insights?
Thanks

 

[PHV]

Have a look here;

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886