473,323 Members | 1,537 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

access 97: queries

I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D
Nov 13 '05 #1
4 1242
AFAIK, there's no way to do that using the query builder. You can certain
group by A, B and sum C, but you can't concatenate in SQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D

Nov 13 '05 #2
That is right. No way to do it in querybuilder but I had an idea once for
the same situation. I came up with a function that did the concatenation.
The problem with it is I am not sure if it will work in all situations. For
example: using it with fields that are very long strings--maybe access will
have some kind of limitation as to how much can be concatenated. Anyway
here is the function if you want to try--check my syntax.

Function Combine(sql)
Set rst = DBEngine.Workspaces(0).Databases(0).OpenRecordset( sql)
While Not rst.EOF
temp = temp & rst.Fields(0)
rst.MoveNext
If Not rst.EOF Then temp = temp & " "
Wend
Combine = temp
End Function
the sql parameter needs to be a valid SELECT statement that has the field
you want to concatenate as the FIRST field (rst.Fields(0)). It also needs
to include any grouping or sorting so that you get "The Cow Goes Moo"
instead of "Moo Goes Cow The". I will warn you that this may be a very
inefficient way of doing this if working with large tables--it was something
I made on the fly and didn't test it alot.

Also, the way it is written above it will separate each field with a space.
This can be changed to a comma or dash or empystring or whatever you want.
cheers.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ce************@news04.bloor.is.net.cable.roge rs.com...
AFAIK, there's no way to do that using the query builder. You can certain
group by A, B and sum C, but you can't concatenate in SQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D


Nov 13 '05 #3
doing a totals query and then throwing in fConcatChild from Accessweb?
No other idea!
Nov 13 '05 #4
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
doing a totals query and then throwing in fConcatChild from Accessweb?
No other idea!

Thank you all for reaffirming my doubts. I appreciate the advise :)
Cheers - Jung
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Jegger | last post by:
Hello! We have following situation; network with 100 users, aplication developed in Access, user DB deployed on SQL Server. Is it better to create query inside aplication (with code) and then...
3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
1
by: John E | last post by:
I have an Access 2000 database in which there are remotely linked SQL tables and a couple of local tables. If I have queries in Access that are designed to pass data between these remote linked...
29
by: Mark B | last post by:
We have an Access app (quite big) at www.orbisoft.com/download. We have had requests by potential users to have it converted to an SQL version for them since there corporate policy excludes them...
25
by: cory | last post by:
Hi, I have an Access database and am having an ASP.NEt application written for it. It is almost complete. I have a hosting company that I signed up with a month ago but before I did anything I...
3
by: pbbriggs | last post by:
I will try to be as descriptive as possible, but this error has me stumped and I am not sure what relevant info to include.... I am running Access XP on a Windows XP machine. I initially began...
11
by: maryjones11289 | last post by:
Hi, I have an app (lost the source code) that performs many different queries in order to achieve the results I'm looking for. Re-developing the app isn't really a big issue...but what would be...
10
by: Hank | last post by:
We have just recently migrated the data from our Access 2000 backend to Postgres. All forms and reports seem to run correctly but, in many cases, very slowly. We do not want to switch over until...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
3
by: Bret Kuhns | last post by:
I recently started a co-op/internship at a company and they are looking to migrate a large legacy supported application from OLEDB to SQL Server. I'm doing prelim work in experimenting with the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.