I am working with a customer that has a automotive performance shop. 
They have a category structure that is based on the following levels:

Make
Model
Engine Type
Product Type

The way the database is structured is causing me problems in solving a
problem.  The "Product type" categories will be the same for all
"Engine Types" and will include: "EXTERIOR", "INTERIOR", "BRAKES",
"RIMS/TIRES", "ENGINE", "EXHAUST", "SUSPENSION"

The problem I am having is that I want to automatically import these
categories for each "Engine Type" in the database and create a
meaningful category "working name" so when they are entering products
and selecting the category from the drop down list it does not show 20
1.5L engines and no description of what "Model" it is assigned to.

I can provide the database with all of the categories entered in it to
you and also walk through this via a Placeware / WebEx online tool so
you can see what I am trying to accomplish.  Please let me know if you
would be interested in working on this.

---

Request for Question Clarification by mathtalk-ga on 28 Apr 2004 17:45 PDT

Hi, khiser0001-ga:

Your description makes it sound like a "Cartesian product", i.e.

given all possible Makes & Models

given all possible Engine Types

you can produce all the possible combinations (an unrestricted SQL join).

But of course there has to be a catch, right?  Not all engines are
available for all Makes/Models.  But, again, perhaps there are some
"simple" rules that will produce all the valid combinations plus or
minus a few exceptions (which could then be inserted or deleted by
hand)?

Perhaps you can demolish my cautious optimism, or elaborate on how you
see a SQL query "automating" the data entry task.

regards, mathtalk-ga

---

Clarification of Question by khiser0001-ga on 28 Apr 2004 19:14 PDT

Well when I say automate what I want to do is not have to enter all
these manually since it will be a one time task.

Here is the database that you can look at.  The question is regarding
the sections table.  In that table there is a field called
"sectionWorkingName".  This would need to be changed for each "Engine
Type" to include the "Make" - "Model" then the "Engine Type".  (ie. 
Honda - Civic - 1.6L)

Next when the new categories are created for each engine they would
need to have the "sectionWorkingName" set as "Model" - "Engine Type" -
Product Category" (ie. Civic - 1.6L - Suspension)

Does this make sense?

Here is the Access DB http://clients.wdcohio.com/rolen/images/vsproducts.mdb

Thanks,
Kevin

---

Clarification of Question by khiser0001-ga on 29 Apr 2004 05:29 PDT

It is a web based shopping cart application.  It does have a drop down
box for the data entry but there are other areas that would benefit
from this information being in the database.  The main reason I am
trying to stay away from modding the code in the actual asp page is
that the authors update or release new features at least once a month
and I do not want to have to re-mod the affected pages every time.

If we can not come up with something in the db I might have to go that
route though.

What I origirnally started playing with was making an asp page that
would pull the data from the database then create the information that
I described above and update the database with the new
sectionWorkingName etc.

I am open to any solution that one can come up with that works as I am
under a time crunch.

---

Request for Question Clarification by mathtalk-ga on 29 Apr 2004 10:19 PDT

Hi, khiser0001-ga:

Would it be accurate to count the total number of sectionWorkingName values as:

# of Make-Model values * # of Engine Type values * # of Product Categories

My concern is that we'd need to eliminate certain combinations of
Make-Model and Engine Type.  Given that restriction, though, I think
it's pretty straightforward to populate a table in Access with all the
combined strings for sectionWorkingName as you've indicated.

regards, mathtalk-ga

---

Clarification of Question by khiser0001-ga on 29 Apr 2004 11:52 PDT

There really would not be any duplicates that could be removed.  The
way the system is designed does not allow for a one to many
relationship such as 1 category called "Honda" and then many makes,
etc.

Typically when I do database design I have a category table and a
subcategory table so I can link everything together but this
application did not do it that way.

Were you able to get the database and look at the structure?  If you
need access to the actual application please let me know and I can
arrange for that.

Thanks again,
Kevin

---

Request for Question Clarification by mathtalk-ga on 29 Apr 2004 18:50 PDT

Hi, khiser0001-ga:

I did download the database and took a careful look at the sections table.

It seems to have 343 rows, some of which are Makes, some of which are
Models, and some of which are Engines (I did not see any Product Type
rows, but they may be mixed in someplace there or in another table).

Assuming you have some rules that could be used to distinguish Makes
vs. Models vs. Engines, I could write a SQL query that would produce
all possible combinations (and even throw in the various multiple
combinations when Product Type is added).

But are you wanting combinations like:

Ford Camry 2.2 Liter EXHAUST

Probably not.  I think there are some rules that are encoded in the
sections table for what Models go with what Makes, and possibly also
what Engines go with what Models.  The Product Types, well, I'm
willing to believe they are "orthogonal" in the sense that each of
them can go with any (valid) combination of the other categories.

So, are you asking me to try and puzzle out what the rules for valid
combinations are?  I haven't spent too much time looking at it, but
topSection and sectionOrder seem to be suspiciously relevant.  Let me
know if you've already got the rules sorted out though, so we don't
lose time unnecessarily.

regards, mathtalk-ga

---

Clarification of Question by khiser0001-ga on 29 Apr 2004 19:58 PDT

The "EXTERIOR", "INTERIOR", "BRAKES", "RIMS/TIRES", "ENGINE",
"EXHAUST", "SUSPENSION" need to be created for EACH "Engine Type"

They are not in the table or any other table.  Infact there is no
other table that impacts this.

The way that the database is laid out the topSection relates to the
sectionID.  For example if you had the following:

sectionID 1 
sectionName Honda
topSection 

sectionID 10
sectionName Civic
topSection 1

sectionID 100
sectionName 1.6L
sectionWorkingName Civic 1.6L  <-- Need to update the sectionworking
name of the "Engine Type"
topSection 10

Then we would need to create the product categories. ("EXTERIOR",
"INTERIOR", "BRAKES", "RIMS/TIRES", "ENGINE", "EXHAUST", "SUSPENSION")
 It might look like this:

sectionID 110
sectionName Exhaust
sectionWorkingName Civic 1.6L Exhaust
topSection 100

sectionID 111
sectionName Engine
sectionWorkingName Civic 1.6L Engine
topSection 100

sectionID 112
sectionName Brakes
sectionWorkingName Civic 1.6L Brakes
topSection 100

etc etc.

If you need to see it in action goto
http://clients.wdcohio.com/rolen/vsadmin/login.asp

User: Rolen
Pass: Data

Two areas to look at are:  

1 Categories <-- Go there and click "New Category" and notice what
appears in the dropdown list of "Home Categories".  You will see why I
need to change the sectionWorkingName to some thing that represents
the chain of categories.

2. Products <-- Go there and click "New Product" and notice the
"Section" drop down in the upper right hand corner.

I really appreciate you trying to help me with this and hope I am
explaining it somewhat decently.

Thanks,
Kevin

---

Request for Question Clarification by mathtalk-ga on 30 Apr 2004 07:24 PDT

Hi, Kevin:

Thanks for the prompt clarification.  So the query we need is actually
an "action" query as the Access terminology goes, to insert new
records into the sections table that provides the 8 new
"sectionWorkingName" labels per each Engine record.

I'm going to guess that many of the 343 records already in the table
are Engine records, say 200 of them (or more).  Thus we would be
inserting 1600 new records, using the fields that you've specified.

It can be done, though I expect it will take a bit of tweaking and
testing to do well.  In SQL terms we need a triple self-join on the
sections table (to pull the Make/Model/Engine rows together) and then
a final (Cartesian) product with the Product Type categories.  This
last could be done by just having 8 queries that differ only wrt the
Product Type category (one for Exhaust, etc.), or by creating a table
with eight records (one for each Product Type).

Let me play around with it some and get you something tonight, say ~8pm Eastern.

regards, mathtalk-ga

---

Clarification of Question by khiser0001-ga on 30 Apr 2004 08:28 PDT

Thanks!!!  Sounds like we are getting on the same page.  My problem is
that my SQL skills fall short when I start needing to do multiple self
joins and thus I am not in a good position to write this code.  I am
ok with SQL and write SQL for various applications so if I need to
tweak something already written it should not be a problem.

Thanks for your help on this!!!

---

Request for Question Clarification by mathtalk-ga on 30 Apr 2004 18:20 PDT

Hi, Kevin:

I noticed in the last go around you moved away from the idea of using
the Make name (e.g. Honda) in combination with the Model to elaborate
the Engine's sectionWorkingName's.

Did you decide to dispense with the Make name at both the Engine level
(update some existing records) and on the Engine by Product Type
(insert new records)?

regards, mathtalk

---

Request for Question Clarification by mathtalk-ga on 30 Apr 2004 19:07 PDT

Hi, Kevin:

