VBA OpenRecordset relationshi

[Anthony Linnett]

Hi guys, please forgive any use of incorrect terminology, i am a bit of an
access newbie.

Below I have constructed an example of the problem I am having. My db does
not have any relationships defined and contains the following tables.

========================================================================
tblManufacturers
Manufacturer_ID Manufacturer_Name
1 Asus
2 Sony
========================================================================
tblSuppliers
Supplier_ID Supplier_Name
1 Bobs Local Computers
2 Black Market
========================================================================
tblParts
Part_ID Part_Name
1 Mobile Phone
2 Laptop
========================================================================
tblPartsSuppliers
PartSupp_ID Supplier_ID PartSupp_Code Manufacturer_ID
PartManuf_Code Part_ID
1 1 BasicPhone
1
2 1 RipoffPhone
2 1
3 StolenLaptop
2 SomeSonyCode 2
========================================================================
We use tblPartsSuppliers to list where a Part can be purchased. Supplier_ID,
ParSupp_Code, Manufacturer_ID and PartManuf_Code are all not required and
Nulls are allowed. I db.OpenRecordset with SQL query "SELECT * from
tblPartsSuppliers WHERE Part_ID = 1" (braces etc. omitted) and PartSupp_ID 1
and 2 are NOT returned, however when all fields are populated 1 and 2 ARE
returned.

What can I use to make the query return records containing fields with Null
values? (I understand the table structure is horrible, however this is what
I inherited. It does make for easy data entry.) Thanks in advance.

 

[Allen Browne]

The query statement:
SELECT tblPartsSuppliers.*
from tblPartsSuppliers
WHERE Part_ID = 1;
returns all records from tblPartsSuppliers that match part 1.

If there are records in tblPartsSuppliers for part 1, then none will be
returned.

 

[Anthony Linnett]

I think you were saying if the are no records, none will be returned.
However there are records for Part 1 which contain Null supplier or
manufacturer ids and theses aren't being returned. when i populate the Null
fields with data, they then are returned by the query.

 

[Anthony Linnett]

Allen, sorry did I miss your point? Are you saying that the behaviour I am
seeing should not happen and must be caused by something else I did not
specify?

 

[Allen Browne]

Sorry: you're correct. No records can be returned if there aren't any in the
table.

Perhaps you have some records in the Parts table that have no suppliers at
all in tblPartsSuppliers?

I had difficulty following your example records, due to the line wrap.

It seems to me that any part has only one manufacturer, so Manufacturer_ID
and PartManuf_Code fields are attributes of the part (so belong in
tblParts.)

Also, I don't understand the point of any record in tblPartsSuppliers with a
null Part_ID, or a null Supplier_ID. It does not seem to be useful to enter
a record that says, "We get I-don't-know-what from I-don't-know-who." The
information on the part and the supplier exists in the other tables, so the
only point of records in this table would be to record that a specific part
comes from a specific manufacturer. (But perhaps there are other issue you
haven't mentioned that make sense of this.)