query question

J

Jessica

Hi,


I am trying to create a query based on a table. When I run the query it
doesn't show any records is it because there are null values in certain
records. If so is there a way to show the records when I run the query?
I want to create a report from that query and if the field is blank then
it would show as a blank text box on the report.

TIA,
Jessica
 
D

Dirk Goldgar

Jessica said:
I am trying to create a query based on a table. When I run the query
it doesn't show any records is it because there are null values in
certain records.
Click to expand...

It's hard to say without seeing the SQL of the query. It could be. If
you're applying criteria to fields, and no records meet those criteria,
that's what you'll get. If you're joining two tables on fields that are
Null, again that's what'll happen. Post the SQL of the query and maybe
we can tell you how to change it to get what you want.
If so is there a way to show the records when I run
the query?
Probably.

I want to create a report from that query and if the field
is blank then it would show as a blank text box on the report.
Click to expand...

We need more information to guide you.
 
J

Jessica

This is what I have

SELECT Container.Description, Tray.Description, [Slip
Sheets].Description, Inserts.Description, [Pallet Tag].Description,
[Tier Sheets].Description, [Cover Sheet].Description, Wrap.Description,
Caps.Description, Pallets.PalletPKNumber, Pallets.PalletDescription,
[Pallet Patterns].PalletPattern
FROM Materials AS [Tier Sheets] INNER JOIN (Materials AS [Pallet Tag]
INNER JOIN (Materials AS Inserts INNER JOIN (Materials AS [Slip Sheets]
INNER JOIN (Materials AS [Cover Sheet] INNER JOIN (Materials AS Wrap
INNER JOIN (Materials AS Caps INNER JOIN (Materials AS Tray INNER JOIN
(Materials AS [Container] INNER JOIN (Expirations INNER JOIN ([Pallet
Patterns] INNER JOIN (Pallets INNER JOIN UPC ON Pallets.PalletID =
UPC.PalletID) ON [Pallet Patterns].PatternId = UPC.PalletPattern) ON
Expirations.ExpirationID = UPC.ExpirationID) ON Container.PKNumber =
UPC.ContainerPKNumber) ON Tray.PKNumber = UPC.TrayPKNumber) ON
Caps.PKNumber = UPC.CapPKNumber) ON Wrap.PKNumber = UPC.WrapPKNumber) ON
[Cover Sheet].PKNumber = UPC.CoverSheetPKNumber) ON [Slip
Sheets].PKNumber = UPC.SlipSheetPKNumber) ON Inserts.PKNumber =
UPC.InsertPKNumber) ON [Pallet Tag].PKNumber = UPC.PalletTag) ON [Tier
Sheets].PKNumber = UPC.TierSheetPKNumber;
 
D

Dirk Goldgar

Jessica said:
This is what I have

SELECT Container.Description, Tray.Description, [Slip
Sheets].Description, Inserts.Description, [Pallet Tag].Description,
[Tier Sheets].Description, [Cover Sheet].Description,
Wrap.Description, Caps.Description, Pallets.PalletPKNumber,
Pallets.PalletDescription, [Pallet Patterns].PalletPattern
FROM Materials AS [Tier Sheets] INNER JOIN (Materials AS [Pallet Tag]
INNER JOIN (Materials AS Inserts INNER JOIN (Materials AS [Slip
Sheets] INNER JOIN (Materials AS [Cover Sheet] INNER JOIN (Materials
AS Wrap INNER JOIN (Materials AS Caps INNER JOIN (Materials AS Tray
INNER JOIN (Materials AS [Container] INNER JOIN (Expirations INNER
JOIN ([Pallet Patterns] INNER JOIN (Pallets INNER JOIN UPC ON
Pallets.PalletID = UPC.PalletID) ON [Pallet Patterns].PatternId =
UPC.PalletPattern) ON Expirations.ExpirationID = UPC.ExpirationID) ON
Container.PKNumber = UPC.ContainerPKNumber) ON Tray.PKNumber =
UPC.TrayPKNumber) ON Caps.PKNumber = UPC.CapPKNumber) ON
Wrap.PKNumber = UPC.WrapPKNumber) ON [Cover Sheet].PKNumber =
UPC.CoverSheetPKNumber) ON [Slip Sheets].PKNumber =
UPC.SlipSheetPKNumber) ON Inserts.PKNumber = UPC.InsertPKNumber) ON
[Pallet Tag].PKNumber = UPC.PalletTag) ON [Tier Sheets].PKNumber =
UPC.TierSheetPKNumber;
Click to expand...

That's a lot of inner joins. To get any records back, none of the
linking fields in all those inner joins can be Null. From what you say,
I guess this is not the case. If that's the problem, you should change
every join that might involve a Null value on one side into an outer
join: either a LEFT JOIN or a RIGHT JOIN. Which of those two the joins
should be is hard for me to figure out just from looking at the SQL, but
I suspect that you want RIGHT JOINs. You want the kind of join that is
described in the query designer as "include ALL records from <table1>
and only those records from <table2> where the joined fields are equal",
with <table1> being the table that might have the Null field.
 
Top