Query Madness

[dan.cawthorne]

Hello,

For the Love of money i cant understand why my query wont Show my
related information which is in another table.

what im trying to do is from a particular table "tbl_ProjectAnalysis"
Under the primary key field "ProjectQNo" is select a project, this
field has a lookup query statement which looks up from table
"tbl_Projects" Field "ProjectQNo and Field "ProjectTitle" but the
bound column is "ProjectQNo"

the 2 Tables are have a relationship as a 1 to 1 between the fields
"ProjectQno" and "ProjectQno"

I created an query so i could use this as an input and which in this
select query i have all the fields from "tbl_ProjectAnalysis" and then
from "tbl_Projects" fields "ProjectTitle", "Address", "Town", "County"
and "PostCode"

So what should happen is when im on my Wizard form ive created which
the data source is this query, when i Select a project from the
ProjectQNo Field (Drop Down) The Project Address and Title should
automatically fill in.

Why is not?

 

[John W. Vinson]

Hello,

For the Love of money i cant understand why my query wont Show my
related information which is in another table.

what im trying to do is from a particular table "tbl_ProjectAnalysis"
Under the primary key field "ProjectQNo" is select a project, this
field has a lookup query statement which looks up from table
"tbl_Projects" Field "ProjectQNo and Field "ProjectTitle" but the
bound column is "ProjectQNo"

I suspect you're yet another victim of the misdesigned, misleading,
infuriating Lookup Field misfeature. See
http://www.mvps.org/access/lookupfields.htm for a discussion. At the root of
it, ProjectTitle *APPEARS* to be in your table but it isn't.

the 2 Tables are have a relationship as a 1 to 1 between the fields
"ProjectQno" and "ProjectQno"

One to one relationships are quite uncommon. Ordinarily you can just use more
fields in the master table rather than having a second table with only one
record per main table record! Is it in fact the case that a given project will
have either zero or one, *NEVER MORE*, tbl_ProjectAnalysis records?

I created an query so i could use this as an input and which in this
select query i have all the fields from "tbl_ProjectAnalysis" and then
from "tbl_Projects" fields "ProjectTitle", "Address", "Town", "County"
and "PostCode"

So what should happen is when im on my Wizard form ive created which
the data source is this query, when i Select a project from the
ProjectQNo Field (Drop Down) The Project Address and Title should
automatically fill in.

Please open the query in SQL view and post it here; doublecheck that it's the
Recordsource of your form. The address and title will NOT automagically fill
in if you're creating a new project.

John W. Vinson [MVP]

 

[dan.cawthorne]

I suspect you're yet another victim of the misdesigned, misleading,
infuriating Lookup Field misfeature. Seehttp://www.mvps.org/access/lookupfields.htmfor a discussion. At the root of
it, ProjectTitle *APPEARS* to be in your table but it isn't.


One to one relationships are quite uncommon. Ordinarily you can just use more
fields in the master table rather than having a second table with only one
record per main table record! Is it in fact the case that a given project will
have either zero or one, *NEVER MORE*, tbl_ProjectAnalysis records?



Please open the query in SQL view and post it here; doublecheck that it's the
Recordsource of your form. The address and title will NOT automagically fill
in if you're creating a new project.

John W. Vinson [MVP]

Thanks for getting back intouch with me john

one solution would be to have the Master table to include the fields,
but that would be the table having about another 15 fields.

and your correct. this table will only have one or none records,

the reason why i created it this way, is there is a dedicated person
for creating new projects, then only the Commercial Director Fills in
the Project Analysis.by doing it this way, i can only limit that
person to creating new Project analysis, unless theres a better better
way i can achieve this.

the SQL of the Query is as follows

SELECT tbl_Project_Analysis.ProjectQNo, tbl_Project_Analysis.[Budget
Value], tbl_Project_Analysis.[Fixed Price Details],
tbl_Project_Analysis.[Sub-Contract Type], tbl_Project_Analysis.
[Payment Terms], tbl_Project_Analysis.[Warranty / Bond Requirment],
tbl_Project_Analysis.[Public Liabilty], tbl_Project_Analysis.
[Employers Liabilty], tbl_Project_Analysis.[Professional Liabilty],
tbl_Project_Analysis.[Notable Contract Requirments],
tbl_Project_Analysis.[Analysis Notes], tbl_Project_Analysis.[Project
Risk Rating], tbl_Project_Analysis.[Created By],
tbl_Projects.ProjectTitle, tbl_Projects.Address1,
tbl_Projects.Address2, tbl_Projects.Address3, tbl_Projects.Town,
tbl_Projects.County, tbl_Projects.Region, tbl_Projects.PostalCode
FROM tbl_Projects INNER JOIN tbl_Project_Analysis ON
tbl_Projects.ProjectQNo = tbl_Project_Analysis.ProjectQNo;

