Create a query for comparison

  • Thread starter Thread starter Nadeem
  • Start date Start date
N

Nadeem

Hi,

I have my databse region wise and I have type of cost and cost for eacg region

For example
AFRICA HM 54
EUROPE HM 75
EUROPE HL 100
AMERICA HM 120

How can I create a query where I can select two regions eg, africa & europe
and select cost type HM to compare HM cost between Europe & africa
 
Hi,

I have my databse region wise and I have type of cost and cost for eacg region

For example
AFRICA HM 54
EUROPE HM 75
EUROPE HL 100
AMERICA HM 120

How can I create a query where I can select two regions eg, africa & europe
and select cost type HM to compare HM cost between Europe & africa
Click to expand...

Create a Self Join query by adding this table to the query grid *twice*.
Access will give the second instance of the table an alias by appending _1 to
the name - e.g. MyTable and MyTable_1.

Join the two instances by Region, and include both cost fields.

Put a criterion of "Africa" under one instance of the country name, and
"Europe" under the other; or of course use a parameter query, e.g.

=[Forms]![SomeFormName]![FirstRegion]

under one and

=[Forms]![SomeFormName]![SecondRegion]

under the other, referenceing two combo boxes on a form.
 
UNTESTED UNTESTED
Substitute your table and field names --
SELECT [RegionCost].[Type], [RegionCost].[Region], [RegionCost].[Cost],
[RegionCost_1].[Region], [RegionCost_1].[Cost]
FROM [RegionCost] INNER JION [RegionCost_1] ON [RegionCost].[Type]
=[RegionCost_1].[Type]
WHERE [RegionCost].[Type] = [Enter type] AND [RegionCost].[Region] = [Enter
region 1] AND [RegionCost_1].[Region] = [Enter region 2];
 
Back
Top