Design-rehaul advice needed from experienced developers

[JohnH]

I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of any of the principles of database design, so what i'm left with is
a junkpile. The only reason I can't start all over?....all the data
needs to be saved.

So here's the boiled down essence:
People visit our company wanting to buy a car. One of our sales guys
locates the car they want (using dealer inventory databases, car
sales, etc) and comes back with a price. The person (or couple) say
okay we want to buy it. They love it, become repeat customers, refer
friends, etc.

After the sale, I get a "deal jacket" with all the details of the
sale. I have to dataenter this information into a horrid orange
frontend and then the lame mutated radioactive hedgehod behind the
form shits on the data and puts it in mismarked cubbyholes.

The database as it exists now is -supposed- to allow us to enter
"deals," which is basically jargon for a car sale. Then later we can
allegedly audit data, verify dates, when plates were mailed, create
monthly reports, etc.

The database structure as it is frightning, and unless you're
massively catatonic it should make you at least giggle. (My reaction
of course isn't so light-hearted)

1 table "Customers"
1 table "Caps" (caps is jargon for the financial tallying of the sale,
which means the deal has been finalized, or "capped.")

[Customers:]
Sale Date
Names
Spouse
Address/Phone
Vehicle Year
Vehicle Make/Model
New/Used
VIN
Dealer
Consultant (our sales guy)
Referral Source
Financer

[CAPS]
Sale Date
Names
Sale Price
Our Cost
MBI
Extras
Extras Amount
Less Expenses
Consultant Initials (2 chars)
Commission
The 2 tables are related by three fields, mainly Sale Date, Last Name,
and First Name. This means there can be no one-to-many relationship
between a "Customer" and a "Sale," disregarding the fact that the
Customer table isn't really a customer table and the Cap table doesn't
really represent a sale.

I'm concerned here with core design. I haven't studied normalization
but I have the basic (what seems to be intuitive) idea of RDMS.

I am not going to fix the current database. The front end is
completely useless and the tables are junk. I'm starting from scratch
but will still need to get the old data into the new tables. This
will create a serious problem, described in a minute. Anyway,
utlimately I need to create a new table schema and that's where I
would like advice.

My current thoughts are:
The best way to model our business might be to create a logical entity
"Customer" and a logical entity "Sale." Or maybe three: Customer,
EntitySold, and Sale. This would allow the option of logging sales of
things other than cars like warranties and safety products. My vision
for the best form of interfacing with the tables would be treating
Customers as the primary data entity, Customers could be searched,
edited, actions perfomed upon, etc. Then, when a customer is
selected, all information relating to that customer will be available,
including of course his history of car purchases with us.

The problem then is how to relate these entities. As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible. I don't
think i'll be able to get SS#s, so I think my only option is to start
requiring the sales department to get Driver's licence numbers and
states as a requirement to capping their deals.

Assuming I have my hat on straight so far, the central task of
relating customers and their sales would mean a Customer table related
to the Sale table(s) related by two fields, DriversLicence# and
DriversLicenceState.

And now onto the problem I forsee. Assuming I can create this new
dabatase structure and painstakingly get the old data into its new
clothes. I'm going to have, essentially, old data in each table
lacking a key (DriversLicense), that which relates the customer with
his sale(s). And even if I were to give that old data arbitrary keys
to maintain their relationship, that brings up all kinds of new
problems which I probably don't have to enumerate.

Anyway, this is where I peter out because I don't have any experience
with this. Can someone give me some advice on my thoughts so far and
where I might go from here? I must admit I'm feeling slightly
overwhelmed.
Thank you for reading my novellete,
John

p.s.
I'm running Access 2000 mdb

[ManningFan]

And when the same customer comes back for another vehicle (you DO want
repeat customers, no?) your layout is phuxored.

You can create a new DB from scratch with the saved data if you know
how to write Append and MakeTable queries. Apparently you don't. I
can tell you you're going to need more tables than you say you will if
you want to be able to make a scalable database. Using the 2 tables
you described, you're going to be up shit's creek without a Camaro
when you hit your 100th sale. All those reports you think you're
going to be able to pull aren't going to be possible to create without
running multiple redundant queries.

Just tell your boss to pony up for a real database designer to do it
properly. Otherwise you're just sticking your finger in the dyke (or
"Ellen Degeneres" as I like to put it). Then you can be the admin and
all is well.

On Feb 26, 4:54 pm, "JohnH" <[e-mail]:

I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of any of the principles of database design, so what i'm left with is
a junkpile. The only reason I can't start all over?....all the data
needs to be saved.

So here's the boiled down essence:
People visit our company wanting to buy a car. One of our sales guys
locates the car they want (using dealer inventory databases, car
sales, etc) and comes back with a price. The person (or couple) say
okay we want to buy it. They love it, become repeat customers, refer
friends, etc.

After the sale, I get a "deal jacket" with all the details of the
sale. I have to dataenter this information into a horrid orange
frontend and then the lame mutated radioactive hedgehod behind the
form shits on the data and puts it in mismarked cubbyholes.

The database as it exists now is -supposed- to allow us to enter
"deals," which is basically jargon for a car sale. Then later we can
allegedly audit data, verify dates, when plates were mailed, create
monthly reports, etc.

