Hi. I am working in Access 2000 in Windows XP.
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:
-
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];
-
As you can see from the code, it takes its values from Calculated Values by Product Group. Here's its code:
-
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];
-
Whenever I try to open up my Savings Calculations query, it prompts me twice for "Calculated Values by Product Group!FPE" and then shows all fields correctly except those requiring this value are empty - no error even. The strange thing is that the FPE field in Calculated Values by Product Groups is perfectly fine, and that there are other fields in Savings Calculations that are calculated with fields from the same place that calculate with no problem.
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.
