Relationship advice

hello,

I am having a small problem connecting the dots on the relationships between
my tables. Here is what I have so far (1st word is title)

dispatcher
dispatcher first name
dispatcher last name

dispatched from
dispatched location city
dispatched location state

info
reason for refusal
impact
extra notes
date
trucker

shipment origin
shipment origin city
shipment origin state

shipment destination
shipment destination city
shipment destination state

I have it broken down as simply as I can, the only problem is I don't know
how I am supposed to relate them. I have a tried a few setups and my queries
don't like to cooperate

Any advice or help would be appreciated. Until I will keep researching.

Thanks!

On Wed, 6 Aug 2008 16:37:01 -0700, NewAccessDude
wrote:

hello,

I am having a small problem connecting the dots on the relationships between
my tables. Here is what I have so far (1st word is title)

dispatcher
dispatcher first name
dispatcher last name

This table needs a DispatcherID primary key...

dispatched from
dispatched location city
dispatched location state

CityID also (there are quite a few Springfields); actually you probably need a
Cities table with CityID, City, State, maybe other fields. This table would be
referenced for DispatchedFrom, Origin and Destination.

info
reason for refusal
impact
extra notes
date
trucker

Not clear what this relates to.

shipment origin
shipment origin city
shipment origin state

shipment destination
shipment destination city
shipment destination state

I have it broken down as simply as I can, the only problem is I don't know
how I am supposed to relate them. I have a tried a few setups and my queries
don't like to cooperate

Any advice or help would be appreciated. Until I will keep researching.

I think the missing piece is... what's being dispatched?

You probably need a Shipments table with fields for DispatcherID, Origin (link
to Cities), Destination (ditto), DispatchedFrom (ditto), DispatchDate (or
date/time), Trucker (name, or a link to a table of truckers).
--

John W. Vinson [MVP]

First off, I suggest starting the name of all your tables with "Tbl". You'll
discover the value of this later as you develop your database. I suggest the
following tables:
TblDispatcher
DispatcherID
FirstName
LastName
Contact fields if needed

TblState
StateID
State

TblCity
CityID
StateID
City

TblTrucker (Assumes all truckers are independents)
TruckerID
FirstName
LastName
Contact fields if needed

TblShipment
ShipmentID
DepartureDate
ArrivalDate
OriginStateID
OriginCityID
DestinationStateID
DestinationCityID

TblShipmentDispatch
ShipmentDispatchID
ShipmentID
DispatcherID
DispatchDateTime
TruckerID

TblShipmentDispatchRefused
ShipmentDispatchRefusedID
ShipmentDispatchID
RefusalReason
Impact
Notes


Relationships .....
StateID in TblState === StateID in TblCity
StateID in TblState === OriginStateID in TblShipment
CityID in TblCity === OriginCityID in TblShipment
StateID in TblState === DestinationStateIDeID in TblShipment
CityID in TblCity === DestinationCityID in TblShipment
ShipmentID in TblShipment === ShipmentID in TblShipmentDispatch
DispatcherID in TblDispatcher === DispatcherID in TblShipmentDispatch
TruckerID in TblTrucker === TruckerID in TblShipmentDispatch
ShipmentDispatchID in TblShipmentDispatch === ShipmentDispatchID in
TblShipmentDispatchRefused

Steve


"NewAccessDude" wrote in message
...
hello,

I am having a small problem connecting the dots on the relationships
between
my tables. Here is what I have so far (1st word is title)

dispatcher
dispatcher first name
dispatcher last name

dispatched from
dispatched location city
dispatched location state

info
reason for refusal
impact
extra notes
date
trucker

shipment origin
shipment origin city
shipment origin state

shipment destination
shipment destination city
shipment destination state

I have it broken down as simply as I can, the only problem is I don't know
how I am supposed to relate them. I have a tried a few setups and my
queries
don't like to cooperate

Any advice or help would be appreciated. Until I will keep researching.

Thanks!

"Steve" wrote:

First off, I suggest starting the name of all your tables with "Tbl".

I disagree. I've never seen any use in using an object prefix such
as tbl, qry, frm, rpt, etc. Indeed, in a database with a large
number of objects this can really slow you down.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Consider just a few issues that might cause the wider world to come
down on the side of "tbl, qry" etc.:

In all cases, please prefix the phrase "All else being equal, "

Millions of Access and VB books with the Reddick naming convention
included in an appendix have been published. It's also available on
the Microsoft site. There is also the Leszynski naming convention
which has some modest sway. The roots of the two conventions are the
same. It all started out as the Leszynski-Reddick naming convention
in the early '90s. I do believe that more people have read and
adopted to some degree one of the foregoing than have visited the
relevant pages on your site and adopted your convention. That's no
put down to you and your site because you have lots of terrific Access
lore there. We're all grateful.

