Need help with Query calculation using lookup values

[Daniel Griswold]

I am designing a database to track and calculate rocket flights.

Flights table contains EngineType1, EngineType2, EngineType3, EngineQTY1,
EngineQTY2, EngineQTY3.

EngineType table contains EngineTypeNumber, EngineTypeImpulse.

The tables are linked.
Flights!EngineType1 to EngineType!EngineTypeNumber
Flights!EngineType2 to EngineType!EngineTypeNumber
Flights!EngineType3 to EngineType!EngineTypeNumber

In the query, I used:

=[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY1]+[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY2]+[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY3]

The query only uses EngineTypeImpulse from the EngineType1 for all QTYs.

What I need is (EngineTypeImpulse for EngineType1) * EngineQTY1 +
(EngineTypeImpulse for EngineType2) * EngineQTY2 + (EngineTypeImpulse for
EngineType3) * EngineQTY3

Any ideas on what the syntax of the query should be?

Thanks,

Daniel

(e-mail address removed)

 

[John Vinson]

I am designing a database to track and calculate rocket flights.

Flights table contains EngineType1, EngineType2, EngineType3, EngineQTY1,
EngineQTY2, EngineQTY3.

Then the table is incorrectly designed. "Fields are expensive, records
are cheap" - if you have three engines, you have a one (rocket) to
many (engine) relationship, and should have TWO TABLES in a one to
many relationship.

EngineType table contains EngineTypeNumber, EngineTypeImpulse.

The tables are linked.
Flights!EngineType1 to EngineType!EngineTypeNumber
Flights!EngineType2 to EngineType!EngineTypeNumber
Flights!EngineType3 to EngineType!EngineTypeNumber

In the query, I used:

=[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY1]+[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY2]+[EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY3]

The query only uses EngineTypeImpulse from the EngineType1 for all QTYs.

What I need is (EngineTypeImpulse for EngineType1) * EngineQTY1 +
(EngineTypeImpulse for EngineType2) * EngineQTY2 + (EngineTypeImpulse for
EngineType3) * EngineQTY3

Any ideas on what the syntax of the query should be?

With your current design you'll need to join the EngineType table to
the Flights table by adding it to the query grid THREE TIMES - once
linked to EngineType1, once to Enginetype2, and the third instance to
EngineType3. Access will alias the three instances by adding _1 to the
first "extra" copy and _2 to the second, so your query would be

=NZ([EngineType]![EngineTypeImpulse]*[Flights]![EngineQTY1]) +
NZ([EngineType_1]![EngineTypeImpulse]*[Flights]![EngineQTY2]) +
NZ([EngineType_2]![EngineTypeImpulse]*[Flights]![EngineQTY3])

assuming that some flights might have fewer than three engines.

Much better would be to restructure your table - unless you can be
CERTAIN that for the lifetime of the application every flight will
have exactly three, no fewer and no more, engines.

John W. Vinson[MVP]