# MS Access Query Question **URL:** \ **Category:** Factual Questions\ **Created:** [September 1, 2011, 7:53pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878 "2011-09-01T19:53:06Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Bookkeeper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bookkeeper/32/281_2.png) [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)\ **Post date:** [September 1, 2011, 7:53pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/1 "2011-09-01T19:53:06Z")
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? ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [September 1, 2011, 8:20pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/2 "2011-09-01T20:20:29Z")
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](http://allenbrowne.com/func-concat.html) 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! 😉 ---
**Author:** ![robert\_columbia](https://avatars.discourse-cdn.com/v4/letter/r/e79b87/32.png) [@robert\_columbia](https://boards.straightdope.com/u/robert_columbia)\ **Post date:** [September 1, 2011, 9:24pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/3 "2011-09-01T21:24:21Z")
Are cursors available in Access (It’s been a while)? If you were using SQL Server then you could easily use a cursor here. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [September 1, 2011, 9:27pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/4 "2011-09-01T21:27:55Z")
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 ```auto 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 ``` ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [September 1, 2011, 9:28pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/5 "2011-09-01T21:28:42Z")
> [@robert\_columbia](#): > > Are cursors available in Access (It’s been a while)? If you were using SQL Server then you could easily use a cursor here. 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](http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) I tend to use the FOR XML construct, as it’s the simplest to implement and read, but CTE approaches work as well. ---
**Author:** ![Bookkeeper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bookkeeper/32/281_2.png) [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)\ **Post date:** [September 2, 2011, 1:58am UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/6 "2011-09-02T01:58:30Z")
> [@Dervorin](#): > > 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](http://allenbrowne.com/func-concat.html) 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! 😉 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”. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [September 2, 2011, 7:43am UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/7 "2011-09-02T07:43:54Z")
> [@Bookkeeper](#): > > 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”. Ah, you’re missing a reference to the DAO libraries. You need to add that reference, as per the instructions here: > **[Set References to Type Libraries \[Access 2003 VBA Language Reference\]](https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa210708(v=office.11)?redirectedfrom=MSDN)** 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](http://www.accessmvp.com/twickerath/articles/adodao.htm) ---
**Author:** ![Bookkeeper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bookkeeper/32/281_2.png) [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)\ **Post date:** [September 2, 2011, 2:00pm UTC](https://boards.straightdope.com/t/ms-access-query-question/594878/8 "2011-09-02T14:00:41Z")
Thanks to all for the replys. I now have a concatenated text field for my report!