I've searched through these forums and read alot of discussions regarding not
using a lookup in a table. Instead, one should put that information in
another table and then use a combo box (using the wizard) to create that
field.

I'm getting confused trying to figure out a few things with this method and
I'm hoping for some direction.

My database will be used to capture enforcement information regarding a
particular crime in my area. This crime involved establishments, the
criminals and certain information related to the criminals. Rather than lay
out all of the specifics, if I can figure out how to relate two of the tables
together, I should be fine. So, I would assume I need a table for the basic
information:

Tbl Incident
Date of Offense
Time of Offense
Day of Week
Establishment Name
Subject Name

Since I will be dealing with several establishments, I'm assuming I'd want
this in a seperate table. I'd also want to include an address and a specific
area for this:

Tbl Establishment
Establishment Name
Establishment Address
Establish Area

I know I should be connecting the Tbl Establishment with the Tbl Incident
and there should be like titles in both of the tables on which to match.
I've looked at databases created by those with alot of knowledge in Access
and I notice there's usually an ID field in each table (EstablishmentNameID -
for instance). This is where the confusion is coming in: Should
EstablishmentNameID be the primary key in this table as opposed to ID with
autonumber? If it's not the primary key, I get an error message that there's
no unique index between the two.

Second point of confusion, assume the Establishment table merely contained
the name of the establishment. By using the wizard, I put a combo box on the
form to capture just the name and then tell it to store the establishment
name in the Incident table (under Establishment Name) and that works fine.
However, we have many of the same establishment names with different
addresses and I need the address and areas included. I'm assuming the combo
box is the wrong choice with this, as it only allows me to store the
information from 1 field in my Incident table.

I apologize for the lengthy post and I appreciate any assistance I can get.
Thanks in advance.

Lee Ann

I think you missed the suggestion ...

You don't use the wizard to create the lookup in a field IN THE TABLE. You
can use a wizard to create a control IN A FORM. That's the preferred
approach.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Lee Ann" <(E-Mail Removed)> wrote in message
news:7BF869C8-DEFB-4D04-B41B-(E-Mail Removed)...
> I've searched through these forums and read alot of discussions regarding
> not
> using a lookup in a table. Instead, one should put that information in
> another table and then use a combo box (using the wizard) to create that
> field.
>
> I'm getting confused trying to figure out a few things with this method
> and
> I'm hoping for some direction.
>
> My database will be used to capture enforcement information regarding a
> particular crime in my area. This crime involved establishments, the
> criminals and certain information related to the criminals. Rather than
> lay
> out all of the specifics, if I can figure out how to relate two of the
> tables
> together, I should be fine. So, I would assume I need a table for the
> basic
> information:
>
> Tbl Incident
> Date of Offense
> Time of Offense
> Day of Week
> Establishment Name
> Subject Name
>
> Since I will be dealing with several establishments, I'm assuming I'd want
> this in a seperate table. I'd also want to include an address and a
> specific
> area for this:
>
> Tbl Establishment
> Establishment Name
> Establishment Address
> Establish Area
>
> I know I should be connecting the Tbl Establishment with the Tbl Incident
> and there should be like titles in both of the tables on which to match.
> I've looked at databases created by those with alot of knowledge in Access
> and I notice there's usually an ID field in each table
> (EstablishmentNameID -
> for instance). This is where the confusion is coming in: Should
> EstablishmentNameID be the primary key in this table as opposed to ID with
> autonumber? If it's not the primary key, I get an error message that
> there's
> no unique index between the two.
>
> Second point of confusion, assume the Establishment table merely contained
> the name of the establishment. By using the wizard, I put a combo box on
> the
> form to capture just the name and then tell it to store the establishment
> name in the Incident table (under Establishment Name) and that works fine.
> However, we have many of the same establishment names with different
> addresses and I need the address and areas included. I'm assuming the
> combo
> box is the wrong choice with this, as it only allows me to store the
> information from 1 field in my Incident table.
>
> I apologize for the lengthy post and I appreciate any assistance I can
> get.
> Thanks in advance.