The database structure as it is frightning, and unless you're
massively catatonic it should make you at least giggle. (My reaction
of course isn't so light-hearted)

1 table "Customers"
1 table "Caps" (caps is jargon for the financial tallying of the sale,
which means the deal has been finalized, or "capped.")

[Customers:]
Sale Date
Names
Spouse
Address/Phone
Vehicle Year
Vehicle Make/Model
New/Used
VIN
Dealer
Consultant (our sales guy)
Referral Source
Financer

[CAPS]
Sale Date
Names
Sale Price
Our Cost
MBI
Extras
Extras Amount
Less Expenses
Consultant Initials (2 chars)
Commission

The 2 tables are related by three fields, mainly Sale Date, Last Name,
and First Name. This means there can be no one-to-many relationship
between a "Customer" and a "Sale," disregarding the fact that the
Customer table isn't really a customer table and the Cap table doesn't
really represent a sale.

I'm concerned here with core design. I haven't studied normalization
but I have the basic (what seems to be intuitive) idea of RDMS.

I am not going to fix the current database. The front end is
completely useless and the tables are junk. I'm starting from scratch
but will still need to get the old data into the new tables. This
will create a serious problem, described in a minute. Anyway,
utlimately I need to create a new table schema and that's where I
would like advice.

My current thoughts are:
The best way to model our business might be to create a logical entity
"Customer" and a logical entity "Sale." Or maybe three: Customer,
EntitySold, and Sale. This would allow the option of logging sales of
things other than cars like warranties and safety products. My vision
for the best form of interfacing with the tables would be treating
Customers as the primary data entity, Customers could be searched,
edited, actions perfomed upon, etc. Then, when a customer is
selected, all information relating to that customer will be available,
including of course his history of car purchases with us.

The problem then is how to relate these entities. As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible. I don't
think i'll be able to get SS#s, so I think my only option is to start
requiring the sales department to get Driver's licence numbers and
states as a requirement to capping their deals.

Assuming I have my hat on straight so far, the central task of
relating customers and their sales would mean a Customer table related
to the Sale table(s) related by two fields, DriversLicence# and
DriversLicenceState.

And now onto the problem I forsee. Assuming I can create this new
dabatase structure and painstakingly get the old data into its new
clothes. I'm going to have, essentially, old data in each table
lacking a key (DriversLicense), that which relates the customer with
his sale(s). And even if I were to give that old data arbitrary keys
to maintain their relationship, that brings up all kinds of new
problems which I probably don't have to enumerate.

Anyway, this is where I peter out because I don't have any experience
with this. Can someone give me some advice on my thoughts so far and
where I might go from here? I must admit I'm feeling slightly
overwhelmed.

Thank you for reading my novellete,
John

p.s.
I'm running Access 2000 mdb

[Ricks]

First create and test a new database and make it like you want it. A
layout on a sheet of paper would help.

Use this as a template but don't create tables from scratch but use
make table queries.

[JohnH]

On Feb 26, 2:19 pm, "Ricks" <[e-mail]:

First create and test a new database and make it like you want it. A
layout on a sheet of paper would help.

Use this as a template but don't create tables from scratch but use
make table queries.

I appreciate people's attempts to help, but Rick, you must not have
read what I wrote.
And ManningFan, I don't want to flame you but you don't seem to care,
so why did you respond?
Your first sentence feigns to reveal some myopia on my part, but
merely shouts that you're arrogant: The entire point of this
discussion question was to ask for ideas on how to solve that very
problem. Also, what is the point in assuming what I "apparently don't
know" something, without evidence? My question centers around design
decisions, abstract/theory, and I would be very grateful to get
responses that might help me in that ear. You however can't stop
listening to yourself talk machismo. And I'd rather not be insulted
when I ask questions so stay out of my posts.

[Albert D. Kallal]

>The only reason I can't start all over?....all the data

needs to be saved.

I am at a loss as to why the above is problem?

You can import data, and write code to re-format, modify the data to the
structures you want. Modifying, and importing data from another system is a
task that MOST developers will have to do many times in their career. If
this was not the case, then a company would never purchase new software and
never change what they used.

I had to import data from 20 year main frame systems into ms-access. This
type of import is par for the course.

>As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible

You miss understanding the use of internal autonumber for relationships, and
that of searching for a customer.

Who cares about the internal autonumber key you use for your relationships.

Why would you EVER care what id ms-access uses for the relation?

Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?

Who cares about a number you, and your users will NEVER see?

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?

Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.

These numbers are NOT for humans to see.....

There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?

If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).

You users should NEVER EVER see a autonumber.

You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.

Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTHING to
do with setting up a relation between two tables.

Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.

Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....

how you search for, or find a customer is up to you. Perhaps you provide a
search form that searches the names by soundex (then, you can search for
names..and even if miss spelled, you can still find that name. Perhaps you
issue a new customer number. How you do this, and whatever you come up with
is going to have ZERO effect on that fact hat you build relationships
between tables. However you identify that customer...lastname..or whoever,
that has nothing to do with the actual relationships you design your
appcation around. Keep the two concepts separate.

Perhaps you simply question is:

What is a good way to identify a customer?

A good answer is how do they do this now?

So, I would import the relational data you have in the old system, but after
you import the data, you will write some code and queries to re-structure
the data to function with your new found knowledge that you can build a
whole data base and it will function relational without you EVER having to
type in, or even worry, or even know that some internal machine system uses
auto numbers.

I'm going to have, essentially, old data in each table

lacking a key (DriversLicense), that which relates the customer with
his sale(s).

Well, did you have some relation before or not? You going have to
re-structure the old data to work with the new autonumber system. Ether that
data had some way to being related, or not. You have to preserve this
relational data, and that means dumping the old keys..and generating new
ones based on the autonumbers. I don't see why code can't be written to take
the old table data, and re-structure the fields and data into the a new
table that is related to the main customer record table.

Without quesiton, migration of data from old systems to new means that you
have to write a LOT of code and queires to re-stuctire the data. As i said,
this is standard fair, and one has to assume you have decient coding skills
here..else how do you expect to move forward with the task given to you?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[JohnH]

On Feb 26, 2:39 pm, "Albert D. Kallal" <[e-mail]>
wrote:

The only reason I can't start all over?....all the data
needs to be saved.

I am at a loss as to why the above is problem?

You can import data, and write code to re-format, modify the data to the
structures you want. Modifying, and importing data from another system is a
task that MOST developers will have to do many times in their career. If
this was not the case, then a company would never purchase new software and
never change what they used.

I had to import data from 20 year main frame systems into ms-access. This
type of import is par for the course.

As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible

You miss understanding the use of internal autonumber for relationships, and
that of searching for a customer.

Who cares about the internal autonumber key you use for your relationships.

Why would you EVER care what id ms-access uses for the relation?

Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?

Who cares about a number you, and your users will NEVER see?

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?

Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.

These numbers are NOT for humans to see.....

There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?

If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).

You users should NEVER EVER see a autonumber.

You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.

Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTHING to
do with setting up a relation between two tables.

Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.

Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....