The greater the number of developers who will readily understand the
notations within an application, the more readily and inexpensively
the application can be maintained and extended. That's especially
true if the more widely published convention is the better one.
That's still true if the naming conventions have only equal value.

Take the simple case of using the Form Wizard to create a form prior
to extending it. With object prefixes it is immediately apparent
whether an object is a table or a query. Without object prefixes the
possibilities for ambiguity are endless. It is well within the realm
of possibility that a developer could give a table and a query the
same root name.

Take the case of using a global utility such as Find and Replace. It
is reassuring to see that the replace action is being taken on exactly
the object you intended, and not on something else with the same name.

I encourage people to use the Reddick naming convention to the degree
they feel comfortable. But they should choose (or create) a
convention, adopt it and follow it.

HTH
--
-Larry-
--

"Tony Toews [MVP]" wrote in message
...
"Steve" wrote:

First off, I suggest starting the name of all your tables with
"Tbl".

I disagree. I've never seen any use in using an object prefix such
as tbl, qry, frm, rpt, etc. Indeed, in a database with a large
number of objects this can really slow you down.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

On Thu, 7 Aug 2008 07:01:37 -0700, "Larry Daugherty"
wrote:

Consider just a few issues that might cause the wider world to come
down on the side of "tbl, qry" etc.:

In all cases, please prefix the phrase "All else being equal, "

Millions of Access and VB books with the Reddick naming convention
included in an appendix have been published. It's also available on
the Microsoft site. There is also the Leszynski naming convention
which has some modest sway. The roots of the two conventions are the
same. It all started out as the Leszynski-Reddick naming convention
in the early '90s. I do believe that more people have read and
adopted to some degree one of the foregoing than have visited the
relevant pages on your site and adopted your convention. That's no
put down to you and your site because you have lots of terrific Access
lore there. We're all grateful.

The greater the number of developers who will readily understand the
notations within an application, the more readily and inexpensively
the application can be maintained and extended. That's especially
true if the more widely published convention is the better one.
That's still true if the naming conventions have only equal value.

Take the simple case of using the Form Wizard to create a form prior
to extending it. With object prefixes it is immediately apparent
whether an object is a table or a query. Without object prefixes the
possibilities for ambiguity are endless. It is well within the realm
of possibility that a developer could give a table and a query the
same root name.

Take the case of using a global utility such as Find and Replace. It
is reassuring to see that the replace action is being taken on exactly
the object you intended, and not on something else with the same name.

I encourage people to use the Reddick naming convention to the degree
they feel comfortable. But they should choose (or create) a
convention, adopt it and follow it.

HTH
--
-Larry-

This is a good argument for the weight of tradition, but maybe it is time for
something new. I notice that the Access 2003 tempates I downloaded have a
modern, refreshing style.

This is something a new user should really think about. Should they load on all
the baggage of yesteryear, or chart a new path. It is understandably hard for
the oldtimers to cast off tradition.

I would not be inclined to tell an older user to change their naming convention,
but a new user should be shown that the way they are being headed is not the
only way.

"Larry Daugherty" wrote:

Millions of Access and VB books with the Reddick naming convention
included in an appendix have been published. It's also available on
the Microsoft site. There is also the Leszynski naming convention
which has some modest sway. The roots of the two conventions are the
same. It all started out as the Leszynski-Reddick naming convention
in the early '90s. I do believe that more people have read and
adopted to some degree one of the foregoing than have visited the
relevant pages on your site and adopted your convention.

Sure, but that doesn't mean that they're right. I firmly believe that
using tbl, qry, frm and rpt are wrong, foolish, a waste of time and
slows you down in a database with a large number of objects.

Take the simple case of using the Form Wizard to create a form prior
to extending it. With object prefixes it is immediately apparent
whether an object is a table or a query. Without object prefixes the
possibilities for ambiguity are endless. It is well within the realm
of possibility that a developer could give a table and a query the
same root name.

So what? Access actually won't allow you to create a query with the
same name as a table.

Take the case of using a global utility such as Find and Replace. It
is reassuring to see that the replace action is being taken on exactly
the object you intended, and not on something else with the same name.

Pretty unlikely you'd have an object with the same name in either of
the three areas. Although possible.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

A simple question for your vast expertise and MVP credentials .........

Suppose in an application's code you see reference to an object named
"Customer". Is that object a Table or a Query?

Steve


"Tony Toews [MVP]" wrote in message
...
"Steve" wrote:

First off, I suggest starting the name of all your tables with "Tbl".

I disagree. I've never seen any use in using an object prefix such
as tbl, qry, frm, rpt, etc. Indeed, in a database with a large
number of objects this can really slow you down.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Tradition!? Pfui! Access hasn't been around all that long. The
references to history was simply to indicate where the sheer numbers
of people familiar with the naming conventions have arisen. It was
hardly an obeisance to tradition. The argument advanced was about
adopting a naming convention. Further, as between naming conventions
of roughly comparable technical merit, the one that is most widely
used among practitioners of the craft under discussion has the greater
value.

