Report HELP

[Will G]

I am trying to display everything on a query through a report, but when i
include some fields, then there is no data showing. here is the query that i
have, can anyone visualize what's wrong with it.

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;

 

[Marshall Barton]

I am trying to display everything on a query through a report, but when i
include some fields, then there is no data showing. here is the query that i
have, can anyone visualize what's wrong with it.

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;

I don't see how adding a field can cause a problem like
that. More likely, it's adding a table using an INNER JOIN.
Try using LEFT JOIN instead.

 

[Will G]

Hi, thanks for the help, it worked. i was wondering if you have an answer for
me for the following question. i am new in access development and i am trying
to create a query that uses the function count to count the amount of items
for a vendor. now, i have a vendor table, a spring table, a foam table and so
on. i like to create query that shows, spring, foam, and other items per
vendor. i create a query for each item count each item and thier vendor, but
when i try to create just one for vendor and all items, i get stuck and get
bad results. it's there a directory where i could learn about that or can you
help? thanks again.

 

[Marshall Barton]

Hi, thanks for the help, it worked. i was wondering if you have an answer for
me for the following question. i am new in access development and i am trying
to create a query that uses the function count to count the amount of items
for a vendor. now, i have a vendor table, a spring table, a foam table and so
on. i like to create query that shows, spring, foam, and other items per
vendor. i create a query for each item count each item and thier vendor, but
when i try to create just one for vendor and all items, i get stuck and get
bad results. it's there a directory where i could learn about that or can you
help?

What is your query?

What are the results?

Why are the results "bad"?

 

[Will G]

Hello,
this is my current query, and it's completely wrong. it doesnt give me no
result. and what i want to do is to create a PO per vendor. i would need to
count each item(spring, foamcore, foamenc) and set a vendor criteria. but
this doesnt work. i think i might need a crosstab query or something, but i
am completely lost on how to do it. can you help.

SELECT Vendor.Vendor, qrProduction.Spring, Count(qrProduction.Spring) AS
CountOfSpring, qrProduction.RequiredDate, qrProduction.FoamCore,
Count(qrProduction.FoamCore) AS CountOfFoamCore, qrProduction.FoanEnc,
Count(qrProduction.FoanEnc) AS CountOfFoanEnc
FROM FoamEnc INNER JOIN (FoamCore INNER JOIN (qrProduction INNER JOIN
((Spring INNER JOIN MSM ON Spring.SpringID = MSM.SpringID) INNER JOIN Vendor
ON Spring.VendorID = Vendor.VendorID) ON qrProduction.ID_1 = MSM.ModelNumber)
ON (FoamCore.FoamCoreID = MSM.FoamCoreID) AND (FoamCore.VendorID =
Vendor.VendorID)) ON (FoamEnc.FoamEncID = MSM.FoamEncID) AND
(FoamEnc.VendorID = Vendor.VendorID)
GROUP BY Vendor.Vendor, qrProduction.Spring, qrProduction.RequiredDate,
qrProduction.FoamCore, qrProduction.FoanEnc
HAVING (((Vendor.Vendor)="Pacific Spring"));

 

[Will G]

Sorry for the mess on the previous post....not what i intended to do. i guess
what i am looking for ultimtely is to comeup with a purchase order. i created
a UNION query that puts together all this mess for me and created another
query base on the Union query to Count. now, my question is how can i
include, Vendor name to this query, Where i will have a result or report that
displays Vendor----Item1, amountitem1, item2, amountofitem2. this is what i
have so far.

Union Query: ItemsToCount

SELECT Spring AS Items FROM [qrProduction]UNION ALL
SELECT FoamCore FROM [qrProduction]UNION ALL SELECT FoanEnc FROM
[qrProduction];


Count Query Base On Union Query: countofItem

SELECT ItemsToCount.Items, Count(ItemsToCount.Items) AS RecordCount
FROM ItemsToCount
GROUP BY ItemsToCount.Items
WITH OWNERACCESS OPTION;

Main Query that i am using for the union is: qrProduction but this doesnt
have the field that i want to incorporate which is VendorName.

am i complecating my life,it's there a easier way to do that? can you help me.

 

[Marshall Barton]

this is my current query, and it's completely wrong. it doesnt give me no
result. and what i want to do is to create a PO per vendor. i would need to
count each item(spring, foamcore, foamenc) and set a vendor criteria. but
this doesnt work. i think i might need a crosstab query or something, but i
am completely lost on how to do it. can you help.

SELECT Vendor.Vendor, qrProduction.Spring, Count(qrProduction.Spring) AS
CountOfSpring, qrProduction.RequiredDate, qrProduction.FoamCore,
Count(qrProduction.FoamCore) AS CountOfFoamCore, qrProduction.FoanEnc,
Count(qrProduction.FoanEnc) AS CountOfFoanEnc
FROM FoamEnc INNER JOIN (FoamCore INNER JOIN (qrProduction INNER JOIN
((Spring INNER JOIN MSM ON Spring.SpringID = MSM.SpringID) INNER JOIN Vendor
ON Spring.VendorID = Vendor.VendorID) ON qrProduction.ID_1 = MSM.ModelNumber)
ON (FoamCore.FoamCoreID = MSM.FoamCoreID) AND (FoamCore.VendorID =
Vendor.VendorID)) ON (FoamEnc.FoamEncID = MSM.FoamEncID) AND
(FoamEnc.VendorID = Vendor.VendorID)
GROUP BY Vendor.Vendor, qrProduction.Spring, qrProduction.RequiredDate,
qrProduction.FoamCore, qrProduction.FoanEnc
HAVING (((Vendor.Vendor)="Pacific Spring"));

That's a heck of a query with all those tables. I can not
unravel all that in a newsgroup so I suggest that you start
over with just two tables and make sure you are getting the
desired result before adding in another table. Taking it in
small steps may allow you to see what is going wrong without
piling it all together into one giant leap with more
problems that anyone can grasp.

 

[Will G]

--
need help

That's a heck of a query with all those tables. I can not
unravel all that in a newsgroup so I suggest that you start
over with just two tables and make sure you are getting the
desired result before adding in another table. Taking it in
small steps may allow you to see what is going wrong without
piling it all together into one giant leap with more
problems that anyone can grasp.

 

[Marshall Barton]

You will need a linking field from the item to the vendor.

If the items are only available from a single vendor and and
the vendors table has a field with the item (unlikely), then
you can link on the items field:

SELECT countofItem.Items, RecordCount, vendorname
FROM countofItem INNER JOIN vendors
ON countofItem.Items = vendors.Items

But if an item can by supplied by more than one vendor, that
will be insufficient. In this case the Join will probably
need to be in each Select in the UNION ALL query using some
other field.