Many To Many Relationship Query?

[perrydaniel]

Hi

I have created a database which consists of three tables
1.Client
2.Property
3.LinkClientProperty

My Client table holds info about the client and their requirements. i.e 3 bed house worth 150k (ClientID key)

My Property table holds info about the properties which are available to buy (PropertyID Key)

My third table holds the keys ClientID and PropertyID

My question is this, I can write a query which looks for anyone looking for a 3 bed house worth 150k, but how can i display in the clients table that a property brochure was sent to a client? And also in the property table a list of clients that were sent a particualr brochure.

I believe my tables are setup ok, and when i populate the link table manually everything seems ok. Hope this makes sense. Any help would be great.

 

[OnTheFly]

I am not sure what your third table is tracking but what you should be tracking there is the properties matched up with the clients and I would add a boolean field that is BrochureSent. In that table, then, you would enter the client ID and all the property IDs that you matched to them. If a brochure was sent then you would make your BrochureSent True.

I have no idea how you are matching properties to clients so I can't tell you how to get this information into the third table but once the information is entered you should beable to create queries that show what property brochures were sent to each client and what clients were send a certain property brochure simply by linking the three tables together.


Hope this helps.

OnTheFly

 

[perrydaniel]

OntheFly,

Thanks for your reply, the part that I am having the problem with is, say a new property comes in (New to the market). I then run a query looking at the Clients table to see which clients maybe interested in this property. I will have a list of say 50 clients, how would this best be managed, and how.

I also have the other side of things, where I have a property table and a new client (Enquirer), they maybe interested in 5 of the existing properties. Hope I am making sense, I thought the best way to show this was via three tables as mentioned. Dont really understand where the Boolean comes in!

 

[lespaul]

a new property comes in:

add a new record to table property

run a query against the client table:

SELECT * from clienttable where {search criteria matching property}

now you have a list of all the people who are interested in this property, add a record for each person and property in LinkClientProperty (you need to add a boolean field to this table and name it BrochureSent), when you insert all these records into LinkClientProperty, set the value of BrochureSent to "FALSE". Once the process runs or someone manually sends off the brochure, you will need to have a process that changes BrochureSent from "FALSE" to "TRUE".

Leslie

 

[perrydaniel]

Lespaul,

Great this is what i needed, do I have to manually update the linkClientproperty? Or is there a way this can be updated via the result of the query? Thanks again

Perry

 

[lespaul]

you can update with a query:

UPDATE linkClientProperty SET BrochureSent = True where propertyID = [New property ID]

Leslie

 

[perrydaniel]

Les,

Do I have to add UPDATE linkClientProperty SET BrochureSent = True where propertyID = [New property ID] onto the end of the first query,

i.e the query that determined the clients that are interested in new property? Sorry but this is very new to me!

Thanks

Perry

 

[lespaul]

Are you trying to do all this from a form or what?

With the query above, the user is going to be prompted to enter the property id, is that what you want?

I would expect you to have a form that allows users to enter a new property, once the property is entered a query should run automatically to determine the people interested in that property. Then some other process would need to occur in order to indicate that the brochure had been mailed. That's when you would need to run the update query.

why don't you tell us a little more about what you are doing?

Leslie

 

[perrydaniel]

Sorry for not being over clear, but fairly new to this. As you have said I have a form(s) which look after the property and the Enquirer. Both of which are tables. When a new property comes in this as you say will be entered into the property table via the form. The very same if a new enquirer calls, they will be added to the Client table via the form.

At this point I need to be able to select a property and find out from the clients table who would be suitable to be sent a brochure on this property. (Problem 1)

Once the selected list have been found these Clients would be added to the link table with the clientID and the PropertyID.(Problem 2)

Finally looking at the link table (Which will now be populated) I will have a field that shows Send Brochure (Yes/No) and BrochureSent (Yes/No).(Problem 3)

I know this is probably not the best way to do, but it will certainly solve a lot of problems for us...

Thanks a million for your time and patience