DLookup, IIF, or different design for what I am trying to accomplish?

[sdmikejr]

Hello everyone,
I really need help solving a problem that is new to me (hopefully not to you all).

So I built a simple product planning tool in Excel and need to implement it into access. I have 2 tables, a product table and a lifecycle table.

The product table has the following:
Product Name
Product Life Exp. (this is years, in number format)
Annual Peak Volume

Example data:

ProductName Peak Annual Volume Product Life Exp.
Product 1 6000 3


The second table is lifecycle table:
LifeCycleID
Life (years)
Volume (decimal number)

LifeCycleID	LifeCycle(Years)	Peak	Multiplier
3-1	3	1	0.500
3-2	3	2	1.000
3-3	3	3	0.500
2-1	2	1	0.650
2-2	2	2	1.000
1-1	1	1	1.000

The objective here is to be able to run a query that shows years 1, 2 and 3 that takes the annual peak volume in the lifecycle table and multiplies it by the multiplier based on the year of the life cycle, as a result, Product 1 would give us 12K over 3 years based on the life cycle.

	Year1	Year2	Year3
Product 1	3,000	6,000	3,000

I added a lifecycle ID field to the second table as I was trying to figure a way to use relationships...but I am not sure this would work and if I would even want to attempt going down that rabbit hole.

Any Ideas here would greatly be appreciated.

[Micron]

I can make no sense out of the structure or target example you posted. What I think you should do is review normalization and naming conventions (3-1 is a poor identifier and you should not have parentheses in names) and then post back with a narrative on what the business entails if the suggestions don't help you with design. Often, starting out with what you have leads down some path that should be avoided because it's not properly designed. Not saying for sure that is the case here, but I suspect as much.
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp