Possible Relationships Design Problem

[Michelle]

I am having trouble with the design of my database. I am
also having a problem attaching my dbase file i'm sorry
but you cant look at it! Anyway .... my problem is this:
when I do a query or form it wont allow me to change
existing data or add new data!

I have designed the database as an employment database.
Basically I want a listing of Employment agencies in
Sydney, their website details, their head office details,
their subsidiary offices details, and the contact persons
details at each local office. I also want to be able to
categorise each company into for
example "Administration", "IT", "Tutoring" depending on
what kind of employment they specialise in. Some agencies
may have only Administration jobs on offer, however other
agencies may offer Administration jobs and IT jobs on
offer- so I need to be able to enter these multiple
categories on the database.

So far I have created approx 5 tables:

National- contains all the national contact details for
the company eg website, national ph no. or email, my
rating of the company as a whole etc. EG Adecco,
www.adecco.com.au, (e-mail address removed), 4 out of 5 rating.
Local- contains the contact details: address, ph no.,
email etc of a local office
EG. 1 York St Sydney City, (e-mail address removed), and 1
arthur st Parramatta, (e-mail address removed), and 1
elizabeth St Chatswood, (e-mail address removed). etc. etc.
Local Employment Consultant- contains the Employment
Consultants contact details for a particular local office.
Eg. Mary Black, (e-mail address removed), ph
9999 4444, and John Wright,
(e-mail address removed) ph 9450 5555 etc.etc.
(I want to put this in a separate table so if the staff
contact changes I can simply update that record only-
rather than having this data embedded in the above local
office table. Furthermore some local offices may have 2 or
3 Employment Consultants that I can contact for employment)
Category- I have a number associated to each category eg 1-
Admin, 2-IT, 3- Child Care.etc (I want to be able to do a
query on Admin agencies only, or IT agencies only).
National/Category- I created this table to link the
National table to the Category table- because I need to be
able to choose multiple categories for some of the
Employment Agencies.

I am not sure if I have related these tables correctly. I
am wondering whether I have related them in an inefficient
way, or incorrect way, so that I cannot enter new data or
change existing data in a query or form.

Any advice is welcome!

 

[Tim Ferguson]

I am having trouble with the design of my database. I am
also having a problem attaching my dbase file i'm sorry
but you cant look at it!

Don't bother attaching binary files to posts in groups like this: not
many people will voluntarily open attachments from people we have never
met and have not asked for... If you really cannot describe the salient
details in text (see other posts here for simple methods), the best way
to demonstrate a database is to take a screen capture of the
relationships window, post it on a website, and post the url here.

Anyway .... my problem is this:
when I do a query or form it wont allow me to change
existing data or add new data!

I am afraid I have not worked all the way through the description below.
Nevertheless, there are some things that worry me about the design. You
seem to need to address a number of different entities -- Companies,
Offices, Consultants, Categories -- each of which should be in a table of
its own. Don't confuse table design with procedures: tables should be
designed round what the actual real-life entities are, while procedures
(i.e. updating local details vs national details) should be controlled by
forms. If you can describe in a bit more detail what you are trying to
model and what you are planning to achieve, we may be able to help.

HTH


Tim F

 

[Michelle]

Fair enough about attaching files- as you may have guessed
this is the first time i have used a newsgroup?!
So i have got a copy of my database design on a website:
here is the url...
http://www.databaseanswers.org/data_models/employment_agenc
ies/index.htm
What i am trying to work out, is why when i do a query on
all the tables- ie i do a normal query, i add all the
tables, i double click on all the stars (to add all dbase
fields into my query) and then when i run it, i can see
every row and field, every entry, but i cant edit it or
add a new entry...the error message in bottom left cnr
says 'This Recordset is not updatable'. I want to be able
to update and add new records in 'one' form based on
this "All" query, but i cant when the recordset is not
updatable.
A friend mentioned to me today, that there are other types
of queries that should allow me to still have all my
tables in the one form where i may edit data and add new
rows.

Kind regards,
Michelle
P.S. is this newsgroup Australia wide only, or does it
cover other countries?

 

[Tim Ferguson]

So i have got a copy of my database design on a website:
here is the url...
http://www.databaseanswers.org/data_models/
employment_agencies/index.htm

Okay, thanks for that. Schema looks pretty straightforward and no obvious
problems.

What i am trying to work out, is why when i do a query on
all the tables- ....
the error message in bottom left cnr
says 'This Recordset is not updatable'.

I'm not surprised! There are rules for predicting whether a query will be
updateable or not, but essentially the more tables that are involved, the
more likely it is to be non-updateable. It's about whether the db engine
can identify which field in which record is to be updated, and as joins
get more complex the required information gets lost.

For example, if you count the rows in your query, you'll see that there
is a record for every address for every local office of every agency; and
even these are multiplied by the number of Agency_Categories; and if an
Agency has two addresses then the whole lot gets double again!

To illustrate what I mean, consider a much simpler db with Rooms related
to Inhabitants related to Equipment and to Clothing:

+-< Equipment
Rooms --< Inhabitants -+
+-< Clothing


RoomNo FirstName Description Item
====== ========= =========== ====
1 Eric Ruler Shirt
1 Eric Pencil Shirt
1 Eric Ruler Trousers
1 Eric Pencil Trousers
1 Basil Ruler Shirt
1 Basil Compass Shirt
1 Basil Felt Tip Shirt

If you try to change Basil's shirt to a Vest, then the recordset will
have to update three rows (at least); and if you try to add another item
of clothing, it will have add several lines to the query. This is not the
way recordsets work -- there may be db engines that can deal with this
level of complexity, but usually they will leave it up to the developer
to do something a bit more straightforward. Like updating one record in
one table at a time!

I want to be able
to update and add new records in 'one' form based on
this "All" query, but i cant when the recordset is not
updatable.

You are going to have to think a little bit harder about your user
interface. The judicious use of subforms will help to make some of the
one-to-many relationships usable, but remember that Agencies -
LocalOffices - Contacts has two levels of subdata. You might want to
think about breaking out some of the tasks into dialogs (i.e. other
forms) like "Add Category" or "Add Address" and so on.

It would be wonderful to throw and entire schema at Access and have it
draw up an intelligent interface, but I'm afraid even Bill Gates ain't
that clever!!

P.S. is this newsgroup Australia wide only, or does it
cover other countries?

Worldwide: I'm in the UK, many here from NAmerica etc.

All the best


Tim F