Hi folks,

I've had to recently do my entire database over again because I majorly
fudged up on a relation, rendering the db entirely useless. So, in order
to avoid something like this happening again, I wanted to run this by
you lads, maybe you have an idea how to get this working:

Table #1: People

Contains people's names, addresses and the like.

Table #2: Boxes:

Contains box numbers, these need to be entered without assigning them at
the same time.

Form #1 (operating on table #1): Appointments:

Allows users to enter appointments for the people.
Here, users should also be able to assign boxes to people (1:1).

How should the form and the relationship be set up? Setting the
relationship 1:1 (using people-ID as primary key in both tables) caused
the initial crash, possibly because of a setting that was wrong somehow
- other tries have yielded complaints about integrity.

Ideally, the end result would be a report containing name, address and
apppointment from table #1 and the assigned box number, all entered
through form #1.

An added bonus would be if the form would also be able to pick entries
from table #2 through a query. If a certain identifier in table #1 is
set A , a combobox would contain one query A, if set to B, query B.

Any help would be much appreciated!

Berti

Berti Ferranti wrote:
OK, I don't know much about OpenOffice or 'boxes' so I'm not quite sure
if I even understand the question. Anyway, my answer is not very
sophisticated:

The tables could look like this:

contacts(contact_id*,contact_name,contact_address)

boxes(box_id*,box_no)

appointments(contact_id*,box_id*,datetime*)

* = Primary Key. Note that the Primary Key on the appointments table is
comprised of all three pieces of information - on the assumption that
nobody can be in the SAME place at the SAME time TWICE! This does not
however preclude the box being, as it were, 'double-booked'. To do
that, the PRIMARY KEY would be comprised solely of the box_id and the
datetime. However, that still wouldn't prevent a contact being
'double-booked' - but I'm going to suggest that that's beyond the scope
of this answer!

I don't know how queries are constructed but let's just assume they're
a bit like MySQL:

SELECT a.datetime,b.box_no,c.contact_name,c.contact_addre ss
FROM appointments a
LEFT JOIN boxes b ON a.box_id = b.box_id
LEFT JOIN contacts c ON a.contact_id = c.contact_id;

Your form simply populates the appointments table from information
supplied by the other two tables.

HIH

zac.carey (AT) gmail (DOT) com schrieb:

Hi Zac!

'Boxes' are just plain boxes that need to be put into somebody's house.
I basically need a way to be able to plan putting said boxes into
people's houses at a certain point in time. The main question is: how do
I enter the relations between people, boxes and appointments once to
have them show in a combined query.


Actually, appointments was supposed to be a form, with the appointment
saved in the 'contacts' table. It isn't ideal as a storage for the
relation anyway, because the appointments obviously expire, whereas the
associations between boxes and people should remain accessible...

Hmm, I could use SQL to build a query, but I'd need to get the field
names right - and I need to get the proper relations first...


Very much appreciated!

Berti

If those were the tables then those would be the fieldnames!!! As for
the relations, they are established in the query itself - 'a.box_id =
b.box_id'.

Transactional databases often have some kind of 'orders' table for
establishing the link between a contact and an item. You could have
something similar, maybe a 'consignments' table if the items aren't
actually 'ordered' - 'appointments' really is a table though, no
matter that it might (just like the boxes and contacts tables) be
populated by a form.

As I said, I really know nothing about OpenOffice - I know that it uses
xml to store these little bits of information but that's about it. FWIW
I do know, however, that you could implement this whole thing in php
and mysql over a long weekend (including the self-populating forms) -
with the help of a couple of decent books on the subject and some
online tutorials! Then you'd have the bonus of having the whole thing
accessible over the web and direct to your delivery guy's blackberry.
OK, maybe a little more than a weekend, but you get the idea.