I recommend that the primarry key of 99.99% of tables be the table name with
"ID" at the end. Make the primary key autonumber. Do this and you will never
go wrong! So in your case the primary key of your incident table would be
IncidentID and the primary key of your establishment table would be
EstablishmentID. Further, It is likely that a criminal will over time be
involved in more than one incident. So I recommend that you also have a
criminal table. This will allow you to easy create a rap sheet any time for
a particular criminal. I don't know what your Area field is but most likely
you also nee an Area table.Finally it is recommended practice not to use
spaces in any Access object names (tables, fields, queries, forms reports,
etc). So now your tables should look like:
TblCriminal
CriminalID
FirstName
LastName
<criminal fields>

TblArea
AreaID
<area fields>

TblEstablishment
EstablishmentID
EstablishmentName
EstablishmentAddress
EstablishmentCity
EstablishmentState
EstablishmentZipcode
AreaID

Tbl Incident
IncidentID
DateofOffense
TimeofOffense
CriminalID
EstablishmentID

Note that I did not include day of week. Day of week can be easily
calculated from DateOfOffense.

Post back if you have any questions.

Steve
(E-Mail Removed)





"Lee Ann" <(E-Mail Removed)> wrote in message
news:7BF869C8-DEFB-4D04-B41B-(E-Mail Removed)...
> I've searched through these forums and read alot of discussions regarding
> not
> using a lookup in a table. Instead, one should put that information in
> another table and then use a combo box (using the wizard) to create that
> field.
>
> I'm getting confused trying to figure out a few things with this method
> and
> I'm hoping for some direction.
>
> My database will be used to capture enforcement information regarding a
> particular crime in my area. This crime involved establishments, the
> criminals and certain information related to the criminals. Rather than
> lay
> out all of the specifics, if I can figure out how to relate two of the
> tables
> together, I should be fine. So, I would assume I need a table for the
> basic
> information:
>
> Tbl Incident
> Date of Offense
> Time of Offense
> Day of Week
> Establishment Name
> Subject Name
>
> Since I will be dealing with several establishments, I'm assuming I'd want
> this in a seperate table. I'd also want to include an address and a
> specific
> area for this:
>
> Tbl Establishment
> Establishment Name
> Establishment Address
> Establish Area
>
> I know I should be connecting the Tbl Establishment with the Tbl Incident
> and there should be like titles in both of the tables on which to match.
> I've looked at databases created by those with alot of knowledge in Access
> and I notice there's usually an ID field in each table
> (EstablishmentNameID -
> for instance). This is where the confusion is coming in: Should
> EstablishmentNameID be the primary key in this table as opposed to ID with
> autonumber? If it's not the primary key, I get an error message that
> there's
> no unique index between the two.
>
> Second point of confusion, assume the Establishment table merely contained
> the name of the establishment. By using the wizard, I put a combo box on
> the
> form to capture just the name and then tell it to store the establishment
> name in the Incident table (under Establishment Name) and that works fine.
> However, we have many of the same establishment names with different
> addresses and I need the address and areas included. I'm assuming the
> combo
> box is the wrong choice with this, as it only allows me to store the
> information from 1 field in my Incident table.
>
> I apologize for the lengthy post and I appreciate any assistance I can
> get.
> Thanks in advance.

>>Date of Offense - Time of Offense - Day of Week
Use single DateTime field and derive the parts as needed.

>>Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table.
No, one table for incidents.

Use EstablishmentNameID as an Autonumber primary key field in the
TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign
key in the incident table.

Create a one-to-many relationship between TblEstablishment and incident,
selecting Referential Integerity and Cascade Update.

Use a combo on your incident form to pick the Establishment from
TblEstablishment.

--
Build a little, test a little.


"Lee Ann" wrote:

> I've searched through these forums and read alot of discussions regarding not
> using a lookup in a table. Instead, one should put that information in
> another table and then use a combo box (using the wizard) to create that
> field.
>
> I'm getting confused trying to figure out a few things with this method and
> I'm hoping for some direction.
>
> My database will be used to capture enforcement information regarding a
> particular crime in my area. This crime involved establishments, the
> criminals and certain information related to the criminals. Rather than lay
> out all of the specifics, if I can figure out how to relate two of the tables
> together, I should be fine. So, I would assume I need a table for the basic
> information:
>
> Tbl Incident
> Date of Offense
> Time of Offense
> Day of Week
> Establishment Name
> Subject Name
>
> Since I will be dealing with several establishments, I'm assuming I'd want
> this in a seperate table. I'd also want to include an address and a specific
> area for this:
>
> Tbl Establishment
> Establishment Name
> Establishment Address
> Establish Area
>
> I know I should be connecting the Tbl Establishment with the Tbl Incident
> and there should be like titles in both of the tables on which to match.
> I've looked at databases created by those with alot of knowledge in Access
> and I notice there's usually an ID field in each table (EstablishmentNameID -
> for instance). This is where the confusion is coming in: Should
> EstablishmentNameID be the primary key in this table as opposed to ID with
> autonumber? If it's not the primary key, I get an error message that there's
> no unique index between the two.
>
> Second point of confusion, assume the Establishment table merely contained
> the name of the establishment. By using the wizard, I put a combo box on the
> form to capture just the name and then tell it to store the establishment
> name in the Incident table (under Establishment Name) and that works fine.
> However, we have many of the same establishment names with different
> addresses and I need the address and areas included. I'm assuming the combo
> box is the wrong choice with this, as it only allows me to store the
> information from 1 field in my Incident table.
>
> I apologize for the lengthy post and I appreciate any assistance I can get.
> Thanks in advance.

I've followed the direction from Steve and Karl. Just a few follow-up
questions - I've set up tables according to Steve. When trying to link
similar fields between the tables (EstablishmentID), it doesn't allow me to
do it unless I set up the foreign key (if that's the correct word) as
Number/Long Integer, as suggested by Karl. Is this always the way it's
supposed to be done? If I don't use the number/LI, but yet have the same
data type in both tables, it tells me it can't match because the data types
are not the same.

Thanks for the assistance - it seems the more I learn, the more questions
there are.



"KARL DEWEY" wrote:

> >>Date of Offense - Time of Offense - Day of Week
> Use single DateTime field and derive the parts as needed.
>
> >>Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table.
> No, one table for incidents.
>
> Use EstablishmentNameID as an Autonumber primary key field in the
> TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign
> key in the incident table.
>
> Create a one-to-many relationship between TblEstablishment and incident,
> selecting Referential Integerity and Cascade Update.
>
> Use a combo on your incident form to pick the Establishment from
> TblEstablishment.
>
> --
> Build a little, test a little.
>
>
> "Lee Ann" wrote:
>
> > I've searched through these forums and read alot of discussions regarding not
> > using a lookup in a table. Instead, one should put that information in
> > another table and then use a combo box (using the wizard) to create that
> > field.
> >
> > I'm getting confused trying to figure out a few things with this method and
> > I'm hoping for some direction.
> >
> > My database will be used to capture enforcement information regarding a
> > particular crime in my area. This crime involved establishments, the
> > criminals and certain information related to the criminals. Rather than lay
> > out all of the specifics, if I can figure out how to relate two of the tables
> > together, I should be fine. So, I would assume I need a table for the basic
> > information:
> >
> > Tbl Incident
> > Date of Offense
> > Time of Offense
> > Day of Week
> > Establishment Name
> > Subject Name
> >
> > Since I will be dealing with several establishments, I'm assuming I'd want
> > this in a seperate table. I'd also want to include an address and a specific
> > area for this:
> >
> > Tbl Establishment
> > Establishment Name
> > Establishment Address
> > Establish Area
> >
> > I know I should be connecting the Tbl Establishment with the Tbl Incident
> > and there should be like titles in both of the tables on which to match.
> > I've looked at databases created by those with alot of knowledge in Access
> > and I notice there's usually an ID field in each table (EstablishmentNameID -
> > for instance). This is where the confusion is coming in: Should
> > EstablishmentNameID be the primary key in this table as opposed to ID with
> > autonumber? If it's not the primary key, I get an error message that there's
> > no unique index between the two.
> >
> > Second point of confusion, assume the Establishment table merely contained
> > the name of the establishment. By using the wizard, I put a combo box on the
> > form to capture just the name and then tell it to store the establishment
> > name in the Incident table (under Establishment Name) and that works fine.
> > However, we have many of the same establishment names with different
> > addresses and I need the address and areas included. I'm assuming the combo
> > box is the wrong choice with this, as it only allows me to store the
> > information from 1 field in my Incident table.
> >
> > I apologize for the lengthy post and I appreciate any assistance I can get.
> > Thanks in advance.

Always use Autonumber for the primary key (table name + ID) and Number -
Long Integer for all foreign keys. When you create a new foreign key, notice
that Access assigns 0 as the default value. I like to reset the default
value to blank.

When createing tables, I always put the primary key (table name + ID) as the
first field. Foreign keys are then created in the field list from 2 on. Also
99.99% of the time I name the foreign key the same as its corresponding
primary key. Doing this you can look at the design of a table and
immediately pick out the primary key, all foreign keys and the tables that
all foreign keys are related to (from the name!).

Steve
(E-Mail Removed)


"Lee Ann" <(E-Mail Removed)> wrote in message
news:BF9FE44C-EA7A-4E6C-9298-(E-Mail Removed)...
> I've followed the direction from Steve and Karl. Just a few follow-up
> questions - I've set up tables according to Steve. When trying to link
> similar fields between the tables (EstablishmentID), it doesn't allow me
> to
> do it unless I set up the foreign key (if that's the correct word) as
> Number/Long Integer, as suggested by Karl. Is this always the way it's
> supposed to be done? If I don't use the number/LI, but yet have the same
> data type in both tables, it tells me it can't match because the data
> types
> are not the same.
>
> Thanks for the assistance - it seems the more I learn, the more questions
> there are.
>
>
>
> "KARL DEWEY" wrote:
>
>> >>Date of Offense - Time of Offense - Day of Week
>> Use single DateTime field and derive the parts as needed.
>>
>> >>Since I will be dealing with several establishments, I'm assuming I'd
>> >>want this in a seperate table.
>> No, one table for incidents.
>>
>> Use EstablishmentNameID as an Autonumber primary key field in the
>> TblEstablishment and EstablishmentNameID - Number Long Integer - as
>> foreign
>> key in the incident table.
>>
>> Create a one-to-many relationship between TblEstablishment and incident,
>> selecting Referential Integerity and Cascade Update.
>>
>> Use a combo on your incident form to pick the Establishment from
>> TblEstablishment.
>>
>> --
>> Build a little, test a little.
>>
>>
>> "Lee Ann" wrote:
>>
>> > I've searched through these forums and read alot of discussions
>> > regarding not
>> > using a lookup in a table. Instead, one should put that information in
>> > another table and then use a combo box (using the wizard) to create
>> > that
>> > field.
>> >
>> > I'm getting confused trying to figure out a few things with this method
>> > and
>> > I'm hoping for some direction.
>> >
>> > My database will be used to capture enforcement information regarding a
>> > particular crime in my area. This crime involved establishments, the
>> > criminals and certain information related to the criminals. Rather
>> > than lay
>> > out all of the specifics, if I can figure out how to relate two of the
>> > tables
>> > together, I should be fine. So, I would assume I need a table for the
>> > basic
>> > information:
>> >
>> > Tbl Incident
>> > Date of Offense
>> > Time of Offense
>> > Day of Week
>> > Establishment Name
>> > Subject Name
>> >
>> > Since I will be dealing with several establishments, I'm assuming I'd
>> > want
>> > this in a seperate table. I'd also want to include an address and a
>> > specific
>> > area for this:
>> >
>> > Tbl Establishment
>> > Establishment Name
>> > Establishment Address
>> > Establish Area
>> >
>> > I know I should be connecting the Tbl Establishment with the Tbl
>> > Incident
>> > and there should be like titles in both of the tables on which to
>> > match.
>> > I've looked at databases created by those with alot of knowledge in
>> > Access
>> > and I notice there's usually an ID field in each table
>> > (EstablishmentNameID -
>> > for instance). This is where the confusion is coming in: Should
>> > EstablishmentNameID be the primary key in this table as opposed to ID
>> > with
>> > autonumber? If it's not the primary key, I get an error message that
>> > there's
>> > no unique index between the two.
>> >
>> > Second point of confusion, assume the Establishment table merely
>> > contained
>> > the name of the establishment. By using the wizard, I put a combo box
>> > on the
>> > form to capture just the name and then tell it to store the
>> > establishment
>> > name in the Incident table (under Establishment Name) and that works
>> > fine.
>> > However, we have many of the same establishment names with different
>> > addresses and I need the address and areas included. I'm assuming the
>> > combo
>> > box is the wrong choice with this, as it only allows me to store the
>> > information from 1 field in my Incident table.
>> >
>> > I apologize for the lengthy post and I appreciate any assistance I can
>> > get.
>> > Thanks in advance.

Lee Ann

At the risk of (re-)igniting "religious wars", I'll point out that arbitrary
primary keys (e.g., Autonumber) work well when there is no reasonable
"natural" key.

An example of a reasonable natural key can be found in another newsgroup
thread concerning using Stock Symbols as "natural" primary keys.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Lee Ann" <(E-Mail Removed)> wrote in message
news:BF9FE44C-EA7A-4E6C-9298-(E-Mail Removed)...
> I've followed the direction from Steve and Karl. Just a few follow-up
> questions - I've set up tables according to Steve. When trying to link
> similar fields between the tables (EstablishmentID), it doesn't allow me
> to
> do it unless I set up the foreign key (if that's the correct word) as
> Number/Long Integer, as suggested by Karl. Is this always the way it's
> supposed to be done? If I don't use the number/LI, but yet have the same
> data type in both tables, it tells me it can't match because the data
> types
> are not the same.
>
> Thanks for the assistance - it seems the more I learn, the more questions
> there are.
>
>
>
> "KARL DEWEY" wrote:
>
>> >>Date of Offense - Time of Offense - Day of Week
>> Use single DateTime field and derive the parts as needed.
>>
>> >>Since I will be dealing with several establishments, I'm assuming I'd
>> >>want this in a seperate table.
>> No, one table for incidents.
>>
>> Use EstablishmentNameID as an Autonumber primary key field in the
>> TblEstablishment and EstablishmentNameID - Number Long Integer - as
>> foreign
>> key in the incident table.
>>
>> Create a one-to-many relationship between TblEstablishment and incident,
>> selecting Referential Integerity and Cascade Update.
>>
>> Use a combo on your incident form to pick the Establishment from
>> TblEstablishment.
>>
>> --
>> Build a little, test a little.
>>
>>
>> "Lee Ann" wrote:
>>
>> > I've searched through these forums and read alot of discussions
>> > regarding not
>> > using a lookup in a table. Instead, one should put that information in
>> > another table and then use a combo box (using the wizard) to create
>> > that
>> > field.
>> >
>> > I'm getting confused trying to figure out a few things with this method
>> > and
>> > I'm hoping for some direction.
>> >
>> > My database will be used to capture enforcement information regarding a
>> > particular crime in my area. This crime involved establishments, the
>> > criminals and certain information related to the criminals. Rather
>> > than lay
>> > out all of the specifics, if I can figure out how to relate two of the
>> > tables
>> > together, I should be fine. So, I would assume I need a table for the
>> > basic
>> > information:
>> >
>> > Tbl Incident
>> > Date of Offense
>> > Time of Offense
>> > Day of Week
>> > Establishment Name
>> > Subject Name
>> >
>> > Since I will be dealing with several establishments, I'm assuming I'd
>> > want
>> > this in a seperate table. I'd also want to include an address and a
>> > specific
>> > area for this:
>> >
>> > Tbl Establishment
>> > Establishment Name
>> > Establishment Address
>> > Establish Area
>> >
>> > I know I should be connecting the Tbl Establishment with the Tbl
>> > Incident
>> > and there should be like titles in both of the tables on which to
>> > match.
>> > I've looked at databases created by those with alot of knowledge in
>> > Access
>> > and I notice there's usually an ID field in each table
>> > (EstablishmentNameID -
>> > for instance). This is where the confusion is coming in: Should
>> > EstablishmentNameID be the primary key in this table as opposed to ID
>> > with
>> > autonumber? If it's not the primary key, I get an error message that
>> > there's
>> > no unique index between the two.
>> >
>> > Second point of confusion, assume the Establishment table merely
>> > contained
>> > the name of the establishment. By using the wizard, I put a combo box
>> > on the
>> > form to capture just the name and then tell it to store the
>> > establishment
>> > name in the Incident table (under Establishment Name) and that works
>> > fine.
>> > However, we have many of the same establishment names with different
>> > addresses and I need the address and areas included. I'm assuming the
>> > combo
>> > box is the wrong choice with this, as it only allows me to store the
>> > information from 1 field in my Incident table.
>> >
>> > I apologize for the lengthy post and I appreciate any assistance I can
>> > get.
>> > Thanks in advance.

Thanks for all the replies. It's a little clearer now.

"BruceM via AccessMonster.com" wrote:

> I too will risk igniting conflict by saying natural keys make perfect sense
> in some situations. An autonumber is a good choice much of the time, but I
> wouldn't get locked into thinking it always needs to be used.
>
> An autonumber is a type of Long Integer field. Linking fields must be of the
> same data type (LI to LI, text to text, Double to Double, etc.), which is why
> Long Integer must be used to link to autonumber. Number field is a sort of
> umbrella term, but the specifics (Integer, Long Integer, Currency, Single,
> Double, etc.) must be the same. You can't link an integer to currency, as
> the latter may contain decimals and the former cannot.
>
> Choose a naming convention that works for you. I too tend to use the table
> name plus ID for the PK field. For instance, in tblEmployee, the PK is
> EmployeeID. In the linked table I usually use the first letter or two of the
> table name, then the linking field name. For instance, tblAward will have
> AwardID as the PK, and A_EmployeeID as the linking field, also known as a
> foreign key by some, but others hotly contest calling it a foreign key. The
> point of using a variant of the field name is that I find it easier to tell
> them apart in SQL and other code. This is my choice, but you may decide to
> make a different choice. It helps to be consistent, especially if you are
> working with others on the same project, but also for your own benefit when
> you revisit a project after a year or two.
>
> Lee Ann wrote:
> >I've followed the direction from Steve and Karl. Just a few follow-up
> >questions - I've set up tables according to Steve. When trying to link
> >similar fields between the tables (EstablishmentID), it doesn't allow me to
> >do it unless I set up the foreign key (if that's the correct word) as
> >Number/Long Integer, as suggested by Karl. Is this always the way it's
> >supposed to be done? If I don't use the number/LI, but yet have the same
> >data type in both tables, it tells me it can't match because the data types
> >are not the same.
> >
> >Thanks for the assistance - it seems the more I learn, the more questions
> >there are.
> >
> >> >>Date of Offense - Time of Offense - Day of Week
> >> Use single DateTime field and derive the parts as needed.
> >[quoted text clipped - 63 lines]
> >> > I apologize for the lengthy post and I appreciate any assistance I can get.
> >> > Thanks in advance.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...esign/201001/1
>
> .
>