Adding columns to a getrows array

[emozley]

Hi,

If I have an array that is built using the GetRows function can I then add additional 'slots' to it?

So far I have

TBL.Open "SELECT cAuthorID, cFirstName, cLastName FROM Author", DB
If Not TBL.EOF Then
AuthorArray=TBL.GetRows()
End If
TBL.Close

I would then like to add some extra elements so that I can store extra information for each author.

Basically I want to keep totals for each author of jobs for each status letter. The jobs in the recordset have an AuthorID and a job status - the job status can be set to 'B', 'I', 'D', 'U' or 'R'.

Hope that makes sense if there's anything else please let me know.

Thanks

Ed

 

[XgrinderX]

I am pretty sure you can. You would have to redimension the array using the ReDim command. Have a Google for that command and I think you will be on the right track.

 

[gmmastros]

You can probably do this....

TBL.Open "SELECT cAuthorID, cFirstName, cLastName[!], '' As Status[/!] FROM Author", DB

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[XgrinderX]

Ah yes, what George said would work too, probably easier than my suggestion.

 

[Tarwn]

Or even:

SELECT cAuthorID, cFirstName, cLastName, JobField, COUNT(cAuthorId) as JobCount
FROM Author
GROUP BY cAuthorID, cFirstName, cLastName, JobField
ORDER BY cAuthorID, JobField

Or something like that. That should give you the total record count for each author for each Job code in a sepearte record. If you wanted you could do one record for each author with a field for each job like so:

SELECT cAuthorID, cFirstName, cLastName, 
   (SUM(CASE WHEN JobField = 'B' THEN 1 ELSE 0)) AS JobB,
   (SUM(CASE WHEN JobField = 'I' THEN 1 ELSE 0)) AS JobI,
   (SUM(CASE WHEN JobField = 'D' THEN 1 ELSE 0)) AS JobD,
   (SUM(CASE WHEN JobField = 'U' THEN 1 ELSE 0)) AS JobU,
   (SUM(CASE WHEN JobField = 'R' THEN 1 ELSE 0)) AS JobR
FROM Author
GROUP BY cAuthorID, cFirstName, cLastName

That one is a bit more complicated because if you ever want to add a job code you have to alter your code, but it should give you one record per author with a column for each of the 5 job codes and how many entries the author has for it.

I know this wasn't what you were asking for, just trying to look at the problem from other directions.

-T