Before I invest the time to learn, I was wondering if this is poss

[Bob Quintal]

=?Utf-8?B?SmltYm8xMw==?= <[email protected]>
wrote in

Could you provide me with direction on where I can find solid
tutorials that might help me start to walk through this
process?

www.google.com

good search keys are:
Access Tutorial,
Relational database design

 

[Guest]

I think Access can do this, but I would like confirmation before investing
the time.

I will have three seperate tables.

Table 1) My products and their list price.
Table 2) My products and reasonable Competitor Substitute
Table 3) Competitor product and list price.

I would like to build a report that pulls information from each of the three
tables and provides me with the desired comparisons.

The reason for having three tables is that this infromation changes
regularly and the amount of information in a table may be quite large.
Furthermore, I feel (right or wrong) that it gives me more control over the
data I am importing in.

 

[Guest]

I think Access can do this, but I would like confirmation before investing
the time.

Oh this sort of thing is just was Access was written to do. It can also
handle one-to-many's, meaning you can have a number of "competitor products"
for every one of yours. This is very very simple stuff in Access.

Maury

 

[Jeff Boyce]

If I'm inferring the actual contents of your tables correctly, yes, the
tables seem correct, but no, not exactly for the reasons you gave <G>!

To get the best use of Access' features and functions, your data needs to be
well-normalized. To normalize your (overall) data, you'd look at what
"things" you want to keep information about (termed "entities"), and how
these are related to each other (termed "relationships").

Are the following (relationships) true for your data?:
* each of your products is listed once and only once
* each of your competitors' products is listed once and only once
* your list of "reasonable...Substitutes" contains ONLY the MyProductID
and CompetitorProductID values for valid substitutions

This would mean that one of your products may have multiple competitor
substitution products, and one of your competitor's product may have
multiple substitute products from your list.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Could you provide me with direction on where I can find solid tutorials that
might help me start to walk through this process?

 

[Guest]

Could you provide me with direction on where I can find solid tutorials that
might help me start to walk through this process?

For basic Access stuff, like setting up tables and making queries, you'll
find about 1 buzzillion examples on Google.

But the trick here is the data, which is a little more tricky. My suggestion
in this case is to use only two tables;

1) your product and it's price
2) a list of competitor products and their prices

In both of these tables make a non-data Number field called "ID" or
something similar, and tell Access to make it auto-increment -- the tutorials
will have all sorts of examples of how to do this.

Now in table (2), add another column called "ourProductId", also a Number.
Whenever you add a row to this table, with a competitor's product in it, fill
in this field with the ID number of your product that it matches.

That's about it. Now you can make a Query that will link the two, and put
that into a Report that displays them nicely. Those steps too will be well
covered in the various tutorials you can find.

If you wish to stick with three tables, that's easy too. Make a new table
that has only two columns in it, "ourProductId" and "theirProductId". Like
the example above, it just links the data by ID. The only reason I don't
recommend this is that it's harder to edit: every time you add a new
competitor product you'll have to go to this table and type in the two ID's.
With two tables you type in only one ID, and while you're editing the record
anyway.

Maury

 

[James A. Fortune]

I think Access can do this, but I would like confirmation before investing
the time.

I will have three seperate tables.

Table 1) My products and their list price.
Table 2) My products and reasonable Competitor Substitute
Table 3) Competitor product and list price.

I would like to build a report that pulls information from each of the three
tables and provides me with the desired comparisons.

The reason for having three tables is that this infromation changes
regularly and the amount of information in a table may be quite large.
Furthermore, I feel (right or wrong) that it gives me more control over the
data I am importing in.

You've got a great idea. It reminds me of the cross elasticity
calculations from one of my Microeconomics classes in college. Even
without the potential for lots of data, Access alone would not be a good
match because of the time-varying nature of the price data (a.k.a.,
temporal data). I think that the best long-term solution to this
problem involves the SQL-3 standards. I feel confident that the SQL-3
standards will be implemented in SQL Server before they are implemented
in any version of Access. So I recommend an Access front end and some
kind of SQL Server back end. Except for very simple calculations,
having temporal data without having the SQL-3 functionality can easily
lead to SQL queries that are a nightmare to maintain. Perhaps you don't
really need the price histories for your calculations. In that case,
Access is suited ideally for your purposes.

James A. Fortune
(e-mail address removed)