Query filters records on null fields

[CJSSC]

I have a query that is filtering out records when no filters are applied. The underlying table "Zero Length & Required" properties are set to no for all of the text boxes and Memo boxes. I should be able to retrieve all records but I only get records with all fields filled.

TIA,
Cj

 

[KenReay]

Hi

Posting the SQL of the query might help someone help you Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[CJSSC]

You got it:

SELECT PartRequest.*, [tblNovellusBOM].[Description], tblNovellusBOM_1.Description, tblNovellusBOM_2.Description, tblNovellusBOM_3.Description, tblNovellusBOM_4.Description, tblNovellusBOM_5.Description, tblNovellusBOM_6.Description, tblNovellusBOM_7.Description, tblNovellusBOM_8.Description, tblNovellusBOM_9.Description
FROM tblNovellusBOM AS tblNovellusBOM_9 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_8 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_7 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_6 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_5 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_4 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_3 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_2 INNER JOIN (tblNovellusBOM AS tblNovellusBOM_1 INNER JOIN (tblNovellusBOM INNER JOIN PartRequest ON [tblNovellusBOM].[PartNumber]=[PartRequest].[Part1Number]) ON tblNovellusBOM_1.PartNumber=[PartRequest].[Part2Number]) ON tblNovellusBOM_2.PartNumber=[PartRequest].[Part3Number]) ON tblNovellusBOM_3.PartNumber=[PartRequest].[Part4Number]) ON tblNovellusBOM_4.PartNumber=[PartRequest].[Part5Number]) ON tblNovellusBOM_5.PartNumber=[PartRequest].[Part6Number]) ON tblNovellusBOM_6.PartNumber=[PartRequest].[Part7Number]) ON tblNovellusBOM_7.PartNumber=[PartRequest].[Part8Number]) ON tblNovellusBOM_8.PartNumber=[PartRequest].[Part9Number]) ON tblNovellusBOM_9.PartNumber=[PartRequest].[Part10Number];

All relationships have been set and I checked that there are no AND references.

Brief explanation: Fills in the part description for the selected parts on an order form. Problem was detected when this query was used to print a Part Request (report tied to query) but only 8 of 13 would print. The 5 that would not print had blank part number fields (10 different parts weren't needed on that request).

Hope that helps.

Cj

P.S. This is the second straight query that was working but suddenly "went south"...I had to retype the last one.

 

[KenReay]

Hi

The joins are on Part Number, and you say 5 had blank (null?) part number surely they would not be expected to pick up with an INNER JOIN

or am I missing the point here? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[CJSSC]

I'm trying to comply with "normalization" rules by not capturing the descriptions in table PartRequest since it is already contained in tblNovellusBOM. The two tables are related by the PartNumber to Part#Number field.

If a particular part request (for a specific system ID#...contained in the PartRequest.*) only has 5 parts, then Part6Number, Part7Number, etc. will be blank (null).

Cj

 

[KenReay]

Hi

Yes I see what you are trying to do, but...

aren't you breaking a normalisation rule by having repeating data in the table PartRequest (ie PartNumber1, PartNumber2..etc)

I think this is what is causing your fundamental problem.

It is a bit cheely of me, since I do not know your application as well as you, but it seems to me that table PartRequest should be redesigned as simply

PartNumber
QtyRequired
...etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[KenReay]

Hi

Yes I see what you are trying to do, but...

aren't you breaking a normalisation rule by having repeating data in the table PartRequest (ie PartNumber1, PartNumber2..etc)

I think this is what is causing your fundamental problem.

It is a bit cheeky of me, since I do not know your application as well as you, but it seems to me that table PartRequest should be redesigned as simply

PartNumber
QtyRequired
...etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[CJSSC]

Only data unique to each record is being captured in the PartRequest table:
ID#
Date
System#
Requestor
Part1Number
Part1Qty
Part1Status
Part1Issued
etc up to Part10

Am I violating normalisation?

Cj

 

[KenReay]

Hi

Yes afraid so, cannot quote verbatin from the Normalisation Bible, but you are breaking the 'rules'

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[CJSSC]

How, if each part number is unique?

 

[KenReay]

Hi

Becuase you should nat have repeating sets of data PartNumber1, PartNumber2 etc

Just to be a little more helpful, I would say it should be two tables, something like:

tblRequest
ID# ) Prime Key
Date )
System# )
Requestor

tblRequestParts
ID# )
Date ) Prime
System# ) Key
ItemNo )
PartNumber
PartQty
PartStatus
PartIssued

I am a bit unsure aboute the prime key, without knowing more about you application, maybe Id# is unique?

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[CJSSC]

Problem solved...the part number field (Part1Number, etc.) cannot be blank (or null) if it is tied to another field such as Description from another table. I added NA for blank part numbers and everything's working great.

Thanks, Ken!