match data from one field to another table field name

[Guest]

I have two tables

Tbl 1-
Cust num
Phone
City
Prc_tier

Tbl 2-
Tier 1
Tier 2
Tier 3

In Tbl 1 the field Prc_tier has either 1 2 or 3 (this of course tells the
tier pricing they get). Tbl 2 has the prices for each tier.

I need a query that will take all the fields from Tbl 1 (that’s the easy
part) but show ONLY the price of what Tier they are getting. So the number
IN the field of Prc_tier should decide which field to show in the query (Tier
1 2 or 3).

I’m not a programming wizard so please go easy.

Thanks VERY much in advance

 

[Vincent Johns]

I have two tables

Tbl 1-
Cust num
Phone
City
Prc_tier

Tbl 2-
Tier 1
Tier 2
Tier 3

Can I correctly guess that [Tbl 2] contains other fields not shown, such
as the product number to which the price applies?

I assumed your Tables contained at least the following information (and,
incidentally, I think the Table names could be more suggestive of what
they contain):

[Tbl 1]

Name Prc_tier
----- --------
Joe 2
Mary 3

[Tbl 2]

Tbl 2_ID Tier 1 Tier 2 Tier 3
-------- ------ ------ ------
1 $6.00 $7.00 $8.00
2 $10.00 $12.00 $14.00

In Tbl 1 the field Prc_tier has either 1 2 or 3 (this of course tells the
tier pricing they get). Tbl 2 has the prices for each tier.

I need a query that will take all the fields from Tbl 1 (that’s the easy
part) but show ONLY the price of what Tier they are getting. So the number
IN the field of Prc_tier should decide which field to show in the query (Tier
1 2 or 3).

I’m not a programming wizard so please go easy.

Thanks VERY much in advance

The Query that I defined to list these follows. This Query lists all
the possibilities (all names, all products), but you'd presumably want
to select just "Joe" and product "2", or something like that:

SELECT [Tbl 1].Name, [Tbl 2].[Tbl 2_ID],
Format(Choose([Tbl 1]![Prc_tier],[Tbl 2]![Tier 1],
[Tbl 2]![Tier 2],[Tbl 2]![Tier 3]),"$0.00") AS Price
FROM [Tbl 1], [Tbl 2]
ORDER BY [Tbl 1].Name, [Tbl 2].[Tbl 2_ID];

Name Tbl 2_ID Price
----- -------- ------
Joe 1 $7.00
Joe 2 $12.00
Mary 1 $8.00
Mary 2 $14.00


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Change Tbl 2 to be like this --
Tier - number - interger
Price - number - single

The date would look something like this --
Tier Price
1 1.43
2 1.57
3 1.86

In you query join Tbl 1 and Tbl 2 on the Tier number.

 

[Vincent Johns]

Change Tbl 2 to be like this --
Tier - number - interger
Price - number - single

The date would look something like this --
Tier Price
1 1.43
2 1.57
3 1.86

In you query join Tbl 1 and Tbl 2 on the Tier number.

In an earlier message, I suggested one way to set up your Query and
Tables, but I'd intended to also suggest something like Karl Dewey's
design (above). Either one will work, but this one is cleaner and makes
it easier to add a 4th "Tier" value, should you ever choose to do that.
It would involve merely adding some new records to [Tbl 2] instead of
changing the structure of the Table by adding a field. This design also
makes it easier to write Queries that give you the information you want
to see.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.