Matching between 2 tables

[slevin]

Hello everyone,

I'm doing this small DB for my real estate company, we're using at the moment "Jade Tools" but due to the many problems and bugs it has, we've decided to switch to Access.

I have created 3 tables: properties, inquiries, contacts

properties: id, location, price, type, owner
inquiries: id, type, location, price, client
contact: id, type, phone, email

I had already made the relationship between 'price' and 'location' so that they fetch the data from the same source (another table).

What I wanna do is match the "Inquiry" to the "Properties"

Let's say, I have a client who wants to buy an Apartment in Lebanon for $300,000 - I want it to automatically look up the properties and find the matches to his request.



I'm new to access so there are alot of things that I don't know, I don't know whether this could be achieved via a query or a report. Some advise would be great.

[NTC]

You will have an Inquiries form based on the record of the inquiry table. This form will of course have controls that have values for price, location. Set that up and have it open with a record showing. shrink it out of the way.

Then make a new query on the Properties table. Add criteria that will filter by 'calling' the controls from the InquiryForm. You will use this syntax for example in the location field: Forms!InquiryForm.LocationField

Once this query is set up and works correctly - that's the fundamental data and if you didn't care about looks you could just manually open up the query.

But getting into design user friendliness - then you can make a form based on this query . - - call it InquiryPropSubform.

Then you have some choices as to how you want to present this subform. The simplist thing is simply to open the subform manually.

You could make your InquiryForm into a tabbed form and insert the subform in its own tab or you could just insert it into the InquiryForm directly.

There is no cross referencing field between these two record sets (Inquiries and Properties) - that is ok. When inserting a subform it presumes there generally is a cross reference - - and it is going to presume it to be 'ID' because you have the same named field in both - - you need to delete this default when inserting the subform.