Add a row based on a field value (1 Viewer)

Jayce72

Registered User.
Local time
Today, 11:47
Joined
Sep 26, 2011
Messages
60
I have a table with data


Col:1 ID

Col.1 1, 2, 3 etc


Col2 Fruit
Col2 Pear, Apple, Orange


Col3 CountofFruit
Col3 2, 4, 7


This would bring back 3 rows with the data above.


I want to increase the row count based on the countofFruit


Therefore, it'll have 2 lines for ID1, 4 for ID2 and 7 for ID3


How can I do this in an access query
 

June7

AWF VIP
Local time
Today, 03:47
Joined
Mar 9, 2014
Messages
5,423
One way requires a table of records with a 'counter' field - a field that has values 1 through 10 (10 records). Or use the hidden MySysObjects table which is certain to have enough records. In either case, the query involves a Cartesian association of records:

SELECT Table.*
FROM Table, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
WHERE F.Factor<CountOfFruit
ORDER BY ID;
 
Last edited:

Jayce72

Registered User.
Local time
Today, 11:47
Joined
Sep 26, 2011
Messages
60
One way requires a table of records with a 'counter' field - a field that has values 1 through 10 (10 records). Or use the hidden MySysObjects table which is certain to have enough records. In either case, the query involves a Cartesian association of records:

SELECT Table.*
FROM Table, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
WHERE F.Factor<CountOfFruit
ORDER BY ID;


That works perfect, thank you - BUT I have several entries that are over 100 - and it looks like your query only goes to 100. Can it go higher - then you've cracked it :)
 

Jayce72

Registered User.
Local time
Today, 11:47
Joined
Sep 26, 2011
Messages
60
That works perfect, thank you - BUT I have several entries that are over 100 - and it looks like your query only goes to 100. Can it go higher - then you've cracked it :)


Cracked it myself -



SELECT Table.*
FROM Table, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 100*Abs([Deca].[id] Mod 100) AS Tens, Abs([Uno].[id] Mod 100) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
WHERE F.Factor<CountOfFruit
ORDER BY ID;
 

Users who are viewing this thread

Top Bottom