My Assistant
Custom Search

Expanding A Range, Access 2010 
Nov 17 2019, 06:28 PM Post#1  
Posts: 235 Joined: 30November 10  Friends, I have a query that produces the following result – CODE ProjectID, EquipmentID, ComponetID, FromTo, ColumnID, Multiplier “A”, “00EQ00”, 1, “TO”, 2, 1 “A”, “00EQ00”, 2, “FROM”, 2, 2 “A”, “00EQ00”, 2, “TO”, 3, 1 “A”, “00EQ00”, 2, “FROM”, 3, 1 “A”, “00EQ01”, 1, “TO”, 2, 1 “A”, “00EQ01”, 2, “FROM”, 2, 3 “A”, “00EQ01”, 2, “TO”, 3, 1 “A”, “00EQ01”, 2, “FROM”, 3, 1 And this query delivers results to my expectations. What I like is to further expand this query so that all entries with Multiplier field greater than 1 expands to 1, 2, 3 etc. CODE ProjectID, EquipmentID, ComponetID, FromTo, ColumnID, Multiplier “A”, “00EQ00”, 1, “TO”, 2, 1 “A”, “00EQ00”, 2, “FROM”, 2, 1 < Here I am expanding to two records “A”, “00EQ00”, 2, “FROM”, 2, 2 < Here I am expanding to two records “A”, “00EQ00”, 2, “TO”, 3, 1 “A”, “00EQ00”, 2, “FROM”, 3, 1 “A”, “00EQ01”, 1, “TO”, 2, 1 “A”, “00EQ01”, 2, “FROM”, 2, 1 < Here I am expanding to three records “A”, “00EQ01”, 2, “FROM”, 2, 2 < Here I am expanding to three records “A”, “00EQ01”, 2, “FROM”, 2, 3 < Here I am expanding to three records “A”, “00EQ01”, 2, “TO”, 3, 1 “A”, “00EQ01”, 2, “FROM”, 3, 1 To do so, I created a superficial table tblMultiplier as follows, based on my earlier post https://www.UtterAccess.com/forum/index.php...2023178&hl= CODE tblMultiplier Multiplier 1 2 3 4 5 6 Then I created the following query – CODE SELECT T1.ProjectID, T1.EquipmentID, T1.ComponentID, T1.FromTo, T1.ColumnID, T2.Multiplier FROM tblTest AS T1 INNER JOIN tblMULTIPLIER as T2 ON T1.Multiplier = T2.Multiplier WHERE T2.Multiplier >=1 and T2.Multiplier <= T1.Multiplier; PS: I have created a temporary table tblTest from the query for the testing purpose. With this query, I am getting result same as tblTest. Not sure what am I missing. Can anyone help me with filling in the gap. Thanks, Nimish 
Nov 17 2019, 06:33 PM Post#2  
Posts: 235 Joined: 30November 10  Never mind. I rewrote query as follows  CODE SELECT T1.ProjectID, T1.EquipmentID, T1.ComponentID, T1.FromTo, T1.ColumnNo, T2.Multiplier FROM tblTest AS T1, tblMULTIPLIER AS T2 WHERE (((T2.Multiplier)>=1 And (T2.Multiplier)<=[T1].[Multiplier])); And it works perfectly well. Thanks, Nimish 
Custom Search

Search Top LoFi  15th December 2019  10:40 AM 