real estate matching

[ilbo]

Hi guys,



I have 2 tables... On the 1st one i put all my apartments (city, sqm, rooms, price) and on the 2nd I put all the requests of my clients.

If one of the apartments matches with the request, the record of this apartment should appear automatically, on the client's report.

Is there a way to do that without making a query for every request? i'll try to better explain: I'd like add a request and, automatically, see on the report of this request all the apartments that could match with this request. If I add a new apartment, and it matches with one or more requests, it should automatically appear on the report of every single customer that is looking for such apartment.....thank you in advance...

[recyan]

Doesn't joining the criteria fields of both ClientRequests table & Apartments table give the desired results ?

Thanks

[ilbo]

Hi, thanks for your answer.

Do you mean make a relantionship between tables?

[recyan]

Yes, perhaps something like :

SELECT 
	tblClientRequests.idCl, 
	tblClientRequests.ClientName, 
	tblClientRequests.[city], 
	tblClientRequests.[sqm], 
	tblClientRequests.[rooms], 
	tblApts.AptID, 
	tblApts.[city], 
	tblApts.[sqm], 
	tblApts.[rooms]
FROM 
	tblClientRequests 
	LEFT JOIN 
	tblApts 
	ON 
	(tblClientRequests.[rooms]=tblApts.[rooms]) 
	AND 
	(tblClientRequests.[sqm]=tblApts.[sqm]) 
	AND 
	(tblClientRequests.[city]=tblApts.[city]);

Thanks

[orange]

ilbo,

There is a free data model here that models a generic real estate business. Your tables should look something like those in the model. If you make a list of the questions you intend to ask of your data base, you can "test the model or your modified model" to ensure all of your questions can be answered. If there are questions for which you can not get an answer, then you'll have to adjust the model to include the data/fields to supply such answers.

Good luck.

[ilbo]

Thank you Orange!

[orange]

You're welcome. Happy to help.
There are some great samples and explanations at Martin Green's site that may be of use to you. If it isn't relevant now, keep the link as a reference. As you get into vba and or sql, the samples will help.

For database and table design info see this tutorial. You'll have to wotk through it, but you will learn.

[ilbo]

Yes, perhaps something like :

SELECT 
    tblClientRequests.idCl, 
    tblClientRequests.ClientName, 
    tblClientRequests.[city], 
    tblClientRequests.[sqm], 
    tblClientRequests.[rooms], 
    tblApts.AptID, 
    tblApts.[city], 
    tblApts.[sqm], 
    tblApts.[rooms]
FROM 
    tblClientRequests 
    LEFT JOIN 
    tblApts 
    ON 
    (tblClientRequests.[rooms]=tblApts.[rooms]) 
    AND 
    (tblClientRequests.[sqm]=tblApts.[sqm]) 
    AND 
    (tblClientRequests.[city]=tblApts.[city]);

Thanks

Hello, I'm trying to understand the way to relate the tables...

In a table I have all the main feautures of a house and an Id of the research of a client
in another table I have all the records about what a client is looking for..I related the Id of the client to the Idresearch...but making an inner or outer join does not work...

thank you!

[recyan]

Why do you have ID of research of a client in the House Features table ?
Do not know the details of your project, but based on what you have told, perhaps you should have below tables :
tblHouses ( Contains the Houses ( HouseID - PK ) with their Features ) - Master Table
tblClients ( Contains the Clients ( ClientID - PK ) with their details, such as Name, Contact No, EmailID, Address, etc ) - Master Table
tblClientRequests ( Contains the Requests of the Clients ( RequestID - PK; ClientID - FK ) with the Request Details )

Do not know what Client Research indicates ?

Apart from above, you should have
tblCities - ( CityID - PK, Name of City ) - Master Table

-------

You should aim for standardising the sqm & rooms.

Try sitting down with the link ( design ) that orange has given & You should be able to tailor it to your needs.

In my sql code, the standardisation of sqm, rooms, etc; is critical.

Thanks