Design Advice

[Andy Bailey]

I've been working on a proposals database in Access 2000. At the moment we
use Word to cobble proposals together.

Simply put a proposal is linked to a customer. Each proposal can consist of
any number of sections and within each section are the individual
machines/parts etc that are drawn from a products table.

Quite often the customer will review the submitted proposal and request a
few changes here and there. In Word it's easy to copy all the section
documents and then make your revisions (rev 1) thereby keeping a copy of the
original for reference. We can have many revisions before persuading the
customer to part with his/her cash.

If I change the Access records for that proposal obviously the original is
lost. I could create a new proposal with a new ID number and copy each
section one by one using an Append query but this is a lengthy process (with
my Access knowledge anyway) and it would mean our drawing office would have
to change their drawing numbers to suit the proposal number.

Could I solicit some guidance as to how best approach this problem?

Any help/pointers gratefully received.

Thanks, Andy

 

[John W. Vinson]

I've been working on a proposals database in Access 2000. At the moment we
use Word to cobble proposals together.

Simply put a proposal is linked to a customer. Each proposal can consist of
any number of sections and within each section are the individual
machines/parts etc that are drawn from a products table.

Quite often the customer will review the submitted proposal and request a
few changes here and there. In Word it's easy to copy all the section
documents and then make your revisions (rev 1) thereby keeping a copy of the
original for reference. We can have many revisions before persuading the
customer to part with his/her cash.

If I change the Access records for that proposal obviously the original is
lost. I could create a new proposal with a new ID number and copy each
section one by one using an Append query but this is a lengthy process (with
my Access knowledge anyway) and it would mean our drawing office would have
to change their drawing numbers to suit the proposal number.

Could I solicit some guidance as to how best approach this problem?

It's a bit hard to determine from this what your table structure might be. I'd
expect you would have at least tables for:

Customers
CustomerID
LastName
FirstName
<contact information>

Proposals
ProposalID
CustomerID <link to Customers>
<details about the proposal as a whole>

Sections
SectionID <Primary Key>
RevisionNo <Integer, part of two-field Primary Key>
ProposalID <link to Proposals>
<fields about this section - I have no idea what this might be>

You don't - I don't think - really need a new PROPOSAL for each revision of
any step; if you have a query selecting only the most up-to-date (highest
revision number) of each Section you can compose the proposal on the fly.

John W. Vinson [MVP]

 

[Andy Bailey]

John

Many thanks for your reply; appreciate your help.

You pretty much got the table structures spot on. The RevNo isn't in (yet)
but would go in the Proposals table. Within the Sections I call up
machines/prices from a products table and then add quantities, whether they
are to be new/existing/client supply etc.

What I have difficulty with is understanding how, when I up the revision
number and make changes to the line items within the proposal section, to
keep what was there originally.

Does it make sense to consider creating some form of "archive" table that
you populate with all the proposal/sections info when you create a new
revision? I'm an Access hobbyist with very limited skills and the basics and
obvious elude me at times.

Again, many thanks.


Andy

 

[John W. Vinson]

John

Many thanks for your reply; appreciate your help.

You pretty much got the table structures spot on. The RevNo isn't in (yet)
but would go in the Proposals table. Within the Sections I call up
machines/prices from a products table and then add quantities, whether they
are to be new/existing/client supply etc.

What I have difficulty with is understanding how, when I up the revision
number and make changes to the line items within the proposal section, to
keep what was there originally.

The revision number would go IN A NEW RECORD in the table. The old records
would still *be* there.

You're not overwriting anything!

Does it make sense to consider creating some form of "archive" table that
you populate with all the proposal/sections info when you create a new
revision? I'm an Access hobbyist with very limited skills and the basics and
obvious elude me at times.

The proposals/revisions table *is* the archive. You'll want to use a query to
display only the most current revision, for most purposes.

John W. Vinson [MVP]

 

[Andy Bailey]

John

The penny finally dropped after your last reply. Thank you.

I had become hung up on the revision being a single entity within the
proposal rather than assigning a revision to each line item.

Thank you for your time and patience.

Kind regards


Andy