MS Access Query Question

Factual Questions
1

I’m using MS Access 2003. I have a database query which produces a list of office codes and the authorized budget codes for each office, e.g.:

Office Budget Code
123 121
123 312
123 505
456 045
456 121
456 431
456 731
456 789
456 831

I want to end up with a second query showing the office code followed by a list of applicable budget codes concatenated into a single text field:

Office Budget Codes
123 121, 312, 505
456 045, 121, 431, 731, 789, 831

How do I concatenate the budget codes into a single text string?

2

I don’t believe it’s possible in Access without code. You’ll need to write some VBA, or just borrow some code that someone else has written.

Microsoft Access tips: Concatenate values from related records seems to have a pretty functional example. Performance may not be great, depending on your data volumes, but should work acceptably. If there are still issues, give me a shout and I’ll see if I can help.

Alternatively, you can write a crosstab report and then export the data, but this looks like a neater solution, particularly since someone else has already done all the hard work for you! :wink:

3

Are cursors available in Access (It’s been a while)? If you were using SQL Server then you could easily use a cursor here.

4

Here’s a way, but you have to set a maximum number of budget_codes that can be processed this way, lots of work tables to make it easier to follow:

Step 1 - create a work table (wt1) with a sequence number for each row in office_code, budget_code order

Office_code
Budget_code
Sequence
Seq2 (as zero initially, will be updated later for simpler calcs)

Step 2 - create a work table (wt2) with the minimum sequence for each office_code

office_code
Min(sequence) as min_sequence
group by office_code
Step3 - update Seq2 with relative sequence number within office_code

join wt1 to wt2
set Seq2=(wt1.sequence-wt2.min_sequence)+1
Step 4 - Convert budget_code rows to concatenated column of values



select
 office_code
,''+max(isnull(t1.budget_code,''))
   +max(isnull(t2.budget-code,''))
   ...etc.

from wt2  drv
left outer join wt1 t1
   on t1.office_code=drv.office_code
 and t1.seq2=1
left outer join wt1 t2
  on t1.office_code=drv.office_code
 and t1.seq2=2
...
(do these joins one for each budget_code, if they can have 15 then 15 joins, I know a little ugly)

group by
 office_code
5

No cursors in Access, unfortunately. And even in SQL Server, there are far more efficient ways of concatenating strings than using cursors.

More ways than you can shake a stick at: Concatenating Row Values in Transact-SQL - Simple Talk

I tend to use the FOR XML construct, as it’s the simplest to implement and read, but CTE approaches work as well.

6

I’m trying the first solution, as this seems like it would be more useful for some similar cases, but I’m not very familiar with VBA and the code has a compile error “User-defined type not defined” at “Dim rs As DAO.Recordset”.

7

Ah, you’re missing a reference to the DAO libraries. You need to add that reference, as per the instructions here:

The name of the library you need is “Microsoft DAO 3.6 Object Library”. More detailed instructions here: http://www.accessmvp.com/twickerath/articles/adodao.htm

8

Thanks to all for the replys. I now have a concatenated text field for my report!