If your studies and your judgement and your own value system show you
a better way then it behooves you to adopt that one. If you believe
that it would benefit others then share it and champion it.

To which Access 2003 templates do you refer? I haven't analyzed
anything from Microsoft in a long time.

I don't see anything in your post that gets into the merits of any
particular naming convention nor that makes any good arguments toward
having none at all.

--
-Larry-
--

"Michael Gramelspacher" wrote in message
news
On Thu, 7 Aug 2008 07:01:37 -0700, "Larry Daugherty"
wrote:

Consider just a few issues that might cause the wider world to come
down on the side of "tbl, qry" etc.:

In all cases, please prefix the phrase "All else being equal, "

Millions of Access and VB books with the Reddick naming convention
included in an appendix have been published. It's also available
on
the Microsoft site. There is also the Leszynski naming convention
which has some modest sway. The roots of the two conventions are
the
same. It all started out as the Leszynski-Reddick naming
convention
in the early '90s. I do believe that more people have read and
adopted to some degree one of the foregoing than have visited the
relevant pages on your site and adopted your convention. That's no
put down to you and your site because you have lots of terrific
Access
lore there. We're all grateful.

The greater the number of developers who will readily understand
the
notations within an application, the more readily and inexpensively
the application can be maintained and extended. That's especially
true if the more widely published convention is the better one.
That's still true if the naming conventions have only equal value.

Take the simple case of using the Form Wizard to create a form
prior
to extending it. With object prefixes it is immediately apparent
whether an object is a table or a query. Without object prefixes
the
possibilities for ambiguity are endless. It is well within the
realm
of possibility that a developer could give a table and a query the
same root name.

Take the case of using a global utility such as Find and Replace.
It
is reassuring to see that the replace action is being taken on
exactly
the object you intended, and not on something else with the same
name.

I encourage people to use the Reddick naming convention to the
degree
they feel comfortable. But they should choose (or create) a
convention, adopt it and follow it.

HTH
--
-Larry-

This is a good argument for the weight of tradition, but maybe it
is time for
something new. I notice that the Access 2003 tempates I downloaded
have a
modern, refreshing style.

This is something a new user should really think about. Should they
load on all
the baggage of yesteryear, or chart a new path. It is
understandably hard for
the oldtimers to cast off tradition.

I would not be inclined to tell an older user to change their naming
convention,
but a new user should be shown that the way they are being headed is
not the
only way.

Tony,

I thought you were above cheap tricks. :-) You cut a paragraph from
my post and then inserted your reply after the omission. That changes
the apparent sense of things. Your subsequent argument had already
been rebutted by the paragraph you cut out.

The omitted paragraph:

"The greater the number of developers who will readily understand the
notations within an application, the more readily and inexpensively
the application can be maintained and extended. That's especially
true if the more widely published convention is the better one.
That's still true if the naming conventions have only equal value."

You then go on to write:
Sure, but that doesn't mean that they're right. I firmly believe
that
using tbl, qry, frm and rpt are wrong, foolish, a waste of time and
slows you down in a database with a large number of objects.

Before I'd take on any jfurther point of yours, you'd have to take on
mine; the one that you deleted. I'd really like to learn and I'm sure
that others would too. But you have to address all of the points or
none of them.

It's my belief that you know more things about Access than I do.
Would you believe that I didn't know that Access wouldn't let a
developer give a table and a query the same name? In my ignorance it
must have been just pure dumb luck all these years...

--
-Larry-
--

"Tony Toews [MVP]" wrote in message
...
"Larry Daugherty" wrote:

Millions of Access and VB books with the Reddick naming convention
included in an appendix have been published. It's also available
on
the Microsoft site. There is also the Leszynski naming convention
which has some modest sway. The roots of the two conventions are
the
same. It all started out as the Leszynski-Reddick naming
convention
in the early '90s. I do believe that more people have read and
adopted to some degree one of the foregoing than have visited the
relevant pages on your site and adopted your convention.

Sure, but that doesn't mean that they're right. I firmly believe
that
using tbl, qry, frm and rpt are wrong, foolish, a waste of time and
slows you down in a database with a large number of objects.

Take the simple case of using the Form Wizard to create a form
prior
to extending it. With object prefixes it is immediately apparent
whether an object is a table or a query. Without object prefixes
the
possibilities for ambiguity are endless. It is well within the
realm
of possibility that a developer could give a table and a query the
same root name.

So what? Access actually won't allow you to create a query with the
same name as a table.

Take the case of using a global utility such as Find and Replace.
It
is reassuring to see that the replace action is being taken on
exactly
the object you intended, and not on something else with the same
name.

Pretty unlikely you'd have an object with the same name in either of
the three areas. Although possible.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/