query problem

[bigwillno2 via AccessMonster.com]

Either i have a problem with this query or i am the problem. i have query
that combines data from two tables.
but somehow, when i try to add another column to the query, i am not getting
anything. even though there is not data associated with the new column,
access is suppose to display the rest of the data for the other columns and
it's not doing it every time i add a new column to this. any suggestion will
be appreciated. thanks

 

[Rick Brandt]

Either i have a problem with this query or i am the problem. i have
query that combines data from two tables.
but somehow, when i try to add another column to the query, i am not
getting anything. even though there is not data associated with the
new column, access is suppose to display the rest of the data for the
other columns and it's not doing it every time i add a new column to
this. any suggestion will be appreciated. thanks

Are you applying criteria to the new column?

Post the SQL.

 

[bigwillno2 via AccessMonster.com]

no criteria applied .....here is my SQL....the new added colums are FoamCore,
FoamEnc, BoxType, BoxProfile.......and i dont seem to know why it's not
giving me a corrent result.

SELECT MSM.ModelNumber, MSM.Desc1, MSM.Desc2, Spring.Spring, Label.Label,
Size.Size, Needle.Needle, Pattern.Pattern, Config.Config, Border.Border, MSM.
Produce, Concatenate("SELECT Qfill FROM qrQfills WHERE ModelNumber = """ &
[MSM].[ModelNumber] & """") AS Qfill, Concatenate("SELECT Bfill FROM qrBfill
WHERE ModelNumber = """ & [MSM].[ModelNumber] & """") AS Bfill, Concatenate
("SELECT UphFill FROM qrUphfills WHERE ModelNumber = """ & [MSM].[ModelNumber]
& """") AS Uphfill, Concatenate("SELECT PtFill FROM qrPtfills WHERE
ModelNumber =""" & [MSM].[ModelNumber] & """") AS Ptfill, FoamCore.FoamCore,
FoamEnc.FoanEnc, BoxType.BoxType, BoxProfile.BoxProfile
FROM BoxType INNER JOIN (BoxProfile INNER JOIN (FoamEnc INNER JOIN (FoamCore
INNER JOIN ([Size] INNER JOIN (Pattern INNER JOIN (Needle INNER JOIN (Label
INNER JOIN (Config INNER JOIN (Border INNER JOIN (Spring INNER JOIN MSM ON
Spring.SpringID = MSM.SpringID) ON Border.BorderID = MSM.BorderID) ON Config.
ConfigID = MSM.ConfigID) ON Label.LabelID = MSM.LabelID) ON Needle.NeedleID =
MSM.NeedleID) ON Pattern.PatternID = MSM.PatternID) ON Size.SizeID = MSM.
SizeID) ON FoamCore.FoamCoreID = MSM.FoamCoreID) ON FoamEnc.FoamEncID = MSM.
FoamEncID) ON BoxProfile.BoxProfileID = MSM.BoxfileID) ON BoxType.BoxTypeID =
MSM.BoxtypeID
WITH OWNERACCESS OPTION;

Either i have a problem with this query or i am the problem. i have
query that combines data from two tables.

[quoted text clipped - 3 lines]

other columns and it's not doing it every time i add a new column to
this. any suggestion will be appreciated. thanks

Are you applying criteria to the new column?

Post the SQL.

 

[Rick Brandt]

no criteria applied .....here is my SQL....the new added colums are
FoamCore, FoamEnc, BoxType, BoxProfile.......and i dont seem to know
why it's not giving me a corrent result.

SELECT MSM.ModelNumber, MSM.Desc1, MSM.Desc2, Spring.Spring,
Label.Label, Size.Size, Needle.Needle, Pattern.Pattern,
Config.Config, Border.Border, MSM. Produce, Concatenate("SELECT Qfill
FROM qrQfills WHERE ModelNumber = """ & [MSM].[ModelNumber] & """")
AS Qfill, Concatenate("SELECT Bfill FROM qrBfill WHERE ModelNumber =
""" & [MSM].[ModelNumber] & """") AS Bfill, Concatenate ("SELECT
UphFill FROM qrUphfills WHERE ModelNumber = """ & [MSM].[ModelNumber]
& """") AS Uphfill, Concatenate("SELECT PtFill FROM qrPtfills WHERE
ModelNumber =""" & [MSM].[ModelNumber] & """") AS Ptfill,
FoamCore.FoamCore, FoamEnc.FoanEnc, BoxType.BoxType,
BoxProfile.BoxProfile
FROM BoxType INNER JOIN (BoxProfile INNER JOIN (FoamEnc INNER JOIN
(FoamCore INNER JOIN ([Size] INNER JOIN (Pattern INNER JOIN (Needle
INNER JOIN (Label INNER JOIN (Config INNER JOIN (Border INNER JOIN
(Spring INNER JOIN MSM ON Spring.SpringID = MSM.SpringID) ON
Border.BorderID = MSM.BorderID) ON Config. ConfigID = MSM.ConfigID)
ON Label.LabelID = MSM.LabelID) ON Needle.NeedleID = MSM.NeedleID) ON
Pattern.PatternID = MSM.PatternID) ON Size.SizeID = MSM. SizeID) ON
FoamCore.FoamCoreID = MSM.FoamCoreID) ON FoamEnc.FoamEncID = MSM.
FoamEncID) ON BoxProfile.BoxProfileID = MSM.BoxfileID) ON
BoxType.BoxTypeID = MSM.BoxtypeID
WITH OWNERACCESS OPTION;

The syntax you are using...

FoamCore.FoamCore, FoamEnc.FoanEnc, BoxType.BoxType, BoxProfile.BoxProfile

....is one that implies TableName.FieldName. You can't just make these up
and add them to a query. What purpose do you want them to serve?

Also what is Concatenate("SELECT ...")? I see no reference to any such
function in Access help. Is that a user-defined function?

 

[bigwillno2 via AccessMonster.com]

Hi Rick;
let me try to explain each step the way to this query........now, let me also
say that the query works perfectly without the new columns only. in the
query design i have table MSM that has fields: ModelNumber, Desc, SpringID,
BorderID, ConfigID, SizeID, FoamCoreID, FoamEncID, BoxTypeID....etc.....the
other tables in the design of the query are Spring that's related to MSM by
SpringID, Border related to MSM by BorderID AND SO ON. essentially, the query
will be displaying
example.
ModelNumber| Desc |Spring, |Border, |Config, |Size, |FoamCore,
|FoamEnc
m1001 some desc sp10 b11 single 33 f345
fe101

Concatinate stuff is the adding of items in the same line.
this is created because the user selects of the same item multiple times on a
form and i used the following module to do this.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String


'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection,
'adOpenKeyset , adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

no criteria applied .....here is my SQL....the new added colums are
FoamCore, FoamEnc, BoxType, BoxProfile.......and i dont seem to know

[quoted text clipped - 22 lines]

BoxType.BoxTypeID = MSM.BoxtypeID
WITH OWNERACCESS OPTION;

The syntax you are using...

FoamCore.FoamCore, FoamEnc.FoanEnc, BoxType.BoxType, BoxProfile.BoxProfile

...is one that implies TableName.FieldName. You can't just make these up
and add them to a query. What purpose do you want them to serve?

Also what is Concatenate("SELECT ...")? I see no reference to any such
function in Access help. Is that a user-defined function?

 

[Rick Brandt]

Hi Rick;
let me try to explain each step the way to this query........now, let me also
say that the query works perfectly without the new columns only. in the
query design i have table MSM that has fields: ModelNumber, Desc, SpringID,
BorderID, ConfigID, SizeID, FoamCoreID, FoamEncID, BoxTypeID....etc.....the
other tables in the design of the query are Spring that's related to MSM by
SpringID, Border related to MSM by BorderID AND SO ON. essentially, the query
will be displaying
example.
ModelNumber| Desc |Spring, |Border, |Config, |Size, |FoamCore,
|FoamEnc
m1001 some desc sp10 b11 single 33 f345
fe101

You still don't explain what you are trying to accomplish by adding "new fields"
to the query. Fields in queries that are not based on any of the input tables
need to have "something" to use for data, either an expression or a constant
value and their names should not be prefaced with non-existent table names.