All results not shown due to empty fields

[jack_attridge]

I have a query which is supposed to search for all engines with a power rating between a user-specified range ('Rated Power'). The results should state all of these engines along with a few more related details ('System_ID_No', 'Project No', 'Rated Speed', 'Other Ratings' and 'Cylinder Capacity') that are useful to know. However, the problem arises when these other fields are empty. If empty, the related engine results do not appear in the final results spreadsheet. How do I make sure they are included aswell?

SQL:

SELECT tblProjectOverview.System_ID_No, tblProjectOverview.[Project No], tblProjectOverview.Customer, tblEnginePerformance.[Rated Power], tblEnginePerformance.[Rated Speed], tblEnginePerformance.[Other Ratings], tblEngineDefinition.[Cylinder Capacity]
FROM (tblProjectOverview INNER JOIN tblEnginePerformance ON tblProjectOverview.[System_ID_No] = tblEnginePerformance.[Sytem_ID_No]) INNER JOIN tblEngineDefinition ON tblProjectOverview.System_ID_No = tblEngineDefinition.System_ID_No
WHERE (((tblEnginePerformance.[Rated Power]) Between [Enter minimum power rating (kW):] And [Enter maximum power rating (kW):]));

 

[namliam]

the problem is most likely in your "Inner join" parts.... where... i.e. the engine definition doesnt exist...

You might try changing your inner join to Left join, to allow for non-existing records in related tables.

 

[jack_attridge]

simple and perfect solution. Thanks namliam

 

[DemonDNF]

the problem is most likely in your "Inner join" parts.... where... i.e. the engine definition doesnt exist...

You might try changing your inner join to Left join, to allow for non-existing records in related tables.

I also had the problem of missing entries. I had figured out that the problem was with records not being found in SPEC tables. I just couldn't figure out how to set up the SQL to account for optional entries. NOW it works, this is what it looks like:

SELECT I.Key, I.Barcode, P.Part, PK.Package,
       S1.Spec, S2.Spec, S3.Spec, S4.Spec, S5.Spec, S6.Spec
  FROM (((((((Inventory AS I
 INNER JOIN Part AS P ON I.PartID = P.Key)
 INNER JOIN Package AS PK ON PK.Key = I.PackageID)
 LEFT JOIN Spec1 AS S1 ON I.SpecID1 = S1.Key)
 LEFT JOIN Spec2 AS S2 ON I.SpecID2 = S2.Key)
 LEFT JOIN Spec3 AS S3 ON I.SpecID3 = S3.Key)
 LEFT JOIN Spec4 AS S4 ON I.SpecID4 = S4.Key)
 LEFT JOIN Spec5 AS S5 ON I.SpecID5 = S5.Key)
 LEFT JOIN Spec6 AS S6 ON I.SpecID6 = S6.Key
 WHERE ((I.PrintLabel)=True);

I had tried LEFT and RIGHT joins, but I hadn't figured how to line them up properly.

Thank you once again.

Robert


EDIT: I have to say that the wording in Access is not apparent how the joins work. They use the term ALL, but omit to say "according to the where". I thought they meant ALL records come out on one side no matter what.

For the next guy like me:

Main table LEFT JOIN Secondary table
...means entries in Main table will be selected according to WHERE clause,
optional entries in Secondary table will be included if present.

 

[namliam]

EDIT: I have to say that the wording in Access is not apparent how the joins work. They use the term ALL, but omit to say "according to the where". I thought they meant ALL records come out on one side no matter what.

Must disagree with you, any "WHERE" you put on the entire table will (offcourse) limit your recordset...
Furthermore for the Outerjoin to work properly if you need only, you cannot do something like:

SELECT I.Key, I.Barcode, P.Part, PK.Package,
       S1.Spec, S2.Spec, S3.Spec, S4.Spec, S5.Spec, S6.Spec
  FROM (((((((Inventory AS I
 INNER JOIN Part AS P ON I.PartID = P.Key)
 INNER JOIN Package AS PK ON PK.Key = I.PackageID)
 LEFT JOIN Spec1 AS S1 ON I.SpecID1 = S1.Key)
 LEFT JOIN Spec2 AS S2 ON I.SpecID2 = S2.Key)
 LEFT JOIN Spec3 AS S3 ON I.SpecID3 = S3.Key)
 LEFT JOIN Spec4 AS S4 ON I.SpecID4 = S4.Key)
 LEFT JOIN Spec5 AS S5 ON I.SpecID5 = S5.Key)
 LEFT JOIN Spec6 AS S6 ON I.SpecID6 = S6.Key
 WHERE ((I.PrintLabel)=True)
