forms for tables set up as "one to one"

[Guest]

I am having trouble with forms I designed for tables that have a one-to-one
relationship, or at least I believe that's the problem. Here's my overall
design strategy: I have a total of 20 tables; 13 are set up as one-to-many
and the forms are working fine. 4 are junction tables, which worked well
until I designed the second and third forms for my 3 primary tables, (which I
split because I kept getting error messages about required information in
parts of the table not included in my first form). Our process happens in
three phases, so I wanted three different forms to populate the records at
different times). The "Phase 1" form was working nicely, including a
subform, until I designed a form for "Phase 2". Now, suddenly, the subform
has stopped allowing input. There is no error message; it just won't allow
the cursor to rest there. The Phase 2 and 3 forms are based on tblPhase1
with subforms for tblPhase2 and tblPhase3. Please help, as my department
needs this database badly, and I'm getting embarrased about saying "almost
ready"! Thanks so much!

 

[Steve Schapel]

Babe,

In my experience, it is a mistake to design your table structure
according to data entry or workflow or user interface considerations.
It is much better to temporarily forget about these things, and focus
first of all on designing your tables according to proper database
principles. Then you can make your forms etc conform to the data
realities, rather than the other way around.

So can you tell us something about the data you are working with, and
the nature of the relationships between the various data entities, and
the tables and fields that you think you might need for normalised data
storage requirements?

 

[Guest]

Thanks so much, Steve. I tried to adhere to the principals of efficient
design in my tables, as follows. Each table also includes an auto-num, which
the user does not see. Any suggestions yopu can offer would be appreciated!

JobID (autonum)

We want a “tracking number†on each item, which is a concatenation of

three >pieces of information:
JobCategory, (two digit alpha/num representation of “brochure, web page,

stationary, advertisementâ€, set up in separate many-side table, below)

Year, (represented in two digits (06, 07 etc.)
Counter- 3 digit number from 000 to 999 (using dMax + 1 with multi-user
language)

the other pieces of information in the main table(s) are set up as follows:

ProjectTitle (text)
ParentCampaign (link to Campaign table, below)
ProjectManager (link to Contact table below)
ClientID (link to Contact table below)
BasedonExisting (text)
Description (text)
FirstContactDate (date)
TargetMarket (text)
MailingHouse (link to Contact table, below)
To MailingHouse (date)
Inv Recd Date (date)
CircDropDate (date)
Product (link to Product table, below)
CrossSell (link to Product table, below)
AssignDate (date)
ArtistID (link to Contact table, below)
ProofDate (date)
VendorID (link to Contact table, below)
ToVendDate (date)
VendorProofDate (date)
FirstEditorialDate (date)
FinalEditorialDate (date)
ClientSignDate (date)
PgmManagerSignoffDate (date)
Status (link to Job Status table, below)
EstimatedDesignCost (currency)
FinalDesignCost (currency)
EstMailCost (currency)
FinMailCost (currency)
EstMktCost (currency)
FinalMktCst (currency)
EstPrintCost (currency)
FinPrintCost (currency)
TrimSize (text)
BleedSize (text)
LiveArea (text)
ColorSpec (Link to ColorSpec table)
ColorDetail (link to Color Detail table, below)
ArtNotes (text)
FileLoc (hyperlink to graphics files)
toCDDate (date)
CDIndex (text)
DistributionTrack (subforms, link to Distribution Table, below)
SuccessTrack (subforms, link to Success Table, below)

_______________________________________________________________________

Campaign Table (lookup from main):

ID (autonum)
Title (text)
Desc (text)
ProdID (link to Product Table)
BudgetNum (text)

Product/Service Table (lookup from main)::

ID (autonum)
Name (text)
Desc (text)
Detail (memo)
InvNum (text)
ISBN (text)
NoPages (text)
SpineSize (text)

Contact Table: Client, Vendor, Mailing, Artist, Project Manager (lookup from main):

ID (autonum)
First (text)
Last (text)
Office (text)
addr1 (text)
addr2 (text)
city (text)
state (text, input masked)
zip (text, input masked)
phone (text, input masked)
ext (text, input masked)
fax (text, input masked)
email (text)
notes (memo)

Job Status Table (“new, to artist, to vendor, to manager, to client, completedâ€, etc. Note: appears on all forms in editable state to track job progression)

StatusCode (two digit number) (lookup from main):
Description (text)

Distribution table: (Junction Table, Job to ClientContact)

JobID (linked to autonum in primary table)
Dist Quant (number)
Client (linked to contact table)

Color Spec Table (Black & White, 2 Color, 3 Color, 4Color etc) (lookup from main)

Code (text)
Description (text)

Color Detail Table (Junction Table JobID to Detail ID)

DetailID
Color Location (text)
PMS Number (from PMS Table, below)

Success Table (With Junction Table, Success ID to Job ID )

ID (autonum)
PeriodEnd
WebClicks
OrdsRef
Revenue

PMS Table

PMS (text)
Alias (text)

 

[Guest]

Well, I had this all neatly formatted in Word, with bolded field names and
indented notes, but when I tranferred it over here, I lost my formatting. If
the sloppiness of the post is the reason I never heard back, please try to
find it in your heart to forgive me.

It would be awfully nice if Microsoft would support their products, rather
than dierecting users to fend for themselves, in convoluted warrens of grief
like this one. Our company has purchased about 300 copies of Access, and I
purchased my own copy to work on this at home. The help language is drivel
geared to either the lowest level use or the highest technical level. There
is no middle ground.

Of course, this is not the place to voice frustration, but I have been round
and round the rugged Microsoft Web Sites until I am blue, and there's just no
way to find the appropriate venue. I am bitter and frustrated.

But what the hell...I just wanted to see this in print somewhere. No one
reads it anyway....

 

[Steve Schapel]

Babe,

I apologise that this thread slipped through my "watched threads" net,
for some unknown reason.

I am also sorry to hear of the frustrations you have been experiencing.
I suppose it is not really comforting to know that almost everyone
finds it a steep learning curve to get on top of database work. I hope
you will continue to seek help in these forums, and that you can find a
good Access book to refer to.

The project you are working on seems to have some tricky aspects, so for
a teeth-cutting example it's pretty challenging.

I can't really fully grasp how it all hangs together, but it seems you
have made a radical change from the design you first described to us,
with its 20 tables. It would help if you could give a synopsis of the
database and its purpose, just in English, i.e. not in terms of tables
and fields. Well, it's good to see the tables and fields too, but I
mean to help convey the meaning and purpose. Like this: "My database is
for the operation of a library. We need to keep a complete catalogue of
resources, including books, videos, and periodicals. We need a register
of our members. And we need to track the members' borrowing and
returning of items from the library, including the payment of any charges."

In the meantime, there would appear to be a couple of immediate changes
I would make to the design of the main table as you have outlined in
your other post.

I would remove these fields from that table:
FirstContactDate (date)
To MailingHouse (date)
Inv Recd Date (date)
CircDropDate (date)
AssignDate (date)
ArtistID (link to Contact table, below)
ProofDate (date)
VendorID (link to Contact table, below)
ToVendDate (date)
VendorProofDate (date)
FirstEditorialDate (date)
FinalEditorialDate (date)
ClientSignDate (date)
PgmManagerSignoffDate (date)

Instead, I would have a separate linked table, let's say it is called
Progress, with these fields:
ProjectID (to link to main table)
StepTaken
ProgressDate
AdditionalInfo

So, each of the steps in the process becomes a separate record in this
table, rather than a separate field in the main table.

If I understand correctly, this field can also be removed:
Status (link to Job Status table, below)
This can ber deduced in a query based on the most recent entry in the
Progress table, so should not be stored in the table, or subject to data
entry/editing.

I would also remove these fields from the main table:
EstimatedDesignCost (currency)
FinalDesignCost (currency)
EstMailCost (currency)
FinMailCost (currency)
EstMktCost (currency)
FinalMktCst (currency)
EstPrintCost (currency)
FinPrintCost (currency)

Instead, I would have a separate table with these fields:
ProjectID (to link to main table)
Cost
CostType

So, each of the cost items becomes a separate record in this table,
rather than a separate field in the main table.

I hope these few comments are helpful.

 

[Guest]

Thank you, Steve. These suggestions are very helpful. Here's my situation
in plain English:
I wish to set up a database to track all the tasks our marketing department
works on. We process about 1,000 to 2,000 tasks each year. The information
we need to store for each task is fairly complex; each task relates to a
campaign: a campaign may require many tasks or only a few. The tasks vary by
type: they may be magazine ads, book covers, pamphlets, web pages, forms,
etc. We want to record when each step of the task is completed, (usually 5
to 8 steps), which Marketing Rep is in charge of it, which Artist it has been
assigned to, which department (client) requested it, which vendor printed or
produced it for us, which mailing house distributed it, and what the
estimated and final costs are for each of four facets of the task: (Marketing
Time, Artist Time, Printing Costs and Mailing Costs) . We also need to have
the colors assigned by the artist and the particulars about the task on
record: overall size, bleed size, graphic file type, hyperlink to graphics
file, print specs, and notes to the vendor. Lastly, we want to record how
many of these projects provoke a response from our customer, by recording how
many orders are placed, the resulting revenue, and how many web
click-throughs a particular ad recieved.

I have tried various permutations of the tables setup I posted. In some
cases, the vendor, client, mailing house, etc each had separate tables. In
the example I poted, they were merged into "contacts", with types assigned.

I welcome any and all suggestions!

 

[Larry Linson]

. . . I tried to adhere to the principals of efficient
design in my tables, as follows. . . .

One-to-one relationships are (almost?) never due to good relational database
design principles, but (almost?) always due to expediency -- as you stated
that yours were, to "group" the data according to the three stages or
phases.

Form/subform relationships where the subform contains data from the same
record as the main form are often problematical. It may be that some similar
effect is occurring in your case.

I am sure that you checked all the items under the Data tab in the
Properties of the Form(s) embedded in the Subform Control to make certain
none properties of those are set in a way that interferes with taking action
on those forms.

With these, perhaps extraneous, comments I am going to leave you in Steve's
very capable care.

Larry Linson
Microsoft Access MVP

 

[Steve Schapel]

Babe,

Thanks for the further clarification of your project.

Ok, well it seems that ou are getting it sorted. I will confirm my
previous comments about a separate table for the steps completed for a
task, and a separate table for costings, along the lines I suggested. I
would also comment on your Tracking Number idea... These three atoms of
information should not be combined into one field in your table. You
need a field in the table for the TaskType, and you will also have the
date, and you can do your incremented counter in a separate field. You
can easily concatenate from the data in these three fields for your
TrackingNumber when required on form or report.

I agree with your current idea of a single Contacts table, with a Type
field. I guess the only time I would vary from this is if it happened
sometimes that a particular person fell into more than one contact category.

So what are your specific questions now?