how you search for, or find a customer is up to you. Perhaps you provide a
search form that searches the names by soundex (then, you can search for
names..and even if miss spelled, you can still find that name. Perhaps you
issue a new customer number. How you do this, and whatever you come up with
is going to have ZERO effect on that fact hat you build relationships
between tables. However you identify that customer...lastname..or whoever,
that has nothing to do with the actual relationships you design your
appcation around. Keep the two concepts separate.

Perhaps you simply question is:

What is a good way to identify a customer?

A good answer is how do they do this now?

So, I would import the relational data you have in the old system, but after
you import the data, you will write some code and queries to re-structure
the data to function with your new found knowledge that you can build a
whole data base and it will function relational without you EVER having to
type in, or even worry, or even know that some internal machine system uses
auto numbers.

I'm going to have, essentially, old data in each table

lacking a key (DriversLicense), that which relates the customer with
his sale(s).

Well, did you have some relation before or not? You going have to
re-structure the old data to work with the new autonumber system. Ether that
data had some way to being related, or not. You have to preserve this
relational data, and that means dumping the old keys..and generating new
ones based on the autonumbers. I don't see why code can't be written to take
the old table data, and re-structure the fields and data into the a new
table that is related to the main customer record table.

Without quesiton, migration of data from old systems to new means that you
have to write a LOT of code and queires to re-stuctire the data. As i said,
this is standard fair, and one has to assume you have decient coding skills
here..else how do you expect to move forward with the task given to you?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]://www.attcanada.net/~kallal.msn

Well your post, though obnoxious, sputtering, and loud, had some
information that is of use to me. I'm still not sure though why my
question hooked what appear to be strong emotions, but I hope it was
therapeutic for you to consider me an idiot for a few minutes; at
least one of us benefited.

I asked the question on this board because there is a hole in my
thinking concerning database design. I haven't been to school for it
or been taught by anyone, which is fairly common. I am familiar with
the existence of autonumbers, primary keys, etc, and I also know that
they are never exposed, which is fairly obvious. However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

I'd like some real help from someone who doesn't have a complex,
please. I'm not stupid, as Alberto the MVPs tone would suggest, I'm
just fairly close to understanding how I want to do this, but I'd like
some clarification, some encouragement, and a little damn respect.
Thank you.

[rkc]

JohnH wrote:

I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of any of the principles of database design, so what i'm left with is
a junkpile. The only reason I can't start all over?....all the data
needs to be saved.

<snip-a-shitload>

Take a look at this as an example of how you need to be thinking:
http://www.databaseanswers.org/data_...ales/index.htm

[Larry Linson]

Yes, you are correct: What's called a "Customer" table in your database
isn't, as it contains other information.

I haven't examined the sample that rkc suggested, but it may well be a good
starting point for the design. So, I'd suggest you look at it, first.

Albert is correct that you can use the AutoNumber to identify and related
Records, and that should not be displayed to the user. The way Auto-Numbers
work can result in gaps in the sequence that drive green-eyeshade
bookkeepers and accountants to distraction.

Your dealership very likely has some other Customer record-keeping which may
have an associated Customer Number, and, for consitency, you might want to
use that...

Social Security Number is, in these days of identity theft and hacking, not
a good choice -- some customers will refuse to give it. And, just for the
record, mistakes at the Social Security Administration have, indeed,
resulted in duplicates.

Driver's License, too, may not be good. You do not have to be a licensed
driver, in most states, to purchase a vehicle, even if a license is required
to drive it. What about Mr. and Mrs. Gotrocks, who will purchase and own the
limousine, but have a chauffeur or two to drive it?

Your first step should be to determine what it is that you need to
accomplish with your database... that may be similar, or dissimilar, to the
requirements of rkc's suggested sample. But, it you perceive that the sample
accomplishes what you want, or most of what you want, or part of what you
want, it could be a good starting point. And, it could suggest additional
functions that might appeal to the owners/users of your database, which
they'd want to include.

Once you've done a little homework, post a followup to this thread for
further discussion.

The tables in your database may not include all the information you'll
need... but the information that exists, as you've been told, can be
extracted with Queries and put into the new Tables you determine will be
needed. You will need to do some reading on Action Queries -- MakeTable,
Append, Update, Delete, etc. -- a good self-study text for Access 2003 is
"Microsoft Access 2003 Inside-Out" by John Viescas, published by Microsoft
Press. John and Jeff Conrad have written a similar book for Microsoft
Access 2007, but it won't be available until May, if I understand correctly.
A shorter, simpler book would be "Microsoft Access 2003 Step by Step" from
Microsoft Press -- good for the new user and straightforward, but will only
get you started on Access as a development tool.

I hope the tone of this response was milder and gentler and, thus, not a
turn-off. Everybody has to start out somewhere, sometime. And I try to
never forget that this is a newsgroup for users of every level, not just for
experienced developers. (I'm more likely to forget, and rant, when someone
performs a major action that destroys their database, but hasn't bothered to
make a backup copy first -- that seems so obvious to me that even the
rankest newbie should know to do it.)

And, as a matter of personal preference, I'd suggest you upgrade to Access
2003, and be sure to obtain a high-speed connection because the Help content
is better, but online Help is primary. For me, Access 2007 is just too new
for me to want to use it for production. (Access 2000, BTW, is "out of
support" in the Microsoft product schedules, but if you have all three
Service Packs installed, it should be relatively stable.)

Larry Linson
Microsoft Access MVP

[Albert D. Kallal]

First, my apologies. that post is out of line. I should have done better.

My sorry.

However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

Yes, you are correct in the above. The so called foreign key
is a standard field you place in the child table. This foreign key
is NOT the primary key. It is a regular long number field.

So, even a child table will have its own primary key (usually the default
name for a autonumber field is "id", and that tends to be fine).

To relate the child table, you simply add anew field, and in that field you
place the value of the parent table id. So, yes, you are 100% correct in the
above that an additional field is placed into the table. Note that if you
use a sub-form, the ms-access can "set" the value used for the child field.
If you need to add records to a child table and you are not using a
sub-form, then ms-access WILL NOT set this value for you.

Once again, my apologies for that post (it was a rant). It was simply out of
line. It sounds likely you inherited a mess (but, lets not be too critical
on those who do mess up!!).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[JohnH]

