Is there an "If Exists" option in SQL?

[rudeboymcc]

HI. I'm having some trouble with basic SQL. the structure of the database:
Table 1: Properties
Table 2: Flats

Each property can have flats, but there are many houses with no flats.

I'm trying to write an SQL statement to list al the properties and flats in one list.

I can get it to list all the Properties with:

SELECT Properties.Address
FROM Properties
GROUP BY Properties.Address;

And can get it to list all the Flats with :

SELECT Properties.Address, Flats.[Flat Number]
FROM Properties INNER JOIN Flats ON Properties.ID = Flats.Address
GROUP BY Properties.Address, Flats.[Flat Number];

The problem is when I use the second one, it does nto show the properties with no flats (becuase the INNER JOIN Flats ON Properties.ID = Flats.Address returns null i guess). Is there any way to concatenate the two statements?

any help is appreciated

 

[FoFa]

Use LEFT JOIN instead of INNER JOIN

 

[rudeboymcc]

Perfect! thanks. Now I have another problem and it's a similar problem. I've got another table of tenants. and a tenant can either live in a property, a flat, or a room.

obviously i can't use just one ID in the tenant's table becuase i won't be able to differentiate between the ID's of the properties, flats and rooms becuase they are autonumbered (and so are not unique when you look at the three tables together).

THe only solution I';ve found is to put three fields in the tenant's table, Property Flat and Room and only fill the relavent ones in. is there a better/more efficient way of doing this?

 

[MagicMan]

Create a field called Tenancy Type with validation of P,F, or R in the tenant table. Or if you do not want the need to translate "Property,Flat, or Room".

 

[MagicMan]

A bit of data normalization might be recommended, for example, the properties and flats tables could be one table...properties...property types (1=property only, 2=property and flat, 3= property and rooms, 4= property and flats, and rooms, 5=flats Only, 6= flats and rooms, 7=rooms only). This would facilitate tenancy type validation to the address.

 

[rudeboymcc]

Ok those two soltuions sound pretty good. I think i'm gonna go with the tenancy type field. but one last question for that.

When i give a tenant an address, I want to use a combo box with the SQL statement at the top of this thread. Now this SQL statement has values with three different levels (some are just properties, some have flats, some have flats and rooms).

Is there a way to fetch what level the chosen property has, and then convert this into the tenancy type field??

My first thoughts on this was first check if room is still null, if it isn't then tenancy type is room. Then check if flat is null and if it isn't then tenancy type is flat. otherwise tenenancy type is property.

cheers for all the help this forum's great.

 

[MagicMan]

So many ways to do things in Access, but the suggestion you presented sounds right to convert the existing data. You could also use Expression Builder in the Query Design to Create the field.

TenancyType: Iif(IsNull(Room)=False,"R", Iif(IsNull(Flat)=False, "F", "P")))

 

[Rich]

How are you going to check for multiple occupancies with an is null statement?

 

[MagicMan]

Oops, that works in a query with one occurance. With multiple occurances, a query can be constructed using Group By on Properties.Address as follows:

TenancyType: Iif(IsNull(Max.Room)=False,"R", Iif(IsNull(Max.Flat)=False, "F", "P")))

 

[Rich]

Then how would you know that the occupancy is full etc?

 

[MagicMan]

Now your beyond my scope of knowledge on the data. The last query creates a field for the property record based on multiple flat records and multiple roomer records. I am not sure how you identify occupancy as being full.

 

[Rich]

I am not sure how you identify occupancy as being full.

Exactly, not enough thought has been put into the basic structure here yet

 

[MagicMan]

Oh, ok. Well if I am correct, you have Tenant table, roomer info in a table, flat info in another table, and property info in the main table. You could use the following in a Group By Properties.address:

TenancyType: Iif(Count.Room)> 0,"R", Iif(Count.Flat > 0, "F", "P")))

This is of course in a query where all four tables are related by Properties.Id and TenacyType is the field updated in the Tenants table. That probably wont do the trick though, since an occupant in a building with roomers and flats could be either the property owner, flat occupant, or room occupant.

Analysis is the key. Foe every hour spent on design, you save a week of development time.

 

[Rich]

And a pen and paper with a list of requirements is the best place to start

 

