vlookup in Access

[Susan Yager]

Hi, I am new to using access and have a question about how to
manipulate data. I am quite proficient in Excel and can easily do
what I need using vlookups and pivot tables, however I am having a
hard time in Access. Basicly, I am trying to store calculations in my
database. For example, I have 3 tables I'm working with:

1. Full inventory list
2. Orders list
3. Commission calculation

I have been able to join table 1 and 2, but in order to calculate the
commission charge I need to be able to join with #3. I also need to
have the appropriate commission "looked up" in number 3 based on
various criteria -- the type of item, who sold it, the category,
etc... I am stumped on how to do the lookup.

I also need to be able to group the inventory into buckets based on
the date we received it and the source, which I am having trouble with
as well.

Any help would be appreciated.

 

[BruceM]

Some description of your tables and how they are related is needed for a
start. It is not readily apparent how Full Inverntory List and Orders List
are related, nor how the joined tables refer to a specific Commission
Calculation record.

It is important to note that while an Access table resembles a spreadsheet,
it is very different. A table is just a place where data are stored.
Calculations are performed in queries, as the control source of controls on
forms or reports, or using VBA code, but not in tables. Also, it is hardly
ever necessary to store a calcualtion result. Instead, they are performed
as needed on the fly.

This link may help you gain a better understanding of how the different
parts of an Access database work together:
http://allenbrowne.com/casu-22.html

 

[Susan Yager]

Hello, Bruce. Thanks for your reply. The full inventory tables lists
all of the items that are currently in our inventory. The orders
table lists all of the items have been ordered. If you add the two
together it would give you totals for all items we've ever had in
inventory. Each inventory item has a source, and that source code is
a combination of several variables within the tables. The fundamental
question I want to answer is how profitable is each source. In order
to calculate that I need to calculate the commissions. But I also
want to be able to see individual items as well. Is this possible to
do in access or should I be using something different? I can do all
of this in Excel but it takes a lot of work which I would like to
automate within the queries and also I am quickly starting to hit the
maximum number of rows in excel.

 

[Clif McIrvin]

Hi Susan.

It sounds like you really need to become acquainted with the elementary
principles of relational database design. Along with the link that Bruce
provided, here are several more. I'd recommend at least skimming
through Crystal's tutorial; then come back here with questions and
you'll find there are experienced developers here who are glad to help
beginners get started 'on the right foot', so to speak.

I'd have saved myself several headaches had I started here instead of
simply diving right in!
--
(Thanks to John W. Vinson [MVP] for this info

"Access has a steeper learning curve than (say) Word or
Excel; not least, in order to make productive use of the program you
have to
understand the theoretical basis of database design - a concept called
'Normalization'. It is very logical and not at all difficult once you
get the
concepts down."

Here are some tutorials and other resources that you should find
helpful:

A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

HTH!

 

[BruceM]

Since an order presumably can consist of many items you will probably need
an OrderDetails table that is related to the Orders table. Further, since
one item can be included on many orders, and each order may consist of many
items ("many" is "more than one" in this context), there is a many-to-many
relationship between Orders and Items, which means the OrderDetails table
table will function as a junction table to resolve the relationship. That
is to say, it is the child table to both the Order table and the Items
table.
It sounds as if you want to add the existing inventory listed in tblItems to
the previously sold items in OrderDetails. That means you need a way to
subtract from the quantity in the Items table after assembling an order,
unless you intend to update the table manually after each order. Also, do
you intend to keep track of deliveries to replenish your inventory, or will
you just revise the quantity when you receive a new delivery?
How does the commission relate to the Items? Is there a fixed commission on
each item sold, or does it vary from order to order, or what exactly?
These are the sorts of questions you need to ask yourself. Remember, you
know your business model and rules; we do not.
As a short answer to your question about the suitability of Access, I expect
it is a good tool for the job, but your Excel skills will not translate into
Access skills, for the most part. As has been mentioned elsewhere, there is
a steep learning curve. There is no substitute for learning the principles
of database design, including especially normalization.
If you post the specifics of your table structure it may be possible to
suggest something specific, but talking you through development of an
application is beyond what most people, including me, are willing to do in a
newsgroup. There are many resources available online, probably enough for
you to make good progress toward understanding how to proceed, but it is up
to you to get a good handle on the fundamentals. After that it may be a
good idea to start with one aspect of the project, such as managing an Items
table, an Orders table, and an OrderDetails junction table, along with the
forms and reports that constitute the user interface.