I have just realised that I cannot link the same identifier across two tables where in each table it is the primary key for two types of property, residential and commercial. The problem arising from this is that my viewings table now has to have two fields for ID, and a field to distinguish the property being viewed as either commercial or residential. As the property can only be one I would like the database to allow you only to enter into the correct field, for example if a property is Commercial, and has the Commercial PropertyID of 12345, then the table should only allow you to enter the data into the corresponding field, and not allow you to enter it into the ResidentialPropertyID field. Is there a way of doing this, would it be through use of an IF statement? The database will be using forms to enter, edit and view information. Your help is much appreciated.

Think you're in the wrong forum for this. Try Office Software

Do the CommercialPropertyID and ResidentialPropertyID reference other tables (as in one for commercial and one for residential)? I would have a rethink if so - perhaps have one table containing both commercial and residential, and have a flag on this table to distinquish them both - therefore using only one ID field which can be changed to a lookup table instead - this way, you can have a different form for Commercial and Residential, and have the lookup tables bind to Queries instead, a query named ResidentialQry which selects only those records where the property type is Residential, and a query named CommercialQry with the equivalent for the Commercial properties.

yes they do link to other tables. the problem is a significantly different amount of data needs to be stored on commercial and residential properties. I think I am going to have a Res Properties ID and Com. properties ID.

A way around this is to store the "base" information - as in the information that is the same on both types of property, and then further link to a secondary table for the additional information. Query definitions can handle the table linking behind the scenes which makes life a heck of a lot easier - it's as if they are in one table. I usually only use Access to create tables and querydefs - I normally have Visual Basic or ASP.NET to handle all the clever stuff so apologies if I am a little set in my ways! As the other fella said up there try in the Office Software and also Programming forums as well.