Save data as a "template"

Greetings,

This may seem painfully long, and I do apologize, but maybe more info from
the start will help in the end...

I have developed a database for my new plumbing business. Part of the
system is used for bidding projects. In each project there are a number of
fixtures (i.e sinks, toilets, etc) and each of these fixtures consists of
numerous parts. The way the db is set up, I build "assemblies" for each
specific fixture, then use append queries to load them into specific
locations. For instance, I create Lavatory #1 with faucet #1 and sink #1 and
Lavatory#2 with faucet #2 and sink #1. The master bathroom will have 2 of
the Lavatory #1's and the secondary bathrooms (2 of these) will each have 1
of Lavatory #2 (I hope that wasn't too confusing!)
This works all well and fine, but I know it violates the #1 rule of good
database design since I am duplicating data. As my database grows, this will
become an obvious problem.
But aside from all that, what I want to ultimately achieve is to save the
entire mess as a template which then can be loaded in a new bid and just make
a few alterations instead of having to do the whole thing over.
I am using Access 2007 now, but the existing system was built in 2003.
To help clarify things a bit, here's sort of an outline structure of how it
presently works.

-The Assemblies Form-
Parent form has fixture type and fixture code
Subform lists the individual components to make that assembly.

-The Locations Form-
Somewhat similar to the Assemblies Form, but different in the Parent form
aspect
Parent form has Location (where this fixture will be) and a pull down for
Fixture Code (the pull down list is populated from the fixture codes filled
in the Assemblies form). When a selection is made in the Fixture code, the
subform is auto-filled with the values that corresponded with that fixture
code in the assemblies form. Since it is an append query, the information
can be changed if necessary without altering the original assembly.

Again, what I am ultimately trying to do is once this whole bid is done, I
would like to reload it and just change a few things here and there as
necessary for a new bid. However, I can't seem to figure out how to do it.
Any ideas?

Sorry this post was so long!

No need to apologize. Quite the contrary in fact; its lack of information
which is our more common problem. However, what you haven't told us is what
tables you have, only the forms, so we need to deduce from those what the
'logical model' is, i.e. the tables and the relationships between them. So
my assumptions below might not be correct.

It seems to me that your model for assemblies is fine. What you have, I
assume is an Assemblies table (your table names will probably differ of
course), a Components table and an AsemblyComponents table which models the
many-to-many relationship between them likes so:

Assemblies----AsemblyComponents----Components

where the and characters represent the 'many' side of each one-to-many
relationship type. Your subform will be based on the AsembyComponents table.

You don't say anything about how you are recording each bid, but I'd
envisage a Bids table, which will include columns such as BidID (its primary
key), CustomerID, BidDate etc.

The Locations table will include a foreign key BidID column referencing the
primary key of Bids. There is a many-to-many relationship type between
locations and assemblies so a table is needed to model this, having foreign
key columns referencing the primary key of each, and a quantity column. I'll
call this LocationFixtures.

The LocationFixtures table will be referenced by a LocationAssemblies table,
which in turn will reference a LocationAssemblyComponents table which will
then reference the Components table. It’s the LocationAssemblies and
LocationAssemblyComponents tables which I envisage you are filling with your
append queries. Lets just say at this stage that in principle this is not
introducing redundancy as each assembly can differ in its specification from
the original 'standard' specification (as contained in the AsemblyComponents
table). So the model for bids looks like this:

Bids---Locations---LocationFixtures---LocationAssemblies----LocationAsemblyComponents----Components

So, cutting to the chase, to use an existing bid as a 'template' for a new
bid is essentially the same process as using the original assemblies as the
basis for the location assemblies by means of your append queries. What you
need therefore is another set of append queries to insert new rows into
Locations, LocationFixtures, LocationAssemblies and
LocationAsemblyComponents, identical to those for the 'template' bid, but
with a different BidID and LocationID values in Locations, and a different
LocationID value in LocationFixtures reflecting the new bid. You can then
edit the new rows in the Locations, LocationFixtures, LocationAssemblies and
LocationAsemblyComponents tables as necessary, inserting or deleting rows,
changing the specifications of an assembly etc.

As I've described it above every bid would be a potential template, so you'd
have to select from all of them which is most appropriate as the 'template'
for a new bid. You could make matters easier by adding a Templates table
with columns descriptive of the 'template' so you can easily select one. As
you'd probably want to have a number of bids as possibilities associated with
each template, with the same bid possibly being an option for more than one
template, you'd have another table to model the many-to-many relationship
types in the usual way:

Templates---TemplateBids---Bids

Finally, all of the above is written off the top of my head of course,
without any opportunity to test the model. So don't accept it as gospel.
The first thing I'd do if developing a model like this would be to draw it
out diagrammatically on paper, with boxes for each table and directional
lines between them to represent the relationships - what's called an entity
relationships diagram. I'd then ask as many questions of the model as I can
think of on the basis of my experience with the real world objects and
processes which it represents, testing it as thoroughly as possible on paper
before implementing it. Then I'd thoroughly test the prototype application
before letting it loose into the wild.

Ken Sheridan
Stafford, England

"Dr Steevil" wrote:

Greetings,

This may seem painfully long, and I do apologize, but maybe more info from
the start will help in the end...

I have developed a database for my new plumbing business. Part of the
system is used for bidding projects. In each project there are a number of
fixtures (i.e sinks, toilets, etc) and each of these fixtures consists of
numerous parts. The way the db is set up, I build "assemblies" for each
specific fixture, then use append queries to load them into specific
locations. For instance, I create Lavatory #1 with faucet #1 and sink #1 and
Lavatory#2 with faucet #2 and sink #1. The master bathroom will have 2 of
the Lavatory #1's and the secondary bathrooms (2 of these) will each have 1
of Lavatory #2 (I hope that wasn't too confusing!)
This works all well and fine, but I know it violates the #1 rule of good
database design since I am duplicating data. As my database grows, this will
become an obvious problem.
But aside from all that, what I want to ultimately achieve is to save the
entire mess as a template which then can be loaded in a new bid and just make
a few alterations instead of having to do the whole thing over.
I am using Access 2007 now, but the existing system was built in 2003.
To help clarify things a bit, here's sort of an outline structure of how it
presently works.

-The Assemblies Form-
Parent form has fixture type and fixture code
Subform lists the individual components to make that assembly.

-The Locations Form-
Somewhat similar to the Assemblies Form, but different in the Parent form
aspect
Parent form has Location (where this fixture will be) and a pull down for
Fixture Code (the pull down list is populated from the fixture codes filled
in the Assemblies form). When a selection is made in the Fixture code, the
subform is auto-filled with the values that corresponded with that fixture
code in the assemblies form. Since it is an append query, the information
can be changed if necessary without altering the original assembly.

Again, what I am ultimately trying to do is once this whole bid is done, I
would like to reload it and just change a few things here and there as
necessary for a new bid. However, I can't seem to figure out how to do it.
Any ideas?

Sorry this post was so long!

Thanks very much for the detailed reply. I actually thought (and I guess
should have) about mentioning the table structure. The problem with that was
that its actually quite complicated (I just kept experimenting until
something worked), and my Relationships screen is quite chaotic (so much so
I'm having a hard time figuring out just what I did do!)

That being said, I've been studying your response and I see where some of
your ideas do indeed correspond to what I've done. I've been reading up on
the rules regarding normalization as well, so I think I have a bit better
idea of what to do. I've actually taken your suggestion of drawing it out on
paper to heart and am going to start there. I've decided to literally start
the whole project over, borrowing code and objects from the existing to speed
things up (thank God for dual screens!). Fortunately the existing system
does work, its just that in time it will be unbelievably big. I may need to
explore the SQL route down the road and maybe use Access as a front end.

Once again, I appreciate your comments. I generally don't make posts
seeking help as I prefer to search out whats out there first and learn from
it. I rather enjoy building Access applications (I've done a few others).
Its a great sense of accomplishment when you can make these machines do
constructive things, isn't it?

"Ken Sheridan" wrote:

No need to apologize. Quite the contrary in fact; its lack of information
which is our more common problem. However, what you haven't told us is what
tables you have, only the forms, so we need to deduce from those what the
'logical model' is, i.e. the tables and the relationships between them. So
my assumptions below might not be correct.

It seems to me that your model for assemblies is fine. What you have, I
assume is an Assemblies table (your table names will probably differ of
course), a Components table and an AsemblyComponents table which models the
many-to-many relationship between them likes so:

Assemblies----AsemblyComponents----Components

where the and characters represent the 'many' side of each one-to-many
relationship type. Your subform will be based on the AsembyComponents table.

You don't say anything about how you are recording each bid, but I'd
envisage a Bids table, which will include columns such as BidID (its primary
key), CustomerID, BidDate etc.

The Locations table will include a foreign key BidID column referencing the
primary key of Bids. There is a many-to-many relationship type between
locations and assemblies so a table is needed to model this, having foreign
key columns referencing the primary key of each, and a quantity column. I'll
call this LocationFixtures.

The LocationFixtures table will be referenced by a LocationAssemblies table,
which in turn will reference a LocationAssemblyComponents table which will
then reference the Components table. It’s the LocationAssemblies and
LocationAssemblyComponents tables which I envisage you are filling with your
append queries. Lets just say at this stage that in principle this is not
introducing redundancy as each assembly can differ in its specification from
the original 'standard' specification (as contained in the AsemblyComponents
table). So the model for bids looks like this:

Bids---Locations---LocationFixtures---LocationAssemblies----LocationAsemblyComponents----Components

So, cutting to the chase, to use an existing bid as a 'template' for a new
bid is essentially the same process as using the original assemblies as the
basis for the location assemblies by means of your append queries. What you
need therefore is another set of append queries to insert new rows into
Locations, LocationFixtures, LocationAssemblies and
LocationAsemblyComponents, identical to those for the 'template' bid, but
with a different BidID and LocationID values in Locations, and a different
LocationID value in LocationFixtures reflecting the new bid. You can then
edit the new rows in the Locations, LocationFixtures, LocationAssemblies and
LocationAsemblyComponents tables as necessary, inserting or deleting rows,
changing the specifications of an assembly etc.

As I've described it above every bid would be a potential template, so you'd
have to select from all of them which is most appropriate as the 'template'
for a new bid. You could make matters easier by adding a Templates table
with columns descriptive of the 'template' so you can easily select one. As
you'd probably want to have a number of bids as possibilities associated with
each template, with the same bid possibly being an option for more than one
template, you'd have another table to model the many-to-many relationship
types in the usual way:

Templates---TemplateBids---Bids

Finally, all of the above is written off the top of my head of course,
without any opportunity to test the model. So don't accept it as gospel.
The first thing I'd do if developing a model like this would be to draw it
out diagrammatically on paper, with boxes for each table and directional
lines between them to represent the relationships - what's called an entity
relationships diagram. I'd then ask as many questions of the model as I can
think of on the basis of my experience with the real world objects and
processes which it represents, testing it as thoroughly as possible on paper
before implementing it. Then I'd thoroughly test the prototype application
before letting it loose into the wild.

Ken Sheridan
Stafford, England

"Dr Steevil" wrote:

Greetings,

This may seem painfully long, and I do apologize, but maybe more info from
the start will help in the end...

I have developed a database for my new plumbing business. Part of the
system is used for bidding projects. In each project there are a number of
fixtures (i.e sinks, toilets, etc) and each of these fixtures consists of
numerous parts. The way the db is set up, I build "assemblies" for each
specific fixture, then use append queries to load them into specific
locations. For instance, I create Lavatory #1 with faucet #1 and sink #1 and
Lavatory#2 with faucet #2 and sink #1. The master bathroom will have 2 of
the Lavatory #1's and the secondary bathrooms (2 of these) will each have 1
of Lavatory #2 (I hope that wasn't too confusing!)
This works all well and fine, but I know it violates the #1 rule of good
database design since I am duplicating data. As my database grows, this will
become an obvious problem.
But aside from all that, what I want to ultimately achieve is to save the
entire mess as a template which then can be loaded in a new bid and just make
a few alterations instead of having to do the whole thing over.
I am using Access 2007 now, but the existing system was built in 2003.
To help clarify things a bit, here's sort of an outline structure of how it
presently works.

-The Assemblies Form-
Parent form has fixture type and fixture code
Subform lists the individual components to make that assembly.

-The Locations Form-
Somewhat similar to the Assemblies Form, but different in the Parent form
aspect
Parent form has Location (where this fixture will be) and a pull down for
Fixture Code (the pull down list is populated from the fixture codes filled
in the Assemblies form). When a selection is made in the Fixture code, the
subform is auto-filled with the values that corresponded with that fixture
code in the assemblies form. Since it is an append query, the information
can be changed if necessary without altering the original assembly.

Again, what I am ultimately trying to do is once this whole bid is done, I
would like to reload it and just change a few things here and there as
necessary for a new bid. However, I can't seem to figure out how to do it.
Any ideas?

Sorry this post was so long!