Rather Big Big SQL,

Did Notice Once a Record in the Project_Analysis table has been
Created for a Project when i have view the query in dataview mode is
does show Project Title and Address.

 

[John W. Vinson]

SELECT tbl_Project_Analysis.ProjectQNo, tbl_Project_Analysis.[Budget
Value], tbl_Project_Analysis.[Fixed Price Details],
tbl_Project_Analysis.[Sub-Contract Type], tbl_Project_Analysis.
[Payment Terms], tbl_Project_Analysis.[Warranty / Bond Requirment],
tbl_Project_Analysis.[Public Liabilty], tbl_Project_Analysis.
[Employers Liabilty], tbl_Project_Analysis.[Professional Liabilty],
tbl_Project_Analysis.[Notable Contract Requirments],
tbl_Project_Analysis.[Analysis Notes], tbl_Project_Analysis.[Project
Risk Rating], tbl_Project_Analysis.[Created By],
tbl_Projects.ProjectTitle, tbl_Projects.Address1,
tbl_Projects.Address2, tbl_Projects.Address3, tbl_Projects.Town,
tbl_Projects.County, tbl_Projects.Region, tbl_Projects.PostalCode
FROM tbl_Projects INNER JOIN tbl_Project_Analysis ON
tbl_Projects.ProjectQNo = tbl_Project_Analysis.ProjectQNo;

Rather Big Big SQL,

Did Notice Once a Record in the Project_Analysis table has been
Created for a Project when i have view the query in dataview mode is
does show Project Title and Address.

Well... exactly. That's just what the query would do. You have an INNER JOIN
which means that you are finding all records in tbl_Projects and *matching
records in tbl_Project_Analysis*. If there is no matching record, you will see
no records.

Two suggestions: change the INNER JOIN to LEFT JOIN ("show all records in
tbl_Project and matching records in tbl_Project_Analysis" if you rightclick
the join line in the query designer and view its properties), and include both
tables' ProjectQNo fields; Access will do "row fixup" and fill in the
ProjectQNo when you enter data into an analysis table field.

Or, use a Form based on tbl_Project with a subform based on
tbl_Project_Analysis using ProjectQNo as the master/child link field.

I'd just use one table, myself. NULLs take up no room. The only case I could
make for the one to one relationship is if the vast majority of projects have
no analysis data.

John W. Vinson [MVP]

 

[dan.cawthorne]

SELECT tbl_Project_Analysis.ProjectQNo, tbl_Project_Analysis.[Budget
Value], tbl_Project_Analysis.[Fixed Price Details],
tbl_Project_Analysis.[Sub-Contract Type], tbl_Project_Analysis.
[Payment Terms], tbl_Project_Analysis.[Warranty / Bond Requirment],
tbl_Project_Analysis.[Public Liabilty], tbl_Project_Analysis.
[Employers Liabilty], tbl_Project_Analysis.[Professional Liabilty],
tbl_Project_Analysis.[Notable Contract Requirments],
tbl_Project_Analysis.[Analysis Notes], tbl_Project_Analysis.[Project
Risk Rating], tbl_Project_Analysis.[Created By],
tbl_Projects.ProjectTitle, tbl_Projects.Address1,
tbl_Projects.Address2, tbl_Projects.Address3, tbl_Projects.Town,
tbl_Projects.County, tbl_Projects.Region, tbl_Projects.PostalCode
FROM tbl_Projects INNER JOIN tbl_Project_Analysis ON
tbl_Projects.ProjectQNo = tbl_Project_Analysis.ProjectQNo;

Rather Big Big SQL,

Did Notice Once a Record in the Project_Analysis table has been
Created for a Project when i have view the query in dataview mode is
does show Project Title and Address.

Well... exactly. That's just what the query would do. You have an INNER JOIN
which means that you are finding all records in tbl_Projects and *matching
records in tbl_Project_Analysis*. If there is no matching record, you will see
no records.

Two suggestions: change the INNER JOIN to LEFT JOIN ("show all records in
tbl_Project and matching records in tbl_Project_Analysis" if you rightclick
the join line in the query designer and view its properties), and include both
tables' ProjectQNo fields; Access will do "row fixup" and fill in the
ProjectQNo when you enter data into an analysis table field.

Or, use a Form based on tbl_Project with a subform based on
tbl_Project_Analysis using ProjectQNo as the master/child link field.

I'd just use one table, myself. NULLs take up no room. The only case I could
make for the one to one relationship is if the vast majority of projects have
no analysis data.

John W. Vinson [MVP]

Thanks,

I think the easier option is just to have a Master and Child Links,
thanks for your time any way Have a good weekend