The problem I am having is very strange. My database has many tables and queries, but the relevant ones here are Pricing and Targets (table), Calculated Values by Product Group (query), and Savings Calculations (query). The Savings Calculations query is really the one causing trouble. I will include its SQL code here:
Expand|Select|Wrap|Line Numbers
- SELECT [Pricing and Targets].Plant, [Product Group Totals].Line, [Pricing and Targets].[Product Group], [Product Group Totals].[SumOfAdj Good Kgs], (([SumOfAdj Good Kgs]/[Pricing and Targets]![2006 FPE])-([SumOfAdj Good Kgs]/[Calculated Values by Product Group]![FPE]))*([Pricing and Targets]![Cost $/lb]*2.2046+[Pricing and Targets]![Conversion Cost ($/lb)]*2.2046) AS [FPE Savings], (([Product Group Totals]![SumOfAdj Good Kgs]/[Pricing and Targets]![2006 Material U])-([Product Group Totals]![SumOfAdj Good Kgs]/[Pricing and Targets]![2007 Target Material U]))*[Pricing and Targets]![Cost $/lb]*2.2046 AS [Material U Savings], [FPE Savings]+[Material U Savings] AS [Total Savings]
- FROM [Product Group Totals] LEFT JOIN [Pricing and Targets] ON [Product Group Totals].[Product Group] = [Pricing and Targets].[Product Group];
Expand|Select|Wrap|Line Numbers
- SELECT [Product Group Totals].Plant, [Product Group Totals].Line, [Product Group Totals].[Product Group], [Product Group Totals].[SumOfAdj Good Kgs], [Product Group Totals].[SumOfAdj Scrap Kgs], [Product Group Totals].[SumOfReclaim Kgs], [Product Group Totals].[SumOfRerun Kgs], [Product Group Totals]![SumOfAdj Good Kgs]/([Product Group Totals]![SumOfAdj Good Kgs]+[Product Group Totals]![SumOfAdj Scrap Kgs]) AS FPE, [SumOfAdj Good Kgs]/([SumOfAdj Good Kgs]+[SumOfAdj Scrap Kgs]-[SumOfReclaim Kgs]-[SumOfRerun Kgs]) AS [Material U]
- FROM [Product Group Totals];
I don't understand why it would do this. This is a query on a query on a query - maybe I have tried to stack too many things on top of each other. However, I never got the impression from any source that this sort of approach would be a problem. Or maybe my table names are too long? I know they're kind of wordy, but that's needed to tell them apart because I have so many that are so similar.
I will greatly appreciate any ideas on what is causing this silliness. I am all out.