Utter, total beginner - query

  • Thread starter Thread starter vstclair
  • Start date Start date
V

vstclair

Hi, I have a basic design query.
I have to design a property agent database for a website. For each
property there is a long list of variables (for renovation, part
renovation, if on main services, which services, no. of main rooms,
several location columns, etc, plus which mailing list clients have
been sent the info).

Site users will search by price range, more than one location type,
property condition, type, land area, no. of rooms, and etc), and I
hope will get a page showing info on properties that fit their bill.

On the site, each property appears on a page with other properties in
the same price range, and also has a page of larger images and more
text.

There will be a potential buyer mailing list, with records of which
properties they have been sent details.

So far all this is wishful thinking!

I have a main properties info database, with all the property details
on it. I have a table for the mailing list clients.

The main property table has loads of columns. Can I break this down
into smaller tables without making data entry a problem for the
website owner, for whom I will have to design one easy-to-use for
putting new property details onto the website?

Have searched internet for a tutorial on the relationship between
having lots of linked tables and the data entry form, in vain.

Any advice would be appreciated,

Vivienne
 
The main property table has loads of columns. Can I break
this down
into smaller tables without making data entry a problem for the
website owner, for whom I will have to design one easy-to- use for
putting new property details onto the website?
Click to expand...

There is no problem designing an easy-to-use form for your
user, grouping information from multiple tables, normally
either by basing the table on a query rather than a single
table, or, for tables that have a one-to-many relationship
(such as Customers and Orders), using a subform for the
many side, placed on a main form, and linking by a foreign
key.

More importantly, the key objective in design is to have a
table contain fields that describe properties of
one "thing". For example, I suspect your current
spreadsheet contains columns that pertain to a particular
Property, a particular Listing Agent, and others. I
suggest you go through each of them and catagorize them by
the type of "thing" they describe. This will suggest what
tables you need.

For example, I suspect you will need the following tables
as a minimum:

Agents
-------------
AgentID Autonumber (key field)
CompanyName
Address
Phone
Website
etc.

Properties
----------
PropertyID Autonumber (key field)
Address
NumberofRooms
SquareFootage
Etc.

Special Features
----------------
SpecialFeatureID Autonumber (key field)
Description

PropertySpecialFeatures (detail table for Properties)
-----------------------
PropertySFID Autonumber (key field)
PropertyID (foreign key to Properties)
SpecialFeatureID (foreign key to Special Features)
Note


Listings
---------
ListingID Autonumber (key field)
Listing Agent (Foreign key to Listing Agent table)
DateListed

Have searched internet for a tutorial on the relationship between
having lots of linked tables and the data entry form, in
Click to expand...
vain.

There are many good books on Access, or you may do an
Internet search on "Relationships", "Forms Based on a
Query", or "Data Entry". That ought to turn up a lot of
useful info.

Good luck.
HTH
Kevin Sprinkel
 
Access and SQL use an Entity-Relationship model. Table definition is the
first step. "Data normalization" is the subject dealing with definition of
tables. Two types of tables contain a) entities and b) relationships. An
entity such as "People" (tblPeople) has an autonumber PK field and
attributes (fields) such as Title, FName, MName, LName, and Suffix. Your DB
might have a need to further describe people such as agents or prospective
buyers. This "role" is a separate type of entity that belongs in a separate
table. You don't want a table for buyers and a table for agents. Once you
set up tblPeople and tblRole (which can have a many:many relationship) you
need a third table containing a row for each relationship between entities,
such as Mary (person) is a prospective buyer (role). This table consists
FK references to the PK fields in the entity tables. The second step is
designing forms. One of your other entity tables is tblProperty. There's a
one:many relationship between properties and potential buyers. On your main
form for Property you want to see many potential buyers. Buyers will be in
a subform on the Properties form. Properties is the Parent, Buyers is the
Child. One side:Parent:main form, Many side:Child:sub form. In your forms
use combo boxes to select data from the entity tables (the cboBox Rowsource)
and store the PK of the selected item into the relationship table as its FK.

As you look at this Access form, each time you select a different property
you see all the prospective buyers who are interested in it. tblProperty,
tblPerson (filtered by role), and tblProperty-Person (stores data about
which buyers are interested in which properties) are the tables involved.
You can add, change or delete rows in the entity or relationship tables on
the fly with appropriate code behind forms. From the subform (relationship
table) a pop-up form can be used to add new buyers to tblPeople if they
aren't already there. Closing the pop-up form takes you back to the subform
where you started.

