Need assistance generating list

Best Microsoft Access Programmer Portland OR

Hello – and thank you.

I’m having issues with how I anticipate an outer join working.

TablesProperties and Values. Properties includes the Property unique key, street address. Values contains a unique key for each value for each property, foreign key that joins the value to it’s property, and a foreign key for each category (county, lat, long, etc). With the software I’m using, if a value is not input, then a key is not created for that record. So for example, if for 123 Main St I do not enter a county, there isn’t even a null line item, there just is no record at all on the values table.

I have a list of properties, for sake of ease lets says 100. Out of the 100, 40 properties have some value fields that are blank, for example “county”. I am trying to generate a list that shows all 100 properties and the county they’re in, and if the county field is blank, the property shows anyway.

What I’ve tried;

I’ve tried doing a LEFT JOIN where I keep all of the Properties records and include only value records where the join fields are equal. I anticipated that when there is no field, the Property would continue to show but with a blank, instead the property is removed from the list.

So my code looks like this;

SELECT Properties.recID, properties.street, values.*

FROM Properties LEFT JOIN Values ON properties.recID=values.proprecID

WHERE values.county=’Clay’;

So what I would like;

Prop1 123 Main Clay Lat Long

Prop2 123 Front Lat Long

Prop3 321 Main Clay Lat Long

What I’m getting;

Prop1 123 Main Clay Lat Long

Prop3 321 Main Clay Lat Long

If I haven’t included enough info to help you help me – let me know. I didn’t want to get too in the weeds.

submitted by /u/LendMeYourWalnuts
[link] [comments]

Microsoft Acess Developer Portland OR