[MagicMan]

Good luck....

 

[oumahexi]

And don't forget to document it all!

 

[rudeboymcc]

K there seems to be a lot of confusion. I've attached my relationship table to give a better idea of what's going on.

What I need is to assign a property to a tenant. and this can either be a whole property, a flat or a room. (I'm not bothered about if a place is full becuase one flat can have multiple tenants).

So with your help i made the following SQL query:

SELECT Properties.ID, Properties.[House Number], Properties.Address, 'Flat '+Flats.[Flat Number] AS Expr1, 'Room '+Rooms.[Room Number] AS Expr2, Properties.[Post Code]
FROM Properties LEFT JOIN (Flats LEFT JOIN Rooms ON Flats.ID = Rooms.Flat) ON Properties.ID = Flats.Address
GROUP BY Properties.ID, Properties.[House Number], Properties.Address, Properties.[Post Code], Flats.[Flat Number], Rooms.[Room Number];

This produces a combo box with entries such as:
5 The avenue flat 1
2 The close
3 King street Flat 3 Room 4

Now becuase the control source of the combo box is set to the first column, whichever I select from above it will choose Properties.ID as the ID. What I want, is if there is a Flat or a Room, it uses Flat.ID or Room.ID as the control source, and also changes another field to indicate what table the ID is from.

So if I select 5 the avenue flat 1, the ID returned would be Flat.ID and a seperate field will be changed to F. similarly, if i select 3 King street flat 3 room4, the Room.ID will be returned and the seperate field changed to R.

If anyone can think of a better way of arranging this please shout, i've sat here for hours analysing the data and can't see a better way. A much easier way would be to only have one table (properties), and have each flat and room in their own record, but this will create a lot of multiple entries as I'd get a list like this:

3 Kings Street Flat 1 Room1
3 Kings Street Flat 1 Room 2
3 Kings Street Flat 2 Room 1 and etc etc

ANd i've left out most of the fields like postcode and landlord, it just produces too much repeated data (although there are only 150 properties here, but still it will slow down what could be a very fast system).

 

[Rich]

You need to normalise your data and structure, Landlords info does not belong in the properties table other than the FK in the same way tenants do not belong in the property table, you have several one to many relationships here

 

[MagicMan]

You have done well so far! The more info we have the better we can help, in that respect what is the purpose of the data being stored. Is this a simple data entry, update, and online lookup? Or, is this for reporting, and if so, by what type of grouping? This combo box appears to be the source for populating information on a tenant's table (not in the Jpg) to identify the type of tenancy. Is this correct?

Anyways, to do what you described...
1) your combo box is 4 columns, It needs to be 6, including RoomId and FlatId ... the column lengths in the combo box should be 0:0:0:2":.x":.y"
2) When flat is not present for that combo box entry, the column is Nulls
3) When Room is not present for that combo box entry, the column is Nulls

Change the Select to include RoomId and FlatID right after Prperties ID
(SELECT Properties.ID, Room.ID, Flat.ID, .......)

In the conbo box After Update Event:

Dim strTenancyType As String
Dim lngId As Long
'The Simple way:
'Note the column start at 0 so column6 is .Column(5)
If IsNulls(Me.cboYourComboBox.Column(5)) = False then
    strTenancyType = "R"
    lngId = Me.cboYourComboBox.Column(2)
Else    
    If IsNulls(Me.cboYourComboBox.Column(4)) = False then
        strTenancyType = "F"
        lngId = Me.cboYourComboBox.Column(1)
    Else
        strTenancyType = "P"
        lngId = Me.cboYourComboBox.Column(0)
    End if
End if
....do what you need to with the variables here

 

[rudeboymcc]

MagicMan, that looks like a solid peice of code, i'll get back to you with the results, cheers!

You need to normalise your data and structure, Landlords info does not belong in the properties table other than the FK in the same way tenants do not belong in the property table, you have several one to many relationships here

Rich, I'm not entirely sure what you're trying to say. The landlord field in the property table is just the landlord ID, all the landlord details are in Contacts. not sure what FK is either.

Do you mean perhaps that I should have another table between the properties and the contacts tables, that just has two columns, one a list of property ID's and another of contact id's? Is there any benefit to this?