E-R modeling leads one to discover "patterns" of similarities between
various applications. Almost every DB contains some information about
"people". One of the attributes about a person is that person's phone
number. That's fine if there's only one phone number. But as soon as we
discover that it's possible for a person to have a home phone, a work phone,
a fax number, and a cell phone, we find that "phone" isn't an attribute of
"person", it's another entity. This means we need to set up tblPhone,
tblPhoneType, and tblPerson-Phone, along with parent forms to maintain
entity data and child forms to display relationship data to handle the
possibility that amny people can have many phones.

These are the building blocks necessary to be able to develop a database
that can be expanded without having to start over again each time the user
says: "Could you make it do THIS?"

When databases don't work, the first thing to check is whether the tables
are normalized. A database schema broken down into entities and
relationships (see the Access Relationship window) has to exist first. The
next problem is how to set up forms, controls, and event procedures so that
forms can be used to get data into and out of tables in the most intuitive
manner for the user.

New would-be developers have a tendency to resist the E-R model without
first absorbing the abstraction between their problem and the model or the
mechanics of data normalization and formscoding. There's a tendency to want
to pound square pegs into round holes, which is why it's frustrating. So,
to learn database development, it's a good idea to start and finish a very
simple database before attempting a much more complex database. It takes
practice. Which is probably not what you want to hear at the moment.

Model your data, thinking about how your user is going to navigate through
it.
Layout all the necessary tables.
a) entities
b) relationships between entities
Build forms and subforms. Forms will frequently use queries to select,
sort, and filter data stored in tables or to join multiple tables.
Start entering data via the forms, or use append queries to load existing
bulk data into entity tables.
Build reports as needed.
 
Rolls, hi, and thank you.
Though that was a bit beyond me!
I can't even afford a second-hand book right now, but will try
searching for data entry on the web.
I suppose, to put it simply, can I have one data entry form that
automatically enters data into all the child forms as well as the
parent?
Each property has eleven categories of associated information plus the
list of which clients were sent the details. There are no agents, just
prospective buyers.

I battle on, will get there eventually!

Viv
 
Many thanks Kevin, I gradually see through the fog in my brain. Years
ago I used to be able to do this sort of thing. Had ME for 8 years &
much memory has gone!

The info is just properties and prospective buyers. The properties
have about 11 categories of information - the site owner will need one
data entry table for that, and one for the client details.
As I say below, cannot even afford second hand books right now, so
your help is great.
Many thanks
Vivienne
 
Many thanks Kevin, I gradually see through the fog in my brain. Years
ago I used to be able to do this sort of thing. Had ME for 8 years &
much memory has gone!

The info is just properties and prospective buyers. The properties
have about 11 categories of information - the site owner will need one
data entry table for that, and one for the client details.
As I say below, cannot even afford second hand books right now, so
your help is great.
Many thanks
Vivienne
 
Actually it becomes clear and simple if you understand the relational model.
If you're using a subform to add a record to store a relationship between
two entity tables (such as John Smith is a possible buyer of 123 Smith
House) the subform has two combo boxes that use a rowsource = tblPeople &
tblProperty. The subform thus adds a record to tblPeople-Property
(relationship table). But you're not going to use that subform to maintain
the entity tables tblPeople or tblProperty if you desire to add a new person
or property to the DB. In each case you need a separate form with a
rowsource equal to the respective entity table that you want to maintain.
Usually these are pop-up forms. Let's say we have the two entities People
and Property and the one relationship table. An existing buyer becomes
interested in an existing property. So we use the subform to add a new row
to the relationship. But if we have either a new buyer or a new property,
when we try to associate the two on the subform we will have a missing item.
At that point we need to jump to the form to add a person and/or property
(entity) then return to add the row to the relationship table.

If your schema has a lot of different child relationships to the same
parent, you can put multiple subforms onto the parent form. If there are
multiple parents you probably want either multiple main forms or a tabbed
form so that the user has no more than one parent entity in view at one
time.

You can build this functionality with the wizards, without writing code.
 
Rolls,
I found your explanation of relationships to be very helpful. I too
was having difficulty. I am trying to do a database for a jail in my
area. We have about 400 inmates. We get roughly 30 new per day and
abut 30 move out. So our counts stays at 400 but the yearly turnover
is quite high.

I want to be able to db the 20 or so programs we deliver to them,
record their work program and pay, and db their progress logs etc.

I was not able to make the relationships work until I finally read
your explanation. I am much closer now. I am going to need to learn
VBA though....
Any suggestions?

Nick.
 
Back
Top