report is not generating query data

[bigwillno2 via AccessMonster.com]

Hey everyone,
i have been running through the same problem with a query over and over, can
anyone help. I am not sure where the problem is, somehow i think is in the
structure of the table, query or access. my query is as follow:

SELECT LinkedSalesOrder.OrderNo, LinkedSalesOrder.ID, LinkedSalesOrder.
RequiredDate, LinkedSalesOrder.ID_1, LinkedSalesOrder.Description,
LinkedSalesOrder.Comment1, LinkedSalesOrder.OrderQty, MSM.Produce, qrMSM.
Bfill, qrMSM.Qfill, qrMSM.Uphfill, qrMSM.Ptfill, LinkedSalesOrder.Comment2,
MSM.SpringID, MSM.LabelID, MSM.SizeID, MSM.NeedleID, MSM.PatternID, MSM.
ConfigID, MSM.BorderID, MSM.FoamCoreID, MSM.FoamEncID
FROM qrMSM INNER JOIN (LinkedSalesOrder INNER JOIN MSM ON LinkedSalesOrder.
ID_1 = MSM.ModelNumber) ON qrMSM.ModelNumber = LinkedSalesOrder.ID_1
WHERE (((MSM.BoxChecked)=No))
WITH OWNERACCESS OPTION;

this horrible looking thing here generates a report that's empty if i include,
MSM.FoamCoreID, MSM.FoamEncID or any other new field that i add to the table
MSM. is it me or access is going bugs. can anyone help

 

[Jeff Boyce]

If the complex query doesn't work, consider stepping back.

Can you get a single-table query to work (i.e., return rows you'd expect to
see)?

Once that's working, can you add a single additional table, join them, and
get the rows you expect?

"Rinse and repeat..."

(hint: since you are using an "Inner Join", you've told Access to only show
you rows where there are matching values in the joined tables. If one of
your tables is missing a matching value, you won't see a row, even though
what you'd expect to see from the OTHER table might be there.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bigwillno2 via AccessMonster.com]

thanks for the help, i still run through the same problem, when it somes to
creating a reports of the value it doesnt show anything if those new fields
are included. however, let me make clear that in the query that i have, i
have everything in ID format and it does show. wherever, there is no matching
value it should just show, nothing right. for example, i have a ModelNumber,
Border, Spring, FoamCore. if they are all there, it should come up with a
result of ModelNumber = m001, a Border = B001, Spring = sp01 and FoamCore =
fc001 and if one of them is not there, for example, ModelNumber = m001,
Border = , Spring = sp01 and FoamCore = fc001....am i correct with this when
it comes inner join.......or am i wrong?

To get a clear picture
the new fields that i am trying to introduce to the MSM table, are FoamCoreID,
FoamEncID, BoxTypeID, BoxProfileID
each fields is foreign to the MSM table and are primary to their individual
tables. foamCore, foamenc, boxtype, boxprofile.

The quey that i have look like this.
qrProd....
ModelNumber(MSM), Desc(MSM), SpringID(MSM), BorderID(MSM), ConfigID(MSM),
FoamCoreID(MSM), RequiredDate(SalesOrder)......ect.......

this query does display all the fields, but when i go to create an report
from the query using the relationship, it doesnt work. meaning that on the
report, i get, ModelNumber, Desc, RequiredDate, Spring(from Spring table),
Border(from Border table), Config(from Config table) till here i am fine, but
when i do the same report with foamcore(from FoamCore table), here is where
the problem comes. i hope i explained myself right, because i dont find any
solution, nor see posible ones for this problem. and i tried the step by step,
you suggested and it remains.

If the complex query doesn't work, consider stepping back.

Can you get a single-table query to work (i.e., return rows you'd expect to
see)?

Once that's working, can you add a single additional table, join them, and
get the rows you expect?

"Rinse and repeat..."

(hint: since you are using an "Inner Join", you've told Access to only show
you rows where there are matching values in the joined tables. If one of
your tables is missing a matching value, you won't see a row, even though
what you'd expect to see from the OTHER table might be there.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hey everyone,
i have been running through the same problem with a query over and over,

[quoted text clipped - 20 lines]

table
MSM. is it me or access is going bugs. can anyone help

 

[Jeff Boyce]

I'm still not quite clear on what you are doing, and what is/isn't
working...

Are you saying that you can create a query, with all joins, and get ALL the
data you need in the report (from your query), but that your report doesn't
show it all? Or are you saying that you reach some point in the
step-by-step-adding-a-new-table buildup of the queries when it suddenly
fails to return the data?

If the latter, take a look at Access HELP re: Left (and Right) Joins, as I
suggested in my previous post.

Regards

Jeff Boyce
Microsoft Office/Access MVP

thanks for the help, i still run through the same problem, when it somes
to
creating a reports of the value it doesnt show anything if those new
fields
are included. however, let me make clear that in the query that i have, i
have everything in ID format and it does show. wherever, there is no
matching
value it should just show, nothing right. for example, i have a
ModelNumber,
Border, Spring, FoamCore. if they are all there, it should come up with a
result of ModelNumber = m001, a Border = B001, Spring = sp01 and FoamCore
=
fc001 and if one of them is not there, for example, ModelNumber = m001,
Border = , Spring = sp01 and FoamCore = fc001....am i correct with this
when
it comes inner join.......or am i wrong?

To get a clear picture
the new fields that i am trying to introduce to the MSM table, are
FoamCoreID,
FoamEncID, BoxTypeID, BoxProfileID
each fields is foreign to the MSM table and are primary to their
individual
tables. foamCore, foamenc, boxtype, boxprofile.

The quey that i have look like this.
qrProd....
ModelNumber(MSM), Desc(MSM), SpringID(MSM), BorderID(MSM), ConfigID(MSM),
FoamCoreID(MSM), RequiredDate(SalesOrder)......ect.......

this query does display all the fields, but when i go to create an report
from the query using the relationship, it doesnt work. meaning that on the
report, i get, ModelNumber, Desc, RequiredDate, Spring(from Spring table),
Border(from Border table), Config(from Config table) till here i am fine,
but
when i do the same report with foamcore(from FoamCore table), here is
where
the problem comes. i hope i explained myself right, because i dont find
any
solution, nor see posible ones for this problem. and i tried the step by
step,
you suggested and it remains.

If the complex query doesn't work, consider stepping back.

Can you get a single-table query to work (i.e., return rows you'd expect
to
see)?

Once that's working, can you add a single additional table, join them, and
get the rows you expect?

"Rinse and repeat..."

(hint: since you are using an "Inner Join", you've told Access to only
show
you rows where there are matching values in the joined tables. If one of
your tables is missing a matching value, you won't see a row, even though
what you'd expect to see from the OTHER table might be there.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hey everyone,
i have been running through the same problem with a query over and over,

[quoted text clipped - 20 lines]

table
MSM. is it me or access is going bugs. can anyone help