UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Expanding A Range, Access 2010    
 
   
NimishParikh
post Nov 17 2019, 06:28 PM
Post#1



Posts: 235
Joined: 30-November 10



Friends,

I have a query that produces the following result –

CODE
ProjectID, EquipmentID, ComponetID, FromTo, ColumnID, Multiplier
“A”, “00-EQ-00”, 1, “TO”, 2, 1
“A”, “00-EQ-00”, 2, “FROM”, 2, 2
“A”, “00-EQ-00”, 2, “TO”, 3, 1
“A”, “00-EQ-00”, 2, “FROM”, 3, 1

“A”, “00-EQ-01”, 1, “TO”, 2, 1
“A”, “00-EQ-01”, 2, “FROM”, 2, 3
“A”, “00-EQ-01”, 2, “TO”, 3, 1
“A”, “00-EQ-01”, 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”, “00-EQ-00”, 1, “TO”, 2, 1
“A”, “00-EQ-00”, 2, “FROM”, 2, 1 <- Here I am expanding to two records
“A”, “00-EQ-00”, 2, “FROM”, 2, 2 <- Here I am expanding to two records
“A”, “00-EQ-00”, 2, “TO”, 3, 1
“A”, “00-EQ-00”, 2, “FROM”, 3, 1

“A”, “00-EQ-01”, 1, “TO”, 2, 1
“A”, “00-EQ-01”, 2, “FROM”, 2, 1 <- Here I am expanding to three records
“A”, “00-EQ-01”, 2, “FROM”, 2, 2 <- Here I am expanding to three records
“A”, “00-EQ-01”, 2, “FROM”, 2, 3 <- Here I am expanding to three records
“A”, “00-EQ-01”, 2, “TO”, 3, 1
“A”, “00-EQ-01”, 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

Go to the top of the page
 
NimishParikh
post Nov 17 2019, 06:33 PM
Post#2



Posts: 235
Joined: 30-November 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
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 03:32 PM