I self-joined the sections table with itself in a series of three
Query's (which as a SQL guy, I tend to think of as Views):

vMake :  the subset of rows in sections where topSection = 0
(25 records)

vMakeModel : joining vMake with sections where vMake's sectionID
             equals sections' topSection
(112 records)

vMakeModelEngine : joining vMakeModel with sections where the
                   sectionID inherited by vMakeModel from sections
                   equals the topSection of this copy of sections
(205 records)

Curiously 25 + 112 + 205 = 342, which is one less than the 343 rows in
the existing sections table.  Thus one row somewhere in there has
become "unlinked" from the hierarchy.  (I'll find it later, just
pointing it out as an interesting result of going through the
self-join process.)

I think my strategy, since you have the autonumber field on sections,
would be to temporarily create a couple of auxiliary tables as
"working space".  One would simply hold the eight Product Type
records, the other would be like the sections table, except without
the autonumber field (sectionID).  We would do an action query to
insert the new records into this last table (without tacking on their
sectionID's).  Once the insert is done we can verify the quality of
the result, even playing around with the punctuation, format, etc. of
the sectionWorkingName's, until we think its perfect.  Then finally do
the insert from that table to the sections table, which gives the
final batch of sectionID's through the autonumber feature.

How about I go ahead and do these steps in the copy of the database I
downloaded, then put it out there for you to download from a different
location?

You can then see how I constructed the Query and Table objects, as the
descriptions given above are of course a bit terse.

regards, mathtalk-ga

---

Clarification of Question by khiser0001-ga on 30 Apr 2004 21:35 PDT

This sounds great!!!  Please go ahead and let me know where to download from.

Sorry I am getting back to you so late.  I just got in for the night.

Hi, Kevin:

You can download the modified database from here:

http://68.15.21.151/uploads/researchers/vsproducts.mdb


I created three Query objects (or Views) that embody the SQL
self-joins on the sections table, as discussed earlier.

Also I added two new tables, productTypes (which contains seven rows
for the names of the Product Type categories) and sectionsTemp (which
is laid out like sections, except for omitting the first column
sectionID).

From there the plan is to construct an "Action" Query of the type that
inserts records into an existing table, so we can build all but the
"autonumber" sectionID for the additional records you will ultimately
require in the sections table.

The SQL for the resulting "Append Query" winds up looking like this:

[Append2sectionsTemp]

INSERT INTO sectionsTemp ( sectionName,
                           topSection,
                           sectionWorkingName )
SELECT productTypes.productName,
       vMakeModelEngine.sectionID,
       vMakeModelEngine.vMakeModel.sections.sectionName
       + " - "
       + vMakeModelEngine.sections.sectionName
       + " - "
       + productName AS Expr1
FROM productTypes, vMakeModelEngine;

This does not address the content for fields sectionImage (which seems
always to be blank), sectionDescription (likewise), sectionOrder
(which appears to be pretty close to an enumeration of the rows in
sections, though I don't know what "order" is implied), and
rootSection (which seems always to be zero).

For a test I ran the Append Query (Append2sectionsTemp) and verified
that it inserted:

205 * 7 = 1435 rows

as expected.  I then deleted those rows to leave you a "clean slate".

Here are the SQL expressions for the Query objects used for the self-joins:

[vMake]

SELECT sections.*
FROM sections
WHERE (((sections.topSection)=0));

This was basically just a filter to get the "root" rows, the Make's.

[vMakeModel]

SELECT vMake.sectionName,
       sections.sectionName,
       sections.sectionID
FROM vMake INNER JOIN sections
ON vMake.sectionID=sections.topSection;

This joins the "subset" vMake to the corresponding rows in sections
that connect back via the relationship sections.topSection =
vMake.sectionID.

[vMakeModelEngine]

SELECT vMakeModel.vMake.sectionName,
       vMakeModel.sections.sectionName,
       sections.sectionName,
       sections.sectionID
FROM sections INNER JOIN vMakeModel
ON sections.topSection=vMakeModel.sectionID;

This is the same sort of idea carried to the next level, where the
"Engine" rows of sections link back to the corresponding rows in
vMakeModel.

Except for the simple case of the first Query, I made an effort to
retain only the useful information, i.e. the sectionName's and the
relevant sectionID.  I even dragged along the sectionName for the
Make, in case you really do want to combine it into the
sectionWorkingName's for the final rows.  The SQL shown as
Append2sectionsTemp only picks up the Model and Engine combinations
(along with the Product Types) as currently written.

regards, mathtalk-ga

---

Request for Answer Clarification by khiser0001-ga on 02 May 2004 12:06 PDT

I am testing this as I type and it looks good so far.  I will get back
to you in a bit.

Since this is the first time I have used Google Answers could you
explain what I need to do next to make sure you get paid?

Thanks,
Kevin

---

Clarification of Answer by mathtalk-ga on 02 May 2004 12:46 PDT

Hi, Kevin:

This may be the easiest Clarification I've ever tackled!

Basically the price you offered was added to your charges when I
posted the Answer.  Up until the time an Answer is posted, you can
always Close (cancel, expire) the Question without charge (except the
$0.50 posting fee).

After the Answer is posted you may Request Clarification as much as
necessary, with no additional charge.  If the initial Answer is not
satisfactory and a round or two of clarifications don't improve it,
then you can request a Refund within the 30 days following the Answer
being posted.

So take a look at the database changes, and let me know if further
explanations would be helpful!

regards, mathtalk-ga

---

Request for Answer Clarification by khiser0001-ga on 03 May 2004 20:08 PDT

I have checked everything and it looks good.  (I modified the code a bit)

I just noticed that there was one negative that I did not anticipate. 
That is that the sectionOrder field ended up left with a value of "0".

Is there an easy way to go through and do a update query such as:

Update sections SET sections.sectionOrder = (previous record) + 1
WHERE sections.sectionOrder = 0

Let me know if you do not mind.

Thanks again as you saved me many hours of headaches

---

Clarification of Answer by mathtalk-ga on 04 May 2004 05:20 PDT

Hi, Kevin:

I don't mind at all.  My first thought is to fix up the sectionOrder
by making it an Autonumber column in sectionsTemp.  I think we could
stick in a dummy record with the highest sectionOrder value from the
existing sections table, then run the Insert query as it exists now.

I'll test this and put out a replacement database for you tonight.

regards, mathtalk-ga

---

Request for Answer Clarification by khiser0001-ga on 04 May 2004 06:37 PDT

Thanks.  I never thought about doing that but it makes sense.  I can
probably play with that tonight as well.

I must tell you that you have been a great help!!

---

Clarification of Answer by mathtalk-ga on 04 May 2004 18:43 PDT

Hi, Kevin:

I thought of a slight simplification of the Autonumber tactic and
tested it satisfactorily.

Before inserting the 1435 new records into sectionsTemp, I went into
Design View and changed the sectionOrder column there from Number to
AutoNumber.  With the table still empty, I ran the Append Query
discussed previously, with the result that values in sectionOrder now
ran from 1 to 1435.

Then I went back to Design View on sectionsTemp, and changed the
AutoNumber property of the sectionOrder column back to Number.

Next I went to the sections table, used the sort Z->A to verify the
largest value in sectionOrder is 347 (your mileage may vary!), then
holding that figure in short term memory, I went back to the Query
View and designed a new Update Query.  Simply put, it updates the
sectionOrder column of sectionsTemp by adding 347.  The SQL for this
looks like:

[adjust_sectionOrder SQL]

UPDATE sectionsTemp
SET    sectionsTemp.sectionOrder = sectionsTemp.sectionOrder+347;

Run it once, and you will have shuffled all the entries in
sectionsTemp just high enough up to insert into sections without
conflicts in that column.

Since you've already tweaked the code in your database, it may not be
as helpful to have my copy as merely adapting the recipe outlined
would be, but I'll have it in the same location in case you'd like to
check it out.

regards, mathtalk-ga

---

Request for Answer Clarification by khiser0001-ga on 04 May 2004 20:09 PDT

Thanks.  It appears to have worked well!!

Kevin

---

Clarification of Answer by mathtalk-ga on 05 May 2004 07:25 PDT

Excellent, Kevin.  It's nice when someone can make practical use of ideas!

best wishes, mathtalk-ga

I'm curious what kind of application will be used to input the data.
Perhaps I can offer an alternative solution.  If you're trying to
create a meaningful dropdown box in a web application, for example,
you can combine fields into one longer name using php, asp, jsp, cold
fusion, etc. You'd set up the select box to use the key from your
table as its ID, but use a CONCAT function in SQL to produce the
meaningful name to be displayed along with it. I'm sure you can do
something similar in MS Access and elsewhere.