Counts in a query (1 Viewer)

Wozza

New member
Local time
Today, 12:26
Joined
May 19, 2020
Messages
13
Hi, I have a query where I want to allocate a number to each of the projectID's. e.g. 14IND01 has 4 records, how do I get a list showing 14IND01 1, 14IND01 2, etc. Thank you in advance for your help.

1602065073948.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:26
Joined
May 7, 2009
Messages
19,175
create another Table (tblNum) with 1 field num (long integer).
fill this table with records, numbers (1 to 100).

create a Query. add your Table and tblNum:
Code:
SELECT yourTable.ProjectID, tblNum.num
FROM yourTable, tblNum
WHERE (((tblNum.num)<=[yourTable].[Count]))
ORDER BY yourTable.ProjectID, tblNum.num;
 

Wozza

New member
Local time
Today, 12:26
Joined
May 19, 2020
Messages
13
create another Table (tblNum) with 1 field num (long integer).
fill this table with records, numbers (1 to 100).

create a Query. add your Table and tblNum:
Code:
SELECT yourTable.ProjectID, tblNum.num
FROM yourTable, tblNum
WHERE (((tblNum.num)<=[yourTable].[Count]))
ORDER BY yourTable.ProjectID, tblNum.num;
 

Wozza

New member
Local time
Today, 12:26
Joined
May 19, 2020
Messages
13
Hi, thank you for taking the time to reply. However, when I run the query, it comes back blank. Any ideas why?
1602070465363.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:26
Joined
May 7, 2009
Messages
19,175
Delete the Joining Line from the tables.
 

Users who are viewing this thread

Top Bottom