Results 1 to 7 of 7
Thread: Access database design help
-
04-21-2005, 10:40 PM #1Web Hosting Master
- Join Date
- Sep 2003
- Posts
- 1,215
Access database design help
Hey everyone, I haven't used access for a while and i'm struggling to work out how to solve this problem.
Ok, i'm building a database for an estage agent. Main tables will be the property table (address, bedrooms, garage etc) purchaser table (details of the purchaser such as name, address, telephone number) vendor table (details of the person selling the property).
Ok, so the person who is interested in buying a property registers their details with the estate agent (walk into a shop, the staff type in their details) and then they are added to the system, they must also register details of the property they are interested in e.g 4 bed, semi. (not sure where to store this information)
Same principle applys with the vendor, they register their details and also the details of the house they are selling.
The problem I have is, the system should provide information on any new properties that are added to the system that match the potential purchasers criteria. E.g I walk into the shop and the staff add my details, e.g I want a 3 bed house, no garden, 2 reception rooms. It should then be able to query the database and pull out any suitable matches.
I'm just really stuck on how to go about doing this....Any help would be appreciated.
-
04-21-2005, 11:02 PM #2Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
have you come up with all the table properties yet?
it would be easier to see your tables.
---
i came up with mine like this, i am sure someone might want to revise it.
Properties (pid, address, bedrooms, garage, ....) --> id is primary key for properties
Purchaser (purid, name, address, phone ) --> purid is primary key for purchase
Vendor (vid, ...detail the vendor ) --> vid is the primary key for the Vendor
Purchaser_Order (oid, purid, pid, vid) --> oid is the primary key for order, purid is foregin key for purchaser; pid is the foregin key for Properties; and vid is the foreign key for Vendor.
Vendor_Offer(vo_id, pid) --> vo_id is the primary key, and pid is the properties that the vendor is saling.
---
from here, we can use LEFT JOIN, etc to pull out the search based on different criteriaLast edited by orbitz; 04-21-2005 at 11:05 PM.
-
04-21-2005, 11:08 PM #3Web Hosting Master
- Join Date
- Sep 2003
- Posts
- 1,215
Yes, I have the data dictionary completed, I have a list of the various entities/fields and such that the database needs.
I can't seem to work out in my mind how to create the query/relationship though that will enable a end user to match the purchaser with the property.
The purchaser is going to need to input their personal info and then the type of property they are after. Would these be in the same table or not? The vendor is going to have to put their details and property they're selling in too.
The question is, how can I get access to query the purchasers criteria agaisnt the properties for sale?
Just need a kick in the right direction! I mean it's easy to do queries like...what purchasers are interested in 3 bedroom houses, but that's not what i'm aiming for.
-
04-21-2005, 11:22 PM #4Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
Originally posted by talkfreelance
[B]
The purchaser is going to need to input their personal info and then the type of property they are after. Would these be in the same table or not? The vendor is going to have to put their details and property they're selling in too.
The question is, how can I get access to query the purchasers criteria agaisnt the properties for sale?
Right now, without your data dictionary, I can point you to this website, there is a tutorial on how to pull data from many tables.
http://www.w3schools.com/sql/sql_join.aspLast edited by orbitz; 04-21-2005 at 11:26 PM.
-
04-22-2005, 01:34 AM #5WHT Addict
- Join Date
- Jan 2004
- Location
- Texas, United States
- Posts
- 131
Hello:
I know this will be of great value for you:
http://www.databaseanswers.org/data_models/index.htm
Thanks!
-
04-22-2005, 02:14 AM #6Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
awesome info!
thanks erod84 for sharing
-
04-22-2005, 08:17 AM #7Web Hosting Master
- Join Date
- Sep 2003
- Posts
- 1,215
Thanks for your help...i'm still stuck
I've looked into LEFT JOIN but where do I enter the query? I need to do it through access and it needs to be automatic. Every purchaser will require different things, so whatever they specifiy needs to basically generate a new query that can be run at the touch of a button to pull any suitable matches out.