[B][I]And S1.Spec = "SomeThing"[/I][/B];

Obviously the left join will allow non-existing records in the S1 table, however the where now demands some value there thus negating your Left outer join all together....

Tip for the wize, never mix Left and Right join's, particularly if you are freehanding the SQL, it gets messy fast.

On a side note, unrelated to the question at hand, any design that has numbered tables and/or columns razes a number of questions to its design. This usually points to a weakness or even flaw to the design of the database.

 

[DemonDNF]

I have 6 spec fields because I have 6 separate fields that go to specific places on the labels (no pun intended - Spec refers to Part Specifications); Spec1 has to go to the upper left corner, Spec2 to the upper center, and so on.

I probably could have merged the Spec tables but that would have added a lot of unnecessary complexity for a beginner like me. This technique permits me to move Specs about on a label so that they don't overlap by avoiding 3 specs with long values on the same line.

It may not be the ideal or most efficient database structure but it works for me. And most importantly, I can open this in a few years and eventually understand what I was doing.

I just ran the Access database analyser on all tables just for fun and it had no suggestions. I understand the importance of normalization in relational databases, it's the whole point, but there are times when simplicity is the way to go.

Robert

 

[namliam]

Sure the perfect solution isnt always going to be the most managable of solutions....
Just to give you an idea of what might have worked too....

You make, what is called a junction table.
tblPartSpec
I_Key
S_Key

Have no spec fields in your I table and only have one spec table.
This will (dynamicaly) allow anny parts to have 1 or 1.000.000 specs without any trouble.
Not only could you have merged the spec table, you should have.... For "simplicity" sake if some specs only fit to slot 5 or 6 you could build distinct queries on top of the single table and use the queries as input for this query. In your current design with spec existing 6 times, what is going to happen if you need to add a spec or delete / obsolete it?
You need to potentially update 6 tables, 6 may not be as bad, but consider 20 even 100.

I just noticed you dont really have a way of "controling" your specs at the moment, normally in these reference tables you need some form of "active" or a date (possibly even 2 dates) ActiveFrom, ActiveTo. So you can add and remove specs from the user's options without infracting on referential integrity.

 

[DemonDNF]

I had started with a single Spec table 'cause that's how I learned too many moons ago, but I got lost along the way in Access. I relearn Access every time I use it every 5-10 years, never getting completely comfortable with it. This is the furthest I've been.

I'm the only user and it's for personal use. I may have 20-30 types of Parts at the most, what varies in electronic components are the combination of values.

The Parts form is set up with the fields as they will appear on the Label:

The Values form was kept simple:

The Inventory form is where 99% of data entry will happen:

I turn off Spec columns when not used, can filter by clicking on the headings. I'm left with figuring how to implement a similar Sort feature.

You're right, it's still incomplete. I'm missing suppliers: their part numbers, price, etc.

I've accumulated quite a lot of variety over the last 10+ years and reached the point of buying stuff I already had. And this is where the need for properly labelling all my cabinets as well as maintain quantity on hand came from.

This is the Labels from the prototyping phase; I wanted to make sure I could design a layout that would fit on these small plastic drawer cabinets. The barcode had to be easily recognized by my phone (confirmed) and the text had to be large enough for my lowering vision (also confirmed). I basically designed this database to accomodate these labels.

And now I remember that it was printing these Labels with optional joins that I switched from a single Spec table to a fixed design.

Robert

 

[namliam]

As long as it works for you, guess that is the most important thing

 

[DemonDNF]

...On a side note, unrelated to the question at hand, any design that has numbered tables and/or columns razes a number of questions to its design. This usually points to a weakness or even flaw to the design of the database.

This issue has been nagging at me every since the beginning. I knew I could rearrange my structure, but a lot of code was already done.

Now that I am starting over (implementing 10 commandments, error handling in every module, not editting a module as I debug to avoid corruption, etc), I re-arranged my tables.

I'm sure there's still room for improvement, but at least I don't have any more numbered fields and referential integrity sticks to all relations.

Robert

 

[namliam]

Kudo's for taking up such a challange, only thing I doubt, not sure if I mentioned it...
Active, perhaps instead of having that (as a assumed yes/no field) maybe have a ValidFrom and ValidTo field would allow you to for example add a part that will become (in)active on April 1st already now and have the database do the "Magic" on April 1st.