Relationship advice

[NewAccessDude]

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!

 

[John W. Vinson]

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).

 

[Steve]

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

 

[Tony Toews [MVP]]

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/tonysobjectnamingconventions.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/

 

[Larry Daugherty]

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

 

[Michael Gramelspacher]

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

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 Toews [MVP]]

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/

 

[Steve]

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

 

[Larry Daugherty]

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 Daugherty]

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...

 

[Michael Gramelspacher]

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.

The templates are here:
http://office.microsoft.com/en-us/templates/TC010184671033.aspx?AxInstalled=1&c=0

There are even more templates for Access 2007, but I do not have Access 2007.

I suggest thatnew users look at them and decide which way they wish to go. Lots
of people have done that, because one template I looked at had more than 370,000
downloads. I wonder if Microsoft has a paper detailing the object naming rules
these templates follow.

In practise I never encountered any issues with using table names without the
prefix.

 

[Steve]

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

 

[Steve]

I guess the great "MVP" is only good at disagreeing but can not answer a
simple question!!!!!

Steve

 

[John... Visio MVP]

Keep your pants on stevie, you have caused more problems than you are worth
and you are surprised some of the regulars ignore you?

You have a rather mixed opinion of Tony. One hand you insult him and on the
other you point people to his website as a great resource.

As to your question, my preferred answer is table. Though I would never
refer to a table in the singular, so my table would be "Customers". Why, I
have done DB work since the sixties, yes stevie, there were databases back
then, and always found the type tagging to be superfluous. Tables should be
objects and queries should have an action or qualifier to them. Referring to
a query as "Customers" brings up question "Which customers?". So the query
name should have a qualify.

John...

John...

 

[Michael Gramelspacher]

As to your question, my preferred answer is table. Though I would never
refer to a table in the singular, so my table would be "Customers". Why, I
have done DB work since the sixties, yes stevie, there were databases back
then, and always found the type tagging to be superfluous. Tables should be
objects and queries should have an action or qualifier to them. Referring to
a query as "Customers" brings up question "Which customers?". So the query
name should have a qualify.

John.

I am a bit puzzeled why Steve thinks the question is important. How do I know
whether Kelly is a boy or a girl? Easy enough to find out, if it is important.
Look in the tables (boys) tab. Is he there? ne, then she is a girl.

SELECT * FROM Customer; Here customer is a table.
SELECT * FROM Customer; Here customer is a query. (Different database, but I
think SQL Server would allow it.)

Me.recordsource = "Customer" Does Access balk and ask whether it is a table or
a query name?

As you say, the table should be Customers; the query might be Get All Customers.

If Steve printed a relationship chart and showed it to a person vaguely
familiar with databases, would it be beneficial for the person to see names like
TblCustomer, TblEmployees, etc.? And if the person were to ask why everything
starts with Tbl-, would he say that it's so you know they are tables. Because
the person asks, "it could be something else?"

I think Access does not have a good reputation in the wider SQL community. This
is mostly unjustified in my opinion. Maybe ditching the prefixes was a
Microsoft attempt to move Access up in the world. No longer will programmers
know that a database originated as an Access database.

Just my opinion. I know all my lights do not go on all the time.

 

[Larry Daugherty]

Many people may find such a simple template usable as it is. Many
developers may find that manifestation of Access utilization a great
basis for launching their own development projects. The subject
template showcases some of the technology embodied within Access.
However, IMHO it falls woefully short of documentation standards
suitable for projects delivered to clients.

The prefixing of object type mnemonics to objects was the impetus of
this thread. By extension that means "documentation".

Everyone is free to adopt their own documentation practices from
preferred naming conventions to no standardization whatever. No
matter how phrased, recommendations are what come out of these
threads, not laws.

HTH

 

[Tony Toews [MVP]]

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?

Very likely a table but what does it matter? You can use them the
same in other objects and code.

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/

 

[Tony Toews [MVP]]

I guess the great "MVP" is only good at disagreeing but can not answer a
simple question!!!!!

So I don't check the newsgroups for a day due to illness and you're
complaining?

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/

 

[Tony Toews [MVP]]

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.

No, I'm trim a lot. I felt that paragraph was quite irrelevant.

"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."

I don't see how that makes a difference. In my opinion the
uselessness and troublesome of tbl, qry, frm and rpt prefixes far
overrides any usefulness in a team environment.

That said I seldom work in a team environment.

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...

My table and query names tend to be very different anyhow. A table
name is succinct, if at all possible, while query names tend to be
relatively verbose and wordy.

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/

 

[Larry Daugherty]

The paragraph you cut and ignored was there because it was very
relevant to the discussion at hand.

Our opinions as to the worth of a notation system to ourselves
personally obviously differ. We're free to differ.

I rarely work as a part of a software development team anymore.
However, when I complete a custom application for a client I provide
the source code for future maintenance. That is done with the
assumption that someone will eventually have to change things to
reflect changed business needs. The future developer, whether it's me
or someone else, benefits from the naming convention. I have never
once picked up some of my old code for analysis prior to change and
said to myself that the documentation, at any level, was a waste of
time. Most Access projects aren't team development endeavors. They
tend to be serial solo efforts.

I see from a different part of this thread that you've been ill. I
hope that you're well now and that you stay that way.