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

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" <JohnHarri...@g mail.comwrote:

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

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" <rickyae...@gma il.comwrote:

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...t hen 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...last name..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" <PleaseNOOOsPAM [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...t hen 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...last name..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
pleaseNOOSpamKa [e-mail] p://www.attcanada.n et/~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" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [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