On Feb 26, 6:42 pm, "Albert D. Kallal" <[e-mail]>
wrote:

First, my apologies. that post is out of line. I should have done better.

My sorry.

However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

Yes, you are correct in the above. The so called foreign key
is a standard field you place in the child table. This foreign key
is NOT the primary key. It is a regular long number field.

So, even a child table will have its own primary key (usually the default
name for a autonumber field is "id", and that tends to be fine).

To relate the child table, you simply add anew field, and in that field you
place the value of the parent table id. So, yes, you are 100% correct in the
above that an additional field is placed into the table. Note that if you
use a sub-form, the ms-access can "set" the value used for the child field.
If you need to add records to a child table and you are not using a
sub-form, then ms-access WILL NOT set this value for you.

Once again, my apologies for that post (it was a rant). It was simply out of
line. It sounds likely you inherited a mess (but, lets not be too critical
on those who do mess up!!).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Thank you rkc, Albert and Larry. I was also having a very bad day
yesterday so I wasn't getting anywhere. I'm new to real database
development; I know a bit of SQL, VBA quite well, and I can navigate
around in Access and create fairly sophisticated forms, but I've never
really learned some of the fundamentals of the actual backend design,
I've spent most of my time messing around with driftwood-ductape-and-
string setups in small businesses.

I'm going to spend some time studying and then write up a schema.

I truly appreciate your help. What you told me about relating tables
is fairly simple and I should have gone with my instincts (I was told
by a selfavowed genius that I *had* to use customer-specific
information as the primary key for Customer). My task really then
(after I've designed a normalized backend) is to design a smart
interface that can, for example, resolve returning customers to their
PK. I suppose the flow of the program will be "search for customer by
{information}, if he exists add car sale, if not, add customer and car
sale."

Any other ideas you want to throw my way, feel free! Again, thank
you, what you guys do here for the public good is admirable.

John

[David W. Fenton]

"JohnH" <Jo**********@gmail.comwrote in
news:11**********************@j27g2000cwj.googlegr oups.com:

Well your post, though obnoxious, sputtering, and loud, had some
information that is of use to me.

You're useless if you can't take Albert's post in the spirit in
which it was offered.

<PLONK>

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[rkc]

David W. Fenton wrote:

"JohnH" <Jo**********@gmail.comwrote in
news:11**********************@j27g2000cwj.googlegr oups.com:

>>Well your post, though obnoxious, sputtering, and loud, had some
information that is of use to me.

You're useless if you can't take Albert's post in the spirit in
which it was offered.

You've been living in a bubble if you think someone coming in here
for the first time is going to be able to decipher some of the shit
Albert comes up with.

[David W. Fenton]

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

David W. Fenton wrote:

>"JohnH" <Jo**********@gmail.comwrote in
news:11**********************@j27g2000cwj.googleg roups.com:

>>>Well your post, though obnoxious, sputtering, and loud, had some
information that is of use to me.

You're useless if you can't take Albert's post in the spirit in
which it was offered.

You've been living in a bubble if you think someone coming in here
for the first time is going to be able to decipher some of the
shit Albert comes up with.

While Albert's message was somewhat repetitive as well as strong in
tone, I saw nothing worth getting upset about.

Some people waltz in and ask for help and when someone like Albert
takes a long time to write a response, they seem to have just been
spoiling for a fight. I'm not interested in reading those people's
posts.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[JohnH]

On Feb 27, 3:18 pm, "David W. Fenton" <[e-mail]>
wrote:

rkc <[e-mail] innews:45***********************@roadrunner.com:

David W. Fenton wrote:

"JohnH" <[e-mail] in
news:11**********************@j27g2000cwj.googleg roups.com:

>>Well your post, though obnoxious, sputtering, and loud, had some
information that is of use to me.

You're useless if you can't take Albert's post in the spirit in
which it was offered.

You've been living in a bubble if you think someone coming in here
for the first time is going to be able to decipher some of the
shit Albert comes up with.

While Albert's message was somewhat repetitive as well as strong in
tone, I saw nothing worth getting upset about.

Some people waltz in and ask for help and when someone like Albert
takes a long time to write a response, they seem to have just been
spoiling for a fight. I'm not interested in reading those people's
posts.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David, I wasn't spoiling for a fight, nor did I try to provoke one.
What I didn't understand (at the time) was why Albert *seemed* to be
pounding me into the ground over how stupid my question was. It was
paragraph after paragraph about how idiotic anyone would have to be to
expose autoIDs. I didn't appreciate being treated (from my
perspective) like a moron just for posing a question. I don't believe
I'm thin-skinned, but when I receive two responses to an innocent
question, one dismissing me altogether (tell your boss to hire a real
developer), and the other wondering how I could be so dumb, I feel
that expressing my frustration is warranted. Albert himself
apologized twice and said he had been "on a rant." It also appears I
misunderstood his tone (probably because of the CAPSLOCK), based on
what rkc said. Contrary to what you perceive as "waltzing," I'm quite
grateful for Albert's responses, as well as Larry's and rkc's, who
also took the time to help a silly novitiate's silly question (see my
previous post.)

[Gord]

Hey, John.

>I was told by a selfavowed genius that I *had* to use customer-
specific information as the primary key for Customer

FWIW, there are some people who feel very strongly that Autonumber
primary keys (sometimes called "Identity columns") are an affront to
the *theory* of relational databases and should NEVER be used. It
should be noted that not all of these people are loonies. They simply
identify with the theoretical much more than the practical in these
matters, and I tend to disagree with them. (If you're curious, search
the comp.databases.ms-sqlserver group via groups.google.com for some
rather lively discussions over the years.)

There are also, as I'm sure you have noticed, some people who are
quick to point out that they would NEVER, UNDER ANY CIRCUMSTANCE do
some particular thing that lots of other people find fairly innocuous.
I'm not really sure what fuels their dogmatism, but it is a fact of
life.

