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 :)

Use LEFT JOIN instead of INNER JOIN

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?

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".

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.

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.

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")))

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

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")))

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

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.

I am not sure how you identify occupancy as being full.
Exactly, not enough thought has been put into the basic structure here yet;)

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.

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

Good luck....

And don't forget to document it all! :D

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).

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

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

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?

OK I've done some research into data normailisation now, and the relationship table i made before was First normalised form. which is the lowest one.

SO trying to make it third normalised form i get what you see in the attachment.

I haven;t tried to work with it yet, but does it look right?? TO me it looks like a lot of unnecesary stuff going on but if it helps data integrity then i'm all for it.

An old DBA expression, Normalize till it hurts, denormalize until it works. In a large corporate environment with 187 tables normalized to the average 2nd level, I had the DBA's experiment...they were only able to normalize to the 4th normalization...they really couldn't understand the 5th. The number of tables grew to 652 and not one sql statement could be converted due to the complexity of the 4th normalized state. At the third, we had 315 tables, and more than 30% of our sql statements were too complex. The DBA's rewrote one of the applications, and the process time trippled to perform the same report. Normalization is great, but is often not practical. I fully agree on normalizing to the highest practical level, but sometimes that is level is the first, or second. Most of my projects landed somewhere just above the 2nd. The same can be said for Indexing fields in the tables. When I was with IBM, we advocated using indexes, especially with DB2 when it was first released. Well, the sales jumped and then died as clients found process time was 20 to 50 times that of IMS databases. It wasn't until the impact of many index's became apparent, that DB2/Sql started to receive performance improvements (better index handling,storage, etc) but to this day, the greater the number of indexed fields, the slower your performance. Not to say indexes are bad...they are great and increase performance for functions using the index. They require thought and planning, as does normalization.

Add on: Data integrity .... nasty thing ... normalized example... If I have a product called a widget selling for $1 and some clerk decides that widget is not worth selling and changes it to a wonker selling for $100 mid year...normalized, every widget sold becomes a wonker and if we sold 10 before and 10 after the change and if the price is calcualted (another normalization practice) it would report 20 wonkers sold for $2000 when we sold 10 wonkers for $1000 and 10 widgets for $10....The exec's would be all over you for the missing $990. In other words data integrity failed due to normalization. In a flat file that would not happen. But in a flat file, if we wanted to change the name of widget to wonker and missed one, we would report we had one widget sold which is wrong...arguments can be made but in reality, data integrity is a major component of the application and its rules, audit trails, etc. Normalization when done correctly, reduces the likelihood of errors, and therefore enhances data integrity. Normalization does not provide data integrity.

An old DBA expression, Normalize till it hurts, denormalize until it works.

I disagree with that sentiment. It is a myth.

In a large corporate environment with 187 tables normalized to the average 2nd level, I had the DBA's experiment...they were only able to normalize to the 4th normalization...they really couldn't understand the 5th.

It takes work to NOT normalize to 5NF. If something is already normalized to 3NF, it is frequently already in HNF. If databases get too large because of normalization, it is because of a bad design, not because of normalization.

Geroge,
I didn't say the DBA's were good. LOL. They learned a lot in that exercise. And whats wrong with normalizing until it hurts (the highest NF) then stepping it back when you cannot create code to support it?
By the way, do you have a comment on rudeboymcc's design and comment?

You don't need the table joining Landlords to Properties each relationship is a One to Many, One Landlord can have Many properties, One Property can hold Many tenants, One Tenant can pay Many rents etc.,etc,
Depending on the complexity of your db and how much info you actually want to collect it may look something like this

Rich, before you said :

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

So i took landlorts out fo the properties table by making the Landlords to properties table. but now you say i don't need that, I can't see how else I can take out landlords from the properties table. and i can't really tell if you're saying that it's all good becuase they're all one to many relationships or if it's bad.

Your design looks very complicated, I don't think i'd be able to write any SQL for that. This database is only for 150 properties so i don't think it needs that much normalization. out of interest did you come up with that just now or have you used that design somewhere?

Rich, before you said :




Your design looks very complicated, I don't think i'd be able to write any SQL for that. This database is only for 150 properties so i don't think it needs that much normalization. out of interest did you come up with that just now or have you used that design somewhere?

It's a long going project;)
I'll try and post a much simplified version, the complication comes of course when you realise just what a db can actually do, I assume you just want something very basic, and don't want to store rents collected etc

Basically the Primary Key from Landlords becomes the Foreign key in say Properties, the Primary key from Properties becomes the Foreign Key in Tenants and so on and so forth, follow that structure and you can get who the Landlord is for any tenant by using queries

That's the thing, this is simple for now but i will want to do all the rent monitoring with the same database in the future. I'll have a look at yours in more detail and see how much of it applies to me.

Same principle again, One Tenant can have Many payments, the tenant PK becomes the Foreign key in say Payments

As you can see from Rich's relationship, your first releationship post was not 1NF, but was in fact 3NF (even 4NF...not knowing all the data requirements). The last relationship you posted was 5NF+. The HNF (highest) is what you wish to achieve..ergo your original post. You normalized till it hurt (5NF+) and now you can denormalize to the original.
If you read up on denormalization, it is especially applicable to high trafific online systems and small operating environment (PC's) systems.

There is nothing to be gained in this DB by denormalising the structure, without wishing to appear condecending to the OP it would bring more problems than those solved, it's a small business not a giant corporation

OK I've done some research into data normailisation now, and the relationship table i made before was First normalised form. which is the lowest one.

SO trying to make it third normalised form i get what you see in the attachment.

I haven;t tried to work with it yet, but does it look right?? TO me it looks like a lot of unnecesary stuff going on but if it helps data integrity then i'm all for it.

Rich,
Your schema is great.
I was making a comment in regards to this post ... untitled_1.jpg in the post.
Are you saying, tables Flat Rooms, Properties Flats, Properties Tenants, and Property Landlord should stay in the design? I personally disagree, but I've only been programming since 1972.
For the stage that rudeboymcc is at in development, the original relationship posted on page 2 seemed acceptable, with a few enhancements gleened from your schema.
Bob

Rich,
Your schema is great.
I was making a comment in regards to this post ... untitled_1.jpg in the post.
Are you saying, tables Flat Rooms, Properties Flats, Properties Tenants, and Property Landlord should stay in the design? I personally disagree, but I've only been programming since 1972.


I didn't suggest that it was or should remain:confused:

Rich, I tried pushing George, and you to answer the question in that post. That type of connection table has it place at the 5NF for large database structures. I really don't want the impression the last untitled_1.jpg was fine, when it in fact is overkill for rudeboymcc's current development stage and volume.
By the way, thanks for posting such good advice.

Rich, I tried pushing George, and you to answer the question in that post. That type of connection table has it place at the 5NF for large database structures. I really don't want the impression the last untitled_1.jpg was fine, when it in fact is overkill for rudeboymcc's current development stage and volume.

I wouldn't argue with any of those sentiments;)

Hi again, Cheers for all teh help guy but i've decided to change the relationship table again.

I don't like the way before where i had an indicator if the foreign key was an address, flat or room, and certainly don't like having these three fields in every table.

so i came up with the new one (see attached). What do you think@??

This is probably overly normalized for you, but the concept is what I thought you wanted.

This is probably overly normalized for you, but the concept is what I thought you wanted.
It is, way too overnormalised;)

Yup, but it may be a better solution, once de-normalized.

Yup, but it may be a better solution, once de-normalized.
Why denormalise it at all?

Wouldn't have to....I use a structure similar to this, but it could be if volume is low.