Similar Product Tables

[cmur8594]

Hi Everyone,

I am trying to make a database which allows you to see the differences between products. So I want to have a list of products and be able to select a product from a list and then select a similar product and then list the differences between them. At the moment I am using 2 tables, one for product1 and a second for product2. I am also using a similar products table to link them. The problem is that i cannot find out how to mirror products from table 1 to table 2. this would save me having to type the prodycts twice. I have a feeling this isn't even the correct way to approach this but i cant think of any other ways.


So basically what i want is the following:

- select product1
- select a similar product - product2
- insert the differences between the 2 products

this has to work both ways, so when you select product 1, it lists product2 as a similar product and when you select product 2 it lists product 1 as a similar product. This is the part that im having problems with.

Any help would be greatly appreciated.

Thanks

 

[DCrake]

Forgive my pessimism, but if you have a master products table that can have many products that vary between thingymibobs to widgets all of which have their own characteristrics and supilites. Recording the differences between similar products could only be recorded as text as an example.

Stock item 1

Football boots - Cheap
Football boots - expensive

Difference = Cheap = made of plastic; Expensive made of leather

Stock item 2

Snooker Cue - Cheap
Snooker Cue - Expensive

Difference = Cheap = Machine made; Expensive man made

The differencees cannot be categorised easy

 

[cmur8594]

I work for a presales department and some of the products we sell such as routers and switches are very similar. For example, 2 routers may be almost identical, except one has half the memory of the other, or one supports vpn termination where as the other only supports vpn passthrough.

I am trying to design the database for new hires so that when a customer comes in and would like to know the difference between two very similar products, the staff member won't have to go searching through all the documents and compare both to find the main differences.

 

[The_Doc_Man]

Believe it or not, a similar issue occurs with True Type fonts - and that one hasn't been solved either. Attributes of things that are similar but not identical can get applied when you attempt to view a page that uses an oddball specialty font and fails to allow you to download or temporarily download that font. Which is why we have such fonts as Arial Turkish and Arial Cyrillic and Arial Baltic and .... you get the idea.

Your say your products table has a list of similar items and you want Access to show you differences among the similarities. But here is a principle you need to know. Access won't tell you anything you didn't tell it first. AND it can't tell you anything until you tell it how to find out what you wanted. I.e. your issue isn't data, it design and setup.

Either you will have a nightmare of data entry for the details of the various products OR you will have to try to somehow "standardize" the kinds of things that can be different about what appears to be possibly a widely disparate range of products. Then try to map reality to those standards.

I'll be honest - I don't see an answer to this in ANY language that isn't so labor intensive that you will have an issue in "staying the course." I.e. as more and more products get added, your "similarities" and "differences" lists will be larger, more populous, and harder to track. Your boss will eventually wonder why you bothered if it was going to take this much overhead.

Another adage comes to mind... you cannot program anything in Access that you couldn't perform on paper. (Meaning: If you haven't got the algorithm figured out on paper first, taking it to Access is totally premature.)

I suspect that though you were hoping Access could help you with your problem, you really don't fully appreciate that problem's programming nuances. If that is so, you aren't ready by a long shot to tackle this level of problem. Hell, I'm a 35-year veteran of programming and a 25-year veteran of database design. I sure wouldn't tackle this one without a LOT of time spent in deciding just what constitutes a difference.

OK, you ask, what do you mean when you ask "what constitutes a difference? Isn't that clear?" To which I reply, "No." I remember an old Star Trek novel in which Spock comes up with one of his philosophical gems: "A distinction without a difference IS no difference." Stated another way, what features can differ from one another such that their differences are totally immaterial for all practical purposes?

So item A is painted extra dark blue and item B is painted midnight blue. Does it make a difference after 20 washings? I guess I'm belaboring the point because what I see from your question is disorganized thinking about the problem. I.e. you have more homework to do on your end first.

Which doesn't mean one of my fellow veterans here couldn't see the problem in a totally different light and hand you a solution on a silver platter.

 

[Balr14]

I'm new here and don't know crap, but I assume each of your products are comprised of multiple rows in a table; otherwise, it would be a simple comparison of the columns between two rows, using VBA. So, if that's the case, I'd do a make table query to select all product 1 rows; a make table query to select all product 2 rows and a delete distinctrow query to delete all matching rows from your second table, which should yield the differences.

 

[Mike375]

Another adage comes to mind... you cannot program anything in Access that you couldn't perform on paper. (Meaning: If you haven't got the algorithm figured out on paper first, taking it to Access is totally premature.)

I agree with The_Doc_Man.

I do quite a bit of this with insurance products and one thing I do (as others also do) is to give a number rating.

So using David's example

Football boots - Cheap..........10
Football boots - expensive.......0

Now we could also have

Football boots - Lowest price..........10
Football boots - Very Upmarket.........0

Also to be considered is how the rating is to be done and who does it. For example something like insurance gets into rating A Vs B on a policy wording interpretation so it needs someone who knows a lot about the insurance. But for other products it might be just brocure information based.

Another approach is to us one product as the benchmark and all the others are rated against that product.

As to tables, you might consider having a table where the fields are the rating category and one field is the name of the product. Thus your table would look very similar to a table that held the various details of people such as clients or prospects.

You can also go the other way (we sometimes do this with insurance) and the product or company name become the field names and the rating category becomes like LastName in a personal record table.

This depends on how you want to display the information. For example, if the product or company name is used for the field names then you would pull up 1 record and this would be on the bais of "feature" and you would then see that feature for all products or companies. If the features are the fields then pulling up two records shows all the features but for only 2 products, in other words, an opposite situation.