Example: Albert's assertion in his first post that "users should NEVER
EVER see a autonumber". Personally, I don't see the harm in many
cases. Larry's point that "The way Auto-Numbers work can result in
gaps in the sequence that drive green-eyeshade bookkeepers and
accountants to distraction" is well taken and that may indeed be a
concern for things like cheque numbers. However, if your company's
database assigns me an Autonumber [Customer_ID] or [Account_Number] of
22597 then IMO it is just like my bank account number, or my driver's
license number, or any of the other essentially arbitrary identifiers
that various organizations assign to me.

Good luck with your project.
On Feb 27, 1:00 pm, "JohnH" <[e-mail]:

On Feb 26, 6:42 pm, "Albert D. Kallal" <[e-mail]>
wrote:

First, my apologies. that post is out of line. I should have done better.

My sorry.

However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

Yes, you are correct in the above. The so called foreign key
is a standard field you place in the child table. This foreign key
is NOT the primary key. It is a regular long number field.

So, even a child table will have its own primary key (usually the default
name for a autonumber field is "id", and that tends to be fine).

To relate the child table, you simply add anew field, and in that field you
place the value of the parent table id. So, yes, you are 100% correct in the
above that an additional field is placed into the table. Note that if you
use a sub-form, the ms-access can "set" the value used for the child field.
If you need to add records to a child table and you are not using a
sub-form, then ms-access WILL NOT set this value for you.

Once again, my apologies for that post (it was a rant). It was simply out of
line. It sounds likely you inherited a mess (but, lets not be too critical
on those who do mess up!!).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Thank you rkc, Albert and Larry. I was also having a very bad day
yesterday so I wasn't getting anywhere. I'm new to real database
development; I know a bit of SQL, VBA quite well, and I can navigate
around in Access and create fairly sophisticated forms, but I've never
really learned some of the fundamentals of the actual backend design,
I've spent most of my time messing around with driftwood-ductape-and-
string setups in small businesses.

I'm going to spend some time studying and then write up a schema.

