My brain just aint working today. I need a query guru to help me out here if possible. I'm joining two tables (simple enough).
The first table is a list of bug requests (Table A), the second table contains custom field information (Table B).
If a bug request is entered, and the user fills-in only those custom fields that are applicable, the fields that are not filled-in simply don't exist in Table B (unless they default to some value and the user doesn't decide to erase this default value...don't ask). Each populated custom field adds a new row in Table B, and that row is identified by a code that represents the type of field being used.
With me so far?
Here's my problem...
Let's say one of the custom fields is a drop-down for a customer name. It defaults to 'NONE'; however, this field hasn't always existed, so there are many bugs that have been submitted without a customer name.
If I write a report query that is supposed to show both a Bug ID from Table A and a Customer Name from Table B (or nothing if there was no associated customer), the join only returns Bugs where a value for customer name actually exists in Table B. Like so...
0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us
What I want to have happen is to show all bugs, regardless of whether or not there is an associated Company. Like so...
0001
0002
0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us
I've tried different types of inner and outer joins to no avail. If the friggin field existed in Table B but was just empty, I'd be fine. This also causes a problem when I'm trying to figure out which bugs do NOT have an associated customer.
The first table is a list of bug requests (Table A), the second table contains custom field information (Table B).
If a bug request is entered, and the user fills-in only those custom fields that are applicable, the fields that are not filled-in simply don't exist in Table B (unless they default to some value and the user doesn't decide to erase this default value...don't ask). Each populated custom field adds a new row in Table B, and that row is identified by a code that represents the type of field being used.
With me so far?
Here's my problem...
Let's say one of the custom fields is a drop-down for a customer name. It defaults to 'NONE'; however, this field hasn't always existed, so there are many bugs that have been submitted without a customer name.
If I write a report query that is supposed to show both a Bug ID from Table A and a Customer Name from Table B (or nothing if there was no associated customer), the join only returns Bugs where a value for customer name actually exists in Table B. Like so...
0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us
What I want to have happen is to show all bugs, regardless of whether or not there is an associated Company. Like so...
0001
0002
0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us
I've tried different types of inner and outer joins to no avail. If the friggin field existed in Table B but was just empty, I'd be fine. This also causes a problem when I'm trying to figure out which bugs do NOT have an associated customer.