Database Design -

G

Guest

BACKGROUND INFO:
We have a software company that allows potential users to download and
pre-try our software. When someone fills out a webform we place their info
(name, address, email, etc) in a single table ("entryTable") in access. We
then forward their information to the regional re-sellers in their part of
the world (a list of 1-10 individuals). The names and email for the
re-sellers is in a 2nd table ("resellers"). The regions are recorded in a 3rd
table "salesRegions".

MY QUESTION:
I have a giant list of emails in Outlook which record the manual forwarding
of downloader information to resellers. We currently have no way to
enter/track that information in our database.

What I envision is a form that has a field where I can search for an email
from an already entered downloader and select a region. This would trigger a
list of resellers in that area (maybe with check boxes?) that I could select.
(Because resellers change, it seems important to link downloaders directly to
the current individual resellers rather than just their region.) The form
would also have a field to record the date the info was forwarded and a notes
field to capture any thing unusual about the transaction (for example - if
the downloader was a person of particular interest). Ideally, the same form
might be used to track further correspondence to, from and about the
downloader.

If this is a good approach how would I do it? I'm having trouble visualizing
how fields that track the dates of contact back & forth would look. Right now
I can only think of a series of fields (lamely) "date_1st_contact_from_us"
"date_1st_contact_from_them"
"date_2nd_contact_from_us""date_2nd_contact_from_them"

When I go further than that imagining a table or series of tables that link
the resellers with the downloaders and record a series of dates and notes -
it's like the 3 stooges ("you try to think and nothing happens").

DAMMIT JIM - I'M A WEBDESIGNER NOT A DB ADMIN:
In the moment, I would be very grateful if anyone can step me through the
process to get the resellers linked to the downloaders so I can produce the
report that our owner is asking for. As I've been reading the posts in this
forum, I notice that I am fuzzy on some of the terms (for example, I only
sort-of grasp "combo box") and I have no idea where or how to "input code" so
it would be impossible to insult me by over-explaining or using baby steps.
To put it bluntly - when it comes to access, I'm definitely ignorant and
might possibly be stupid.

In the medium term, I would like to outsource this sort of stuff if I can
find someone trustworthy, competent and reasonably affordable.

ADDITIONAL BACKGROUND WHINING:
Through banging and dumb luck I have come up with a form that works pretty
well for entering downloaders ("entryForm"). For example, I got the entryForm
fields to suggest completions based on previous data. This was the result of
a fortunate dinking around with queries on one highly caffeinated morning,
and I've just been copying and pasting the field and adjusting its properties
since. In short, I have no idea what I did, or how to reproduce it and I'm
nervous about making changes.

It's tedious to enter each email by hand into the above form and I
frequently fall behind, so....My current work flow is to parse the Outlook
forms into notetab pro, then into excel then copy and paste the data into
entryTable. (On a separate issue my access will no longer import from excel
and reregistering the dlls doesn't work :S) A requirement that each
downloader have a unique email keeps me from duplicating records. (Sort
of...the Chinese, in particular seem to have multiple email addys) In a
perfect world this would be an automated process from the forms that come in
to Outlook from our website.

Since I started pasting, access started giving me a table called
"PasteErrors" which is a bonus, since I can then go and manually note in the
record that this individual has asked for a 2nd/3rd download and pass this
info onto the reseller. This data goes into the notes field - which seems
really sub-optimal.. All this I'm willing to limp along with, but we're
blowing it by not capturing/tracking what happens to the downloaders once
they're in our database.

Thanks
Diane Meriwether
Biomechanics Research Group
www.lifemodeler.com
(e-mail address removed)
 
D

Douglas J. Steele

Sorry, I didn't read your post that closely, but you definitely don't want a
collection of fields with names like "date_1st_contact_from_them"
"date_2nd_contact_from_us" and "date_2nd_contact_from_them": that's normally
referred to as a repeating group, and it violates database normalization
principles.

What you want is a 2nd table with an FK pointing back to your existing table
and an additional field (or two) to indicate what type of contact it is:
probably a field for "contact from whom" and another for "contact number".
Each contact would be a separate row in this separate table.
 
V

Vincent Johns

Douglas said:
Sorry, I didn't read your post that closely, but you definitely don't want a
collection of fields with names like "date_1st_contact_from_them"
"date_2nd_contact_from_us" and "date_2nd_contact_from_them": that's normally
referred to as a repeating group, and it violates database normalization
principles.

There are a couple of problems that this "repeating group" creates.

(1) If you don't know how many, or which, dates you'll need to record,
each new type will call for adding an entire new field to all the
records in that table. Many records will have null values there, but
they'll still take space and reduce your speed.

(2) If each date is associated with a couple of additional fields, such
as a type of contact or someone's name, you'd have multiple fields in
the table that do equivalent work. So, any change you make to one you'd
have to make to all the others of the same type. :-(
What you want is a 2nd table with an FK pointing back to your existing table
and an additional field (or two) to indicate what type of contact it is:
probably a field for "contact from whom" and another for "contact number".
Each contact would be a separate row in this separate table.

.... not to mention a field identifying the date (which I think may have
been the main kind of information you wanted saved there)!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top