Re: Relational database design

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

unfortunately learnt the hard way
in terms of using lookup fields in tables......

well, a lot of us learned that lesson the hard way - and now we warn
everybody who crosses our paths! :)


"Anthony" <awmorgan@xxxxxxxxxxxxxxxxxx> wrote in message
news:1152532977.612050.66830@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks tina i will give that a shot.

I do have a form for data entry but unfortunately learnt the hard way
in terms of using lookup fields in tables......

Thanks again.

Cheers, Anthony

tina wrote:
yes, you can link a "supporting" table to more than one field in the
same
"data" table. in the Relationships window, add the supporting table
twice;
the second table will have a "_1" appended to the end of the name, which
is
fine.

btw, i hope that 1) you provided a form for you user(s) to enter data in
the
table, because data entry should not be done directly into forms, and 2)
that you did not use Lookup fields in your data table - if you did,
suggest
you get rid of them immediately. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.

hth


<awmorgan@xxxxxxxxxxxxxxxxxx> wrote in message
news:1152522590.897281.124600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello all

Hoping somebody will be able to assist me with a problem that has me
stumped. I have developed a database for recording 'alcohol related
incidents'. The database relates to a manual form completed when a
person attends the incident. The form records a information on a
number
of variables, including standard person details (ie sex, DOB etc),
details on incident location and on last place of drink. The forms are
then submitted for data entry to a single person, in each district.

My problem is this. At present the form records information on the
name
of the premise and suburb where the incident took place, and
information on the premise and suburb where the person consumed their
last drink. These can be the same, can be left blank or may be
identical, depending on the circumstances surrounding the incident.

In my database I have a single form which aims to replicate the manual
form used when attending the incident. This form (frmARIF) is based on
the table tblARIF. There are a number of combo boxes in this form,
that
allow for drop down selection. In the case of incident location, the
combobox refers to the tblINCSUBURB for the suburb in which the
incident takes place, and tblINCPREMISE for the name of the premise in
which the incident occurred. Both tables have been populated by
preexisting tables, so in most cases it is a matter of making the
relevant selection.

Similarly, for the location of place of last drink, the suburb of last
drink is selected using a combobox based on the table tblDRINKSUBURB,
and the premise of last drink is selected using a combobox based on
tblDRINKPREMISE. Again, both tables are popualted and enable easy
selection.

tblINCPREMISE and tblDRINKPREMISE contain identical data, as do
tblINCSUBURB and tblDRINKSUBURB. However, I had it in my mind that in
both cases I couldn't refer to the same table for both incident
location and place of last drink, which was supported by the fact that
I could not enforce referential integrity.

There are some 4000+ premises, and around 300 suburbs in the database
(x2 because both tables are duplicated), which has made the database
quite large in size. In addition, if a premise is not in the database
(ie known under different name, newly opened etc) it has to be added
into both tblINCPREMISE and tblDRINKPREMISE - which of course is quite
cumbersome.

I guess my question comes down to this - can I refer to the same field
in another table twice to populate a single field in my primary table,
or is this likely to cause me a lot of problems. Other than the
problems with size (which slows it down because it is on a network)
and
adding new premises, the database is up and running and seems to be
pretty user friendly. I was satisfied with my resolution but
discussions with another colleague caused me to reconsider.

Apologies if my explanation is unclear. If you think you can help but
need more information please let me know.

Much appreciated.

Cheers, Anthony




.

Relevant Pages

  • Re: Relational database design
    ... in terms of using lookup fields in tables...... ... I have developed a database for recording 'alcohol related ... details on incident location and on last place of drink. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relational database design
    ... I have developed a database for recording 'alcohol related ... details on incident location and on last place of drink. ... tblINCPREMISE and tblDRINKPREMISE contain identical data, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relational database design
    ... I have developed a database for recording 'alcohol related ... details on incident location and on last place of drink. ... tblINCPREMISE and tblDRINKPREMISE contain identical data, ...
    (microsoft.public.access.tablesdbdesign)
  • Relational database design
    ... I have developed a database for recording 'alcohol related ... details on incident location and on last place of drink. ... tblINCPREMISE and tblDRINKPREMISE contain identical data, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Very slow query
    ... something about your tables design. ... it's that simple - though i never create Lookup fields ... create a new blank database and immediately turn OFF the Name Autocorrect ... reports fall into the user interface category; ...
    (microsoft.public.access.queries)