I truly appreciate your help. What you told me about relating tables
is fairly simple and I should have gone with my instincts (I was told
by a selfavowed genius that I *had* to use customer-specific
information as the primary key for Customer). My task really then
(after I've designed a normalized backend) is to design a smart
interface that can, for example, resolve returning customers to their
PK. I suppose the flow of the program will be "search for customer by
{information}, if he exists add car sale, if not, add customer and car
sale."

Any other ideas you want to throw my way, feel free! Again, thank
you, what you guys do here for the public good is admirable.

John

[JohnH]

On Feb 27, 4:34 pm, "Gord" <[e-mail]:

Hey, John.

I was told by a selfavowed genius that I *had* to use customer-
specific information as the primary key for Customer

FWIW, there are some people who feel very strongly that Autonumber
primary keys (sometimes called "Identity columns") are an affront to
the *theory* of relational databases and should NEVER be used. It
should be noted that not all of these people are loonies. They simply
identify with the theoretical much more than the practical in these
matters, and I tend to disagree with them. (If you're curious, search
the comp.databases.ms-sqlserver group via groups.google.com for some
rather lively discussions over the years.)

There are also, as I'm sure you have noticed, some people who are
quick to point out that they would NEVER, UNDER ANY CIRCUMSTANCE do
some particular thing that lots of other people find fairly innocuous.
I'm not really sure what fuels their dogmatism, but it is a fact of
life.

Example: Albert's assertion in his first post that "users should NEVER
EVER see a autonumber". Personally, I don't see the harm in many
cases. Larry's point that "The way Auto-Numbers work can result in
gaps in the sequence that drive green-eyeshade bookkeepers and
accountants to distraction" is well taken and that may indeed be a
concern for things like cheque numbers. However, if your company's
database assigns me an Autonumber [Customer_ID] or [Account_Number] of
22597 then IMO it is just like my bank account number, or my driver's
license number, or any of the other essentially arbitrary identifiers
that various organizations assign to me.

Good luck with your project.

On Feb 27, 1:00 pm, "JohnH" <[e-mail]:

On Feb 26, 6:42 pm, "Albert D. Kallal" <[e-mail]>
wrote:

First, my apologies. that post is out of line. I should have done better.

My sorry.

However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

Yes, you are correct in the above. The so called foreign key
is a standard field you place in the child table. This foreign key
is NOT the primary key. It is a regular long number field.

So, even a child table will have its own primary key (usually the default
name for a autonumber field is "id", and that tends to be fine).

To relate the child table, you simply add anew field, and in that field you
place the value of the parent table id. So, yes, you are 100% correct in the
above that an additional field is placed into the table. Note that if you
use a sub-form, the ms-access can "set" the value used for the child field.
If you need to add records to a child table and you are not using a
sub-form, then ms-access WILL NOT set this value for you.

Once again, my apologies for that post (it was a rant). It was simply out of
line. It sounds likely you inherited a mess (but, lets not be too critical
on those who do mess up!!).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Thank you rkc, Albert and Larry. I was also having a very bad day
yesterday so I wasn't getting anywhere. I'm new to real database
development; I know a bit of SQL, VBA quite well, and I can navigate
around in Access and create fairly sophisticated forms, but I've never
really learned some of the fundamentals of the actual backend design,
I've spent most of my time messing around with driftwood-ductape-and-
string setups in small businesses.

I'm going to spend some time studying and then write up a schema.

I truly appreciate your help. What you told me about relating tables
is fairly simple and I should have gone with my instincts (I was told
by a selfavowed genius that I *had* to use customer-specific
information as the primary key for Customer). My task really then
(after I've designed a normalized backend) is to design a smart
interface that can, for example, resolve returning customers to their
PK. I suppose the flow of the program will be "search for customer by
{information}, if he exists add car sale, if not, add customer and car
sale."

Any other ideas you want to throw my way, feel free! Again, thank
you, what you guys do here for the public good is admirable.

John- Hide quoted text -

- Show quoted text -

Thank you for the clarification Gord. I've noticed some such
disagreement and I'm sure one day I'll develop some shade of righteous
indignation, ostensibly based on holy Logos.

Anyhow, I'm excited about learning these things. The more I've
studied today the more I've realized how useful it is to learn theory
before diving in with preconceived notions. My thinking concerning
entities and relationships was undisciplined and led to this whole
messy exchange.

I am very curious as to why some 'purists' feel autonumber primary
keys are an affront to RDBMS theory, I haven't yet come across that in
my reading.

John

[Gord]

>I am very curious as to why some 'purists' feel autonumber primary keys

>are an affront to RDBMS theory, I haven't yet come across that in my
reading.

Here's an interesting exchange from five years ago:

http://groups.google.ca/group/comp.d...109ff59c673154

On Feb 27, 7:48 pm, "JohnH" <[e-mail]:

On Feb 27, 4:34 pm, "Gord" <[e-mail]:

Hey, John.

>I was told by a selfavowed genius that I *had* to use customer-
>specific information as the primary key for Customer

FWIW, there are some people who feel very strongly that Autonumber
primary keys (sometimes called "Identity columns") are an affront to
the *theory* of relational databases and should NEVER be used. It
should be noted that not all of these people are loonies. They simply
identify with the theoretical much more than the practical in these
matters, and I tend to disagree with them. (If you're curious, search
the comp.databases.ms-sqlserver group via groups.google.com for some
rather lively discussions over the years.)

There are also, as I'm sure you have noticed, some people who are
quick to point out that they would NEVER, UNDER ANY CIRCUMSTANCE do
some particular thing that lots of other people find fairly innocuous.
I'm not really sure what fuels their dogmatism, but it is a fact of
life.

Example: Albert's assertion in his first post that "users should NEVER
EVER see a autonumber". Personally, I don't see the harm in many
cases. Larry's point that "The way Auto-Numbers work can result in
gaps in the sequence that drive green-eyeshade bookkeepers and
accountants to distraction" is well taken and that may indeed be a
concern for things like cheque numbers. However, if your company's
database assigns me an Autonumber [Customer_ID] or [Account_Number] of
22597 then IMO it is just like my bank account number, or my driver's
license number, or any of the other essentially arbitrary identifiers
that various organizations assign to me.

Good luck with your project.

On Feb 27, 1:00 pm, "JohnH" <[e-mail]:

On Feb 26, 6:42 pm, "Albert D. Kallal" <[e-mail]>
wrote:

First, my apologies. that post is out of line. I should have done better.

My sorry.

However, I still
don't fully understand (which it would appear is a criminal offense).
Autonumbers cannot repeat, which means that in a many-to-one
relationship between tables, the autoID field in Table1 will have to
be present alongside an ID field in Table2, correct? Or does one
create a third table that describes this relationship?

Yes, you are correct in the above. The so called foreign key
is a standard field you place in the child table. This foreign key
is NOT the primary key. It is a regular long number field.

So, even a child table will have its own primary key (usually the default
name for a autonumber field is "id", and that tends to be fine).

To relate the child table, you simply add anew field, and in that field you
place the value of the parent table id. So, yes, you are 100% correct in the
above that an additional field is placed into the table. Note that if you
use a sub-form, the ms-access can "set" the value used for the child field.
If you need to add records to a child table and you are not using a
sub-form, then ms-access WILL NOT set this value for you.

Once again, my apologies for that post (it was a rant). It was simply out of
line. It sounds likely you inherited a mess (but, lets not be too critical
on those who do mess up!!).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Thank you rkc, Albert and Larry. I was also having a very bad day
yesterday so I wasn't getting anywhere. I'm new to real database
development; I know a bit of SQL, VBA quite well, and I can navigate
around in Access and create fairly sophisticated forms, but I've never
really learned some of the fundamentals of the actual backend design,
I've spent most of my time messing around with driftwood-ductape-and-
string setups in small businesses.

I'm going to spend some time studying and then write up a schema.

I truly appreciate your help. What you told me about relating tables
is fairly simple and I should have gone with my instincts (I was told
by a selfavowed genius that I *had* to use customer-specific
information as the primary key for Customer). My task really then
(after I've designed a normalized backend) is to design a smart
interface that can, for example, resolve returning customers to their
PK. I suppose the flow of the program will be "search for customer by
{information}, if he exists add car sale, if not, add customer and car
sale."

Any other ideas you want to throw my way, feel free! Again, thank
you, what you guys do here for the public good is admirable.

John- Hide quoted text -

- Show quoted text -

Thank you for the clarification Gord. I've noticed some such
disagreement and I'm sure one day I'll develop some shade of righteous
indignation, ostensibly based on holy Logos.

Anyhow, I'm excited about learning these things. The more I've
studied today the more I've realized how useful it is to learn theory
before diving in with preconceived notions. My thinking concerning
entities and relationships was undisciplined and led to this whole
messy exchange.

I am very curious as to why some 'purists' feel autonumber primary
keys are an affront to RDBMS theory, I haven't yet come across that in
my reading.

John

[kingston via AccessMonster.com]

A good way to start might be to spend some time with the tutorial Northwind
database (search for northwind in the help). There are a number of built-in
sample databases that you might find useful too. Take a look at the
relationships window in Northwind to see how the fields are distributed
amongst the tables and study the relationships. If you are a visual learner,
I think that this will quickly help you to grasp the core concepts of
database design. The rest is a matter of learning how to do specific tasks
in Access, assuming you know how to take care of the customer side of things.
hth

JohnH wrote:

>I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of any of the principles of database design, so what i'm left with is
a junkpile. The only reason I can't start all over?....all the data
needs to be saved.

So here's the boiled down essence:
People visit our company wanting to buy a car. One of our sales guys
locates the car they want (using dealer inventory databases, car
sales, etc) and comes back with a price. The person (or couple) say
okay we want to buy it. They love it, become repeat customers, refer
friends, etc.

After the sale, I get a "deal jacket" with all the details of the
sale. I have to dataenter this information into a horrid orange
frontend and then the lame mutated radioactive hedgehod behind the
form shits on the data and puts it in mismarked cubbyholes.

The database as it exists now is -supposed- to allow us to enter
"deals," which is basically jargon for a car sale. Then later we can
allegedly audit data, verify dates, when plates were mailed, create
monthly reports, etc.

The database structure as it is frightning, and unless you're
massively catatonic it should make you at least giggle. (My reaction
of course isn't so light-hearted)

1 table "Customers"
1 table "Caps" (caps is jargon for the financial tallying of the sale,
which means the deal has been finalized, or "capped.")

[Customers:]
Sale Date
Names
Spouse
Address/Phone
Vehicle Year
Vehicle Make/Model
New/Used
VIN
Dealer
Consultant (our sales guy)
Referral Source
Financer

[CAPS]
Sale Date
Names
Sale Price
Our Cost
MBI
Extras
Extras Amount
Less Expenses
Consultant Initials (2 chars)
Commission

The 2 tables are related by three fields, mainly Sale Date, Last Name,
and First Name. This means there can be no one-to-many relationship
between a "Customer" and a "Sale," disregarding the fact that the
Customer table isn't really a customer table and the Cap table doesn't
really represent a sale.

I'm concerned here with core design. I haven't studied normalization
but I have the basic (what seems to be intuitive) idea of RDMS.

I am not going to fix the current database. The front end is
completely useless and the tables are junk. I'm starting from scratch
but will still need to get the old data into the new tables. This
will create a serious problem, described in a minute. Anyway,
utlimately I need to create a new table schema and that's where I
would like advice.

My current thoughts are:
The best way to model our business might be to create a logical entity
"Customer" and a logical entity "Sale." Or maybe three: Customer,
EntitySold, and Sale. This would allow the option of logging sales of
things other than cars like warranties and safety products. My vision
for the best form of interfacing with the tables would be treating
Customers as the primary data entity, Customers could be searched,
edited, actions perfomed upon, etc. Then, when a customer is
selected, all information relating to that customer will be available,
including of course his history of car purchases with us.

The problem then is how to relate these entities. As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible. I don't
think i'll be able to get SS#s, so I think my only option is to start
requiring the sales department to get Driver's licence numbers and
states as a requirement to capping their deals.

Assuming I have my hat on straight so far, the central task of
relating customers and their sales would mean a Customer table related
to the Sale table(s) related by two fields, DriversLicence# and
DriversLicenceState.

And now onto the problem I forsee. Assuming I can create this new
dabatase structure and painstakingly get the old data into its new
clothes. I'm going to have, essentially, old data in each table
lacking a key (DriversLicense), that which relates the customer with
his sale(s). And even if I were to give that old data arbitrary keys
to maintain their relationship, that brings up all kinds of new
problems which I probably don't have to enumerate.

Anyway, this is where I peter out because I don't have any experience
with this. Can someone give me some advice on my thoughts so far and
where I might go from here? I must admit I'm feeling slightly
overwhelmed.

Thank you for reading my novellete,
John

p.s.
I'm running Access 2000 mdb

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[JohnH]

On Feb 28, 6:12 am, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

A good way to start might be to spend some time with the tutorial Northwind
database (search for northwind in the help). There are a number of built-in
sample databases that you might find useful too. Take a look at the
relationships window in Northwind to see how the fields are distributed
amongst the tables and study the relationships. If you are a visual learner,
I think that this will quickly help you to grasp the core concepts of
database design. The rest is a matter of learning how to do specific tasks
in Access, assuming you know how to take care of the customer side of things.
hth

JohnH wrote:

I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of any of the principles of database design, so what i'm left with is
a junkpile. The only reason I can't start all over?....all the data
needs to be saved.

So here's the boiled down essence:
People visit our company wanting to buy a car. One of our sales guys
locates the car they want (using dealer inventory databases, car
sales, etc) and comes back with a price. The person (or couple) say
okay we want to buy it. They love it, become repeat customers, refer
friends, etc.

After the sale, I get a "deal jacket" with all the details of the
sale. I have to dataenter this information into a horrid orange
frontend and then the lame mutated radioactive hedgehod behind the
form shits on the data and puts it in mismarked cubbyholes.

The database as it exists now is -supposed- to allow us to enter
"deals," which is basically jargon for a car sale. Then later we can
allegedly audit data, verify dates, when plates were mailed, create
monthly reports, etc.

The database structure as it is frightning, and unless you're
massively catatonic it should make you at least giggle. (My reaction
of course isn't so light-hearted)

1 table "Customers"
1 table "Caps" (caps is jargon for the financial tallying of the sale,
which means the deal has been finalized, or "capped.")

[Customers:]
Sale Date
Names
Spouse
Address/Phone
Vehicle Year
Vehicle Make/Model
New/Used
VIN
Dealer
Consultant (our sales guy)
Referral Source
Financer

[CAPS]
Sale Date
Names
Sale Price
Our Cost
MBI
Extras
Extras Amount
Less Expenses
Consultant Initials (2 chars)
Commission

The 2 tables are related by three fields, mainly Sale Date, Last Name,
and First Name. This means there can be no one-to-many relationship
between a "Customer" and a "Sale," disregarding the fact that the
Customer table isn't really a customer table and the Cap table doesn't
really represent a sale.

I'm concerned here with core design. I haven't studied normalization
but I have the basic (what seems to be intuitive) idea of RDMS.

I am not going to fix the current database. The front end is
completely useless and the tables are junk. I'm starting from scratch
but will still need to get the old data into the new tables. This
will create a serious problem, described in a minute. Anyway,
utlimately I need to create a new table schema and that's where I
would like advice.

My current thoughts are:
The best way to model our business might be to create a logical entity
"Customer" and a logical entity "Sale." Or maybe three: Customer,
EntitySold, and Sale. This would allow the option of logging sales of
things other than cars like warranties and safety products. My vision
for the best form of interfacing with the tables would be treating
Customers as the primary data entity, Customers could be searched,
edited, actions perfomed upon, etc. Then, when a customer is
selected, all information relating to that customer will be available,
including of course his history of car purchases with us.

The problem then is how to relate these entities. As far as my
thinking is concerned, the only way to track customers is to use a key
for them that is specific to their person: SS#, Drivers license, etc.
I don't see how using arbitrary numbers would be possible. I don't
think i'll be able to get SS#s, so I think my only option is to start
requiring the sales department to get Driver's licence numbers and
states as a requirement to capping their deals.

Assuming I have my hat on straight so far, the central task of
relating customers and their sales would mean a Customer table related
to the Sale table(s) related by two fields, DriversLicence# and
DriversLicenceState.

And now onto the problem I forsee. Assuming I can create this new
dabatase structure and painstakingly get the old data into its new
clothes. I'm going to have, essentially, old data in each table
lacking a key (DriversLicense), that which relates the customer with
his sale(s). And even if I were to give that old data arbitrary keys
to maintain their relationship, that brings up all kinds of new
problems which I probably don't have to enumerate.

Anyway, this is where I peter out because I don't have any experience
with this. Can someone give me some advice on my thoughts so far and
where I might go from here? I must admit I'm feeling slightly
overwhelmed.

Thank you for reading my novellete,
John

p.s.
I'm running Access 2000 mdb

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

Thank you, I'll check out that exchange and Northwind indepth.

[ManningFan]

Well, when so many solid Access DBAs are looking for jobs, I always
feel like people such as yourself are akin to "outsourcees".
Employers hire the cheap guys with no experience, so they can come
"waltzing" in (heh, I like the imagery on that one...) and ask how to
set up a relational DB. It's not something everyone can do or even
learn; you either have it or you don't. So I take offense to that,
like I gotta protect my Access homies from people tryin' to creep up
on our turf. Dig?

Aside from that, Microsoft's website is actually useful in that it has
a plethora of sample databases to download. And not just "sample" as
in, "This is just to teach you", but you can actually download Access
databases that allow you to catalog your music collection, follow
business project pipelines, track personal finances and a bunch more.
Check http://office.microsoft.com/en-us/te...s/default.aspx and
click on the "Databases"link.

On Feb 27, 6:55 pm, "JohnH" <[e-mail]:

I don't believe
I'm thin-skinned, but when I receive two responses to an innocent
question, one dismissing me altogether (tell your boss to hire a real
developer), and the other wondering how I could be so dumb, I feel
that expressing my frustration is warranted.

[Tim Marshall]

rkc wrote:

You've been living in a bubble if you think someone coming in here
for the first time is going to be able to decipher some of the shit
Albert comes up with.

???????????

Albert is a very respected contributer to the group. I must have missed
whatever spat the two of you had, but it always sucks when two
knowledgeable people come to loggerheads like that. 8(
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[JohnH]

On Mar 1, 3:31 am, "ManningFan" <[e-mail]:

Well, when so many solid Access DBAs are looking for jobs, I always
feel like people such as yourself are akin to "outsourcees".
Employers hire the cheap guys with no experience, so they can come
"waltzing" in (heh, I like the imagery on that one...) and ask how to
set up a relational DB. It's not something everyone can do or even
learn; you either have it or you don't. So I take offense to that,
like I gotta protect my Access homies from people tryin' to creep up
on our turf. Dig?

Aside from that, Microsoft's website is actually useful in that it has
a plethora of sample databases to download. And not just "sample" as
in, "This is just to teach you", but you can actually download Access
databases that allow you to catalog your music collection, follow
business project pipelines, track personal finances and a bunch more.
Checkhttp://office.microsoft.com/en-us/templates/default.aspxand
click on the "Databases"link.

On Feb 27, 6:55 pm, "JohnH" <[e-mail]:

I don't believe
I'm thin-skinned, but when I receive two responses to an innocent
question, one dismissing me altogether (tell your boss to hire a real
developer), and the other wondering how I could be so dumb, I feel
that expressing my frustration is warranted.- Hide quoted text -

- Show quoted text -

I get it Manning, and ultimately, I didn't realize my place or who I
was talking to. But I'd rather get the cold shoulder than be spooked
outta town. As far as databases go, I'm game, and I guess that might
piss you off. At least I can say I'm not a college dropout hotshot
with toilet paper stuffed in his resume, or some Ramashamajama from
India. I'm no DBA, and I'm very aware of my ignorance, but if it's my
good fortune to be in a position to become an Access DBA, I'd like to
think you'd be less upset about another colleague and more upset about
the real problems that prevent Access DBAs from getting the jobs they
deserve.

Thanks for the links. Shit, after all the replies, I feel like I'm
part of some initiation ritual ;P

[JohnH]

On Mar 1, 4:51 pm, "JohnH" <[e-mail]:

On Mar 1, 3:31 am, "ManningFan" <[e-mail]:

Well, when so many solid Access DBAs are looking for jobs, I always
feel like people such as yourself are akin to "outsourcees".
Employers hire the cheap guys with no experience, so they can come
"waltzing" in (heh, I like the imagery on that one...) and ask how to
set up a relational DB. It's not something everyone can do or even
learn; you either have it or you don't. So I take offense to that,
like I gotta protect my Access homies from people tryin' to creep up
on our turf. Dig?

Aside from that, Microsoft's website is actually useful in that it has
a plethora of sample databases to download. And not just "sample" as
in, "This is just to teach you", but you can actually download Access
databases that allow you to catalog your music collection, follow
business project pipelines, track personal finances and a bunch more.
Checkhttp://office.microsoft.com/en-us/templates/default.aspxand
click on the "Databases"link.

On Feb 27, 6:55 pm, "JohnH" <[e-mail]:

I don't believe
I'm thin-skinned, but when I receive two responses to an innocent
question, one dismissing me altogether (tell your boss to hire a real
developer), and the other wondering how I could be so dumb, I feel
that expressing my frustration is warranted.- Hide quoted text -

- Show quoted text -

I get it Manning, and ultimately, I didn't realize my place or who I
was talking to. But I'd rather get the cold shoulder than be spooked
outta town. As far as databases go, I'm game, and I guess that might
piss you off. At least I can say I'm not a college dropout hotshot
with toilet paper stuffed in his resume, or some Ramashamajama from
India. I'm no DBA, and I'm very aware of my ignorance, but if it's my
good fortune to be in a position to become an Access DBA, I'd like to
think you'd be less upset about another colleague and more upset about
the real problems that prevent Access DBAs from getting the jobs they
deserve.

Thanks for the links. Shit, after all the replies, I feel like I'm
part of some initiation ritual ;P- Hide quoted text -

- Show quoted text -

BTW, if it wasn't obvious, Ramashamajama is supposed to be commenting
on IT outsourcing and not on race, toward which I harbor no bigotry.

[storrboy]

>
Thanks for the links. Shit, after all the replies, I feel like I'm
part of some initiation ritual ;P

Ahh, but there's the panty raid and keg-fest yet to come....