Query to concatenate

M

mari_hitz

Registered User.
Local time
Today, 02:10
Joined
Nov 12, 2010
Messages
120
Hi everybody,

Hope you are great! I just wanted to let you know that I have searched into the net for the solution to this and on the forum but found no answer.
I wanted to know if it is possible to do the following:
I have a table that contains the name of the person, the numbers of days spent in a country. On this table the same person can appear several times so I wanted to see only once that person and to all the countries she/he had visited and the amount of days spent in each country. I could easily do this with a cross tab query.
However, I wanted to know if there is a way to create a field, with built in, that can concatenate me all the information from that row in one field so I can see:
[Name]+[Country]+[N° of days spent]. I tried to do it but I did not had any sucess, do you have any idea? Thanks!
 
Do you have control over the source of this data? Because as it stands your data is not normalized and that is why you're having such trouble getting what you want out of it.

You should have three tables: tablePeople, tableCountries, tableVisits. tableVisits would contain the personID and the countryID, and the length of that visit. Then a simple query would tell you all of this data anytime you wanted, in less time than it takes to ask.

As long as your data is of good quality (John Smith is always John Smith, not John H. Smith due to completeness or Jonh Smith due to bad data entry), you can make those three tables pretty easily from what you have now.
 
Actually if that really is all the data in your table now, you can do a rough version with a Totals Query (sigma button, top right).

PersonName and Country should stay Group By, NoOfDays should change to Sum. Run, sort as needed.
 
Hi David!

Thanks for your reply! I understand what you say about normalize data, however the data that is being entered in the table is dinamic, meaning they come from an excel file which is linked to the access.
The aim of this access database is to send an email to each person that appears on the table indicating that they had spent X amount of days in X country. My acess database already sends email based on a linked table, however I would like in the body of the template to appear the ALL the information available in the table from one person in just one email, instead of sending as much emails as the person appears in the table.
I don't know if this makes any sense, and if its not, then I guess what I am trying to achieve is impossible to do in acess.

Thanks :)
 
Okay, my second reply should give you a way to summarize all of that data. Are you now trying to figure out how to add it into your emails automatically?

I understand about the linked data you have no control over, I was just hoping for the best. ;)

Have you worked at all with Recordsets or VBA before? There are ways to get that data into your email, but we'll need more information.
 
David R said:
Okay, my second reply should give you a way to summarize all of that data. Are you now trying to figure out how to add it into your emails automatically?

I understand about the linked data you have no control over, I was just hoping for the best. ;)

Have you worked at all with Recordsets or VBA before? There are ways to get that data into your email, but we'll need more information.
Click to expand...

Thanks david! I am attaching a copy of the testing database I am creating, in this testing I did not included a linked table, but a table itself on access. However, the format of the table will be the same. I want to send an email with the information from those tables in the body of the email or in an attachment, but one mail to the person with all the information included in the table. I don't know if I had explained myself good. Please let me know.
 

Attachments

Wow. I don't suppose you can kick the person who designed that Excel table, can you?!? :D That's awful... anyway, try this in your email command button.

Code: 
.HTMLBody = "<html><body><font face=calibri> Dear Assignee,</font></body></html>" & vbNewLine & _
        "<html><body><font face=calibri>" & vbNewLine & _
        "<p>Our records indicate that you have the following amount of days in the respective countries:<ul>" & _
[B]"<li><b>" & Me.Home_Location & "(Home):</b> " & Me.N_Days_in_Home & "</li> & vbNewLine & _
"<li><b>" & Me.Host_Location & "(Host):</b> " & Me.N_Days_in_Host & "</li></ul> & vbNewLine & _[/B]
"We appreciate your prompt attention to this matter.</p>
<p>Regards,<BR><BR>Service Delivery</p></font></body></html>"
This is extremely quick and dirty (aside from some HTML cleanup), but I think you can get the idea of what I'm doing here.
 
Thanks David! That's great, however what I was looking for is to send in one email ALL the information available for the person.
With the code you had provided me, similar to the one I had, it sends one email per row of information. If "marina.silva" appears seven times, I don't want "marina.silva" to receive seven emails, I want her to receive one email with all her data available. That is why I wanted to make trough a query something similar to a concatenate, or maybe a new column field in the table itself, where I have all the information for that person concatenated and make the code to refer to that field and sends one email per person containing all the data.
Again, I don't know if I explained myself better, but this is what I am trying to achieve, so far I did not find anything in the web so I believe it is impossible to achieve.
 
You'll have to structure the VBA a bit differently then. Essentially what you want is to filter your recordset down to just the ones that match "marina.silva", then step through all of her entries in that table, adding them line by line to .HTMLBody.

You can get away with some inefficient queries to do this if your VBA is inferior to your query skills, but we'll need more information. Does everyone get an email every 'time' or is the plan to only email then when they hit a certain threshold of days remaining?
 
You must log in or register to reply here.

Similar threads

Cotswold
Sly and Sneaky Starmer's Latest Ego Trip.
2 3
Replies
45
Views
1,488
Isaac
Isaac
NauticalGent
Using subqueries to establish a timeline.
Replies
0
Views
181
NauticalGent
NauticalGent
E
Solved DLookup & Concatenate
Replies
11
Views
498
cheekybuddha
cheekybuddha
E
SQL VIEW, query executes but the text does not display in the editor window
Replies
2
Views
289
isladogs
isladogs
S
Unbound form and combobox for Country and Salution Selection
Replies
7
Views
256
silentwolf
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom