SQL - SELECT and INNER JOIN Question

B

Brad Clarke

Hi,

We have a database setup and working correctly with the following query,
that is used to populate a list-box.

Form_frmMenu.lstLoad.RowSource =
"SELECT tblLoad.LoadID,
tblLoad.LoadNo AS [Load Number],
tblLoad.Date,
tblTreatment.Treatment,
tblLocation.LocationName AS [Location Name]
FROM tblTreatment
INNER JOIN (tblLocation
INNER JOIN (tblLoad
INNER JOIN tblLnkLoadLocation
ON tblLoad.LoadID =
tblLnkLoadLocation.Load)
ON tblLocation.LocationID =
tblLnkLoadLocation.Location)
ON tblTreatment.TreatmentID = tblLoad.Treatment
WHERE (((tblLoad.Project)=[Forms]![frmMenu]![cboProject]))ORDER BY
tblLoad.LoadNo DESC;"

What we want to do is change the location of the field "Treatment" from the
existing tabel (tblLoad) to another existing table (tblMix). The field
"Treatment" still contains the same info, it is just more logical in our
case to include it in tblMix rather than tblLoad. I have tried just
changing the "tblLoad.Treatment" to "tblMix.Treatment", with the
corresponding changes to the underlying tables, however the query does not
return any information. I have a number of reports that access the same
tables, and their underlying queries have been changed and they work ok.

The tblTreatment.Treatment contains the actual text, whereas the
tblMix.Treatment (or tblLoad.Treatment) contains the Index number of the
item in tblTreatment.Treatment. tblTreatment contains 2 field with about 8
records, the first field being TreatmentID (the Index key) and the 2nd being
Treatment - the actual text. These are used in a drop-down box for the user
to select in a form.

Thanks

Brad Clarke
 
M

[MVP] S. Clark

Does the field Treatment exist in tblMix?
(Sorry to ask the obvious, but I don't know your table structure. I'll
assume by the fact that you don't get a parameter prompt, that it is.)

Be sure that ALL references get changed to tblMix. I counted only two, but
make sure both are done.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
B

Brad Clarke

Steve,

Yes, the field is included in the tblMix.

You mentioned changing 2 references. I know there is one (the
tblLoad.Treatment). What is the other?

Thanks

Brad

[MVP] S. Clark said:
Does the field Treatment exist in tblMix?
(Sorry to ask the obvious, but I don't know your table structure. I'll
assume by the fact that you don't get a parameter prompt, that it is.)

Be sure that ALL references get changed to tblMix. I counted only two, but
make sure both are done.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


Brad Clarke said:
Hi,

We have a database setup and working correctly with the following query,
that is used to populate a list-box.

Form_frmMenu.lstLoad.RowSource =
"SELECT tblLoad.LoadID,
tblLoad.LoadNo AS [Load Number],
tblLoad.Date,
tblTreatment.Treatment,
tblLocation.LocationName AS [Location Name]
FROM tblTreatment
INNER JOIN (tblLocation
INNER JOIN (tblLoad
INNER JOIN tblLnkLoadLocation
ON tblLoad.LoadID =
tblLnkLoadLocation.Load)
ON tblLocation.LocationID =
tblLnkLoadLocation.Location)
ON tblTreatment.TreatmentID = tblLoad.Treatment
WHERE (((tblLoad.Project)=[Forms]![frmMenu]![cboProject]))ORDER BY
tblLoad.LoadNo DESC;"

What we want to do is change the location of the field "Treatment" from the
existing tabel (tblLoad) to another existing table (tblMix). The field
"Treatment" still contains the same info, it is just more logical in our
case to include it in tblMix rather than tblLoad. I have tried just
changing the "tblLoad.Treatment" to "tblMix.Treatment", with the
corresponding changes to the underlying tables, however the query does not
return any information. I have a number of reports that access the same
tables, and their underlying queries have been changed and they work ok.

The tblTreatment.Treatment contains the actual text, whereas the
tblMix.Treatment (or tblLoad.Treatment) contains the Index number of the
item in tblTreatment.Treatment. tblTreatment contains 2 field with about 8
records, the first field being TreatmentID (the Index key) and the 2nd being
Treatment - the actual text. These are used in a drop-down box for the user
to select in a form.

Thanks

Brad Clarke
Click to expand...
Click to expand...
 
Top