By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

problem with query SQL

100+
P: 135
Hello,

I have a table ENGINEERING with product informationm. The field COMMODITY represent different categories which are in french. In order to be able to create reports with the COMMODITY information in ENGLISH i created another table COMMODITY NAME with the following fields:
commodity (the french one)
commodityenglish

Both tables have a one-to-one relationship through the field "COMMODITY"

Im trying to modify the query code so that it will "look" for the "commodityenglish" but i havent been able to do it. I dont know how to indicate the relationship between the tables.

In a few words i need to replace the "engineering.commodity" with "commodity name.commodityenglish".

The current code, which looks for the commodities in french ("commodity" in table ENGINEERING):

Expand|Select|Wrap|Line Numbers
  1. SELECT Engineering.Commodity
  2. nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  3. nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY Engineering.Commodity
  8. ORDER BY Engineering.Commodity
  9. UNION SELECT "Total" AS Commodity, 
  10. nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  11. nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. WHERE Engineering.Variant1=-1;
Thanks,
Gilberto
Oct 12 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT Engineering.CommodityEnglish,
  2. nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  3. nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering INNER JOIN [COMMODITY NAME] ON (Engineering.Commodity = [COMMODITY NAME].Commodity )  
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY Engineering.CommodityEnglish
  8. ORDER BY Engineering.CommodityEnglish
  9. UNION SELECT "Total" AS CommodityEnglish,
  10. nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  11. nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. WHERE Engineering.Variant1=-1;
  15.  
Nic;o)
Oct 12 '07 #2

100+
P: 135
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT Engineering.CommodityEnglish,
  2. nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  3. nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering INNER JOIN [COMMODITY NAME] ON (Engineering.Commodity = [COMMODITY NAME].Commodity )  
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY Engineering.CommodityEnglish
  8. ORDER BY Engineering.CommodityEnglish
  9. UNION SELECT "Total" AS CommodityEnglish,
  10. nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  11. nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. WHERE Engineering.Variant1=-1;
  15.  
Nic;o)
Thanks for the reply Nico, i just had to make some changes and it worked perfectly.

Expand|Select|Wrap|Line Numbers
  1. SELECT [commodity name].CommodityEnglish,
  2. nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  3. nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering INNER JOIN [COMMODITY NAME] ON (Engineering.Commodity = [COMMODITY NAME].Commodity )  
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY [commodity name].CommodityEnglish
  8. ORDER BY [commodity name].CommodityEnglish
  9. UNION SELECT "Total" AS CommodityEnglish,
  10. nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
  11. nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. WHERE Engineering.Variant1=-1;
Oct 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.