Terminology first:
Macro - In Access one of the two totally separate programing languages. This language is mostly avoided unless one is posting to a SharePoint site.
VBA - In Access one of the two totally separate programming languages. Most Applications development is undertaken using this programing language.
In Access, VBA is NOT the same as Macro. One can call the other using various methods. Of the two, VBA is widely considered to be more robust and useful than the Access-Macro
Second:
I think you can do this with just a cross-tab query and then maybe a select query afterwards to combine the results; however, give me a few moments to think this thru as the logic isn't clear in my head yet... I'm a little slower on the SQL than the others. (^_^) Rabbit might be along and I think he dreams in SQL (^_^)
In the meantime take a look at:
•Crosstab query techniques - row totals, zeros, parameters, column headings - Allen Browne
as for the code... please be aware that we don't normally give out code to start with - think of it as teaching to fish.
The basic outline for would be:
Create a table with the field names you need, is very important to keep in mind when naming fields it is preferable to use alphanumeric and preferably no spaces just underscores and avoid:
Access 2007 reserved words and symbols and
Problem names and reserved words in Access (AllenBrowne)
Next open your VBA editor (alt-f11) and make sure that that the Options are properly set ([*]
> Before Posting (VBA or SQL) Code):
Then what I would do here is
Open a DAO record set on your table
- one record set
grouped by PROJECT_ID
- second record set that would be feed by outside loop
- third record set that would hold a filter of second.
Move to the first record in both record sets
Outside loop - pull PROJECT_ID
save to new table
inside loop - set the second recordset using PROJECT_ID from the outside loop to pull just those records with that PROJECT_ID. setup the filter on this recordset and (third recordset) and return count for records ABBREVIATED_NAME <> to null or empty-string.
Depending on this count, either step thru the filtered set or the unfiltered set and pull and concatenate the proper fields.
Once done, then store result to the table.
Release the inside loop record sets
Step the outer loop
repeat.