Creating Relationships between tables

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.
0
Utf
1/28/2010 5:42:02 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

8 Replies
824 Views

Similar Articles

[PageSpeed] 36

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" <LeeAnn@discussions.microsoft.com> wrote in message 
news:7BF869C8-DEFB-4D04-B41B-FFD1B950E42A@microsoft.com...
> 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. 


0
Jeff
1/28/2010 7:17:18 PM
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
santus@penn.com





"Lee Ann" <LeeAnn@discussions.microsoft.com> wrote in message 
news:7BF869C8-DEFB-4D04-B41B-FFD1B950E42A@microsoft.com...
> 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. 


0
Steve
1/28/2010 7:32:08 PM
>>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.
0
Utf
1/28/2010 8:42:07 PM
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.
0
Utf
1/29/2010 2:31:03 PM
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
santus@penn.com


"Lee Ann" <LeeAnn@discussions.microsoft.com> wrote in message 
news:BF9FE44C-EA7A-4E6C-9298-B633FAEAA0A8@microsoft.com...
> 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. 


0
Steve
1/29/2010 4:45:52 PM
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" <LeeAnn@discussions.microsoft.com> wrote in message 
news:BF9FE44C-EA7A-4E6C-9298-B633FAEAA0A8@microsoft.com...
> 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. 


0
Jeff
1/29/2010 5:28:02 PM
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/Forums.aspx/access-tablesdbdesign/201001/1

0
BruceM
1/29/2010 6:12:37 PM
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/Forums.aspx/access-tablesdbdesign/201001/1
> 
> .
> 
0
Utf
1/29/2010 7:04:03 PM
Reply:

Similar Artilces:

Creating PivotTable in Excel2007
How do you create a Pivot Table in Compatibility Mode in Excel 2007. The PivotTable need to be refreshed in Excel 2003. ...

Prevent new pivot table items from being selected
I have a pivot table that contains accounts and reports sales by account. Users selected the accounts they want to see and save their spreadsheet. When new accounts are added to the accounts list, users do not want to see the new accounts. They only want to see the accounts they selected from a previous session when they refresh the pivot table. Does anyone know how to prevent the new accounts from appearing in the pivot table? Thanks! Tom, Use another column: Include, with values of Yes or No, and include it in your pivot table, showing only Yes. Then when new data is added, enter ...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

How do you replace old data with new data without creating a new .
I have existing pivot tables and I want to replace the data source worksheet with new data and the pivot tables update with the new information. I have replace the data source but the pivot tables didn't update. Hit the "Refresh Data" in the pivot table toolbar (looks like an exclamation point) -- Regards, Dave "Dena" wrote: > I have existing pivot tables and I want to replace the data source worksheet > with new data and the pivot tables update with the new information. I have > replace the data source but the pivot tables didn't update. So...

Could Microsoft create a MSPOS user manual?
My customers are asking for one. Guess we were spoiled by RMS Store Operations having one available after Microsoft updated the SMS Commerce manuals. Makes me expect one for MSPOS even though most software no longer has printed manuals. -- Jeff Faul Merchants Solutions ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

Viewing xml created in .Net in a ASP form VB6
I have been trying to create an xml document in VB.Net for displaying on an existing page written in ASP. im having a type mismatch error in my code. When i view my XML i have the following included in the <binary> node. <Binary xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">DATA HERE </Binary My issue is how do i set the datatype of the the node using dotnet so that i can replicate the same node structure and use my existing ASP page. thanks in advance Colin Graham ...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

Creating a form or template
We scanned in an image of a post it note that has graphics on it. It is a jpg file. Can we somehow make this jpg file a form or template in Outlook? Ideally, we want to pull up a form that looks like the image of a post it note with the graphics in tact and be able to compose a message on the email post it note. How do you accomplish this? Thanks! ...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

Insufficient Permissions to Create Tasks
Hi, Exchange 5.5, Windows 2000, Outlook 2000. I have a user who is trying to create tasks and he gets a message that says that he does not have permission to do this. He can edit tasks and delete tasks, but not create them. We've run the repair utility, reinstalled, thought that it had to do with the PALM software and removed that, and there has been no change. Any help would be much appreciated! Thanks! ...

Fonts do not print in colur used when document created.
When I create a document in either Word ot Publisher the text is not always printed in the colour selected. This problem seems particular to Red & Blue. can any one help please. Peeter Have you tried some maintenance on your printer? Are you saving as a PDF? This problem has been reported using Microsoft's save as add-in. -- Mary Sauer http://msauer.mvps.org/ "Peter Piper" <PeterPiper@discussions.microsoft.com> wrote in message news:76737C66-2B07-42DF-BFC5-828A14CE135F@microsoft.com... > When I create a document in either Word ot Publisher t...

Creat new dictionery
It says when I try to add a new word to my dictionery that it is full. How do I expand same, or create a new dictionery? I use WORD 2003 See http://sbarnhill.mvps.org/WordFAQs/MasterSpellCheck.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "kernaghanl" <kernaghanl@discussions.microsoft.com> wrote in message news:BBC3C828-16EF-4861-B3A5-2A7CC406F43C@microsoft.com... > It says when I try to add a new word to my dictionery that it is full. > How > do I expand same, or create a new dictione...

Choosing FROM when creating an e-mail
I have several e-mail accounts. When creating a new e- mail message sometimes I like the option of choosing a different e-mail account the new e-mail is 'from' rather than the default account. This function is possible in Outlook Express via a drop down list in the 'FROM' field when creating an e-mail, but Outlook doesn't even have a 'FROM' field and uses the default e-mail address as the return address. Please advise if this is possible in Outlook. Thanks. (PS I'm using Outlook 2000) Outlook 2000 Corporate or Workgroup mode (check Help->About) doe...

Creating Custom Dialog Boxex (Popup)
Hello, I am trying to create a custom dialog box that will allow me to have the user clarify an entry when a checkbox is checked. I want them to be able to select something from a list that I have created. I have created the popup in Excel but cannot find out how to call the popup into the display when the checkbox is checked. Can anyone help me please? Is this a checkbox on the worksheet? If yes, then if it's a checkbox from the Forms toolbar, assign this macro to the checkbox: Option Explicit Sub testme() Dim myCBX As CheckBox Set myCBX = ActiveSheet.CheckBoxes(Appli...

create tent fold card
I have a lot of cards made in pub03 that I can't print correctly in pub07. 07 wants to print 1 pic in the middle of the page and page 2 on a separate page. any way to print as a tent card the way pub 3 did? What size are you trying to print? A whole page folded or two cards per page? The page setup in 2007 takes a lot of getting use to. Open your document, file, page setup, click greeting card, select top fold, on the right click the advanced button. Do your adjusting. How did you setup your card in 2003? Did you insert two more pages? Page 4 would be the inside of your tent car...

How to create this grouping in ssrs?
I have a dataset with the following columns: This is a one to many relationship between the health authority to city and one to many relationship between the city and hospital column_group: health authority, city and hospital (three possible groupings for each of the row in the dataset) column_health_authority_name: it displays the name of the health authority to the row with the group "health authority" and it displays null to the "city" and "hospital" group column_city_name: it displays the city name to the row with the group "city" a...

Need to create blank computation column
Ok, let's try again. This time I'm breaking it down to a simple problem so that I don't confuse anyone: I have 3 cells in a row. In cell x I have a variable. In cell y I have a variable. In cell z I have a formula: =x*y . Now if the user is presented with variable x and nothing for variable y, and the formula is already in place, cell z will display 0. But I want the user to be presented with a blank field instead, until such time he/she enters in a variable for cell y. Therefore, I need to know what command will tell cell z to display a figure only if the computation is ...

Create or find "Favorite Folders" pane
I can't find the "Favorite Folders" pane above "Folders List", anymore. If you're using the Folder List then the Favorite Folders section won't appear. "ChristiA" <ChristiA@discussions.microsoft.com> wrote in message news:CEC82608-6F53-4269-9799-61D3DF7374E1@microsoft.com... >I can't find the "Favorite Folders" pane above "Folders List", anymore. ...

create a button when pressed it will +1 to another field
Customer Survey- When a button or cell is pressed once it will add 1 to another existing field and every time it is pressed it will add another 1. Private Sub Commandbutton1_Click() Dim myRange as Range Dim myNumber as Long Set myRange =3D ActiveSheet.Range("a5") '<=3D=3D change myNumber =3D myRange.Value myNumber =3D myNumber + 1 myRange.Value =3D myNumber End Sub susan On Nov 24, 9:25=A0am, mswisher <mswis...@discussions.microsoft.com> wrote: > Customer Survey- When a button or cell is pressed once it will...