Counts in a query

[Wozza]

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.

 

[arnelgp]

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:

SELECT yourTable.ProjectID, tblNum.num
FROM yourTable, tblNum
WHERE (((tblNum.num)<=[yourTable].[Count]))
ORDER BY yourTable.ProjectID, tblNum.num;

 

[Wozza]

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:

SELECT yourTable.ProjectID, tblNum.num
FROM yourTable, tblNum
WHERE (((tblNum.num)<=[yourTable].[Count]))
ORDER BY yourTable.ProjectID, tblNum.num;

 

[Wozza]

Hi, thank you for taking the time to reply. However, when I run the query, it comes back blank. Any ideas why?

 

[arnelgp]

Delete the Joining Line from the tables.

 

[Wozza]

Delete the Joining Line from the tables.

Hi, unfortunately it didn't work

 

[Wozza]

Delete the Joining Line from the tables.

Thank you. I've been playing around and now have it working.