Combine and Count

[michibahn]

Hi I need to combine a multiple rows into a summarized single column plus its total. Here's the current scenario:

FROM THIS:
DATE --- FIELD1 --- FIELD2 --- COUNT
m/d/y --- xx11 --- 1 --- 1
m/d/y --- yy22 --- 2 --- 1
m/d/y --- xx11 --- 3 --- 1
m/d/y --- xx11 --- 4 --- 1
m/d/y --- zz33 --- 5 --- 1
m/d/y --- zz33 --- 6 --- 1
m/d/y --- yy22 --- 7 --- 1

TO THIS:
DATE --- FIELD1 --- FIELD2 --- COUNT
m/d/y --- xx11 --- 1,3,4 --- 3
m/d/y --- yy22 --- 2,7 --- 2
m/d/y --- zz33 --- 5,6 --- 2

I'm using MS Access 2003.

I hope someone can help me on this.

TIA!

 

[vbaInet]

Have a look at this: http://allenbrowne.com/func-concat.html

Create a totals query so that you get a count grouped by Date and Field1. Use the function above to get the concatenated values of field2.

 

[michibahn]

Hi vbaInet, thanks for taking time to reply in my question. I'm still a newbie in access and I'll try to check on your suggestion.

If i had to create a totals query grouped by Date and Field1, I will have to remove Field2 in this query right?

Thanks!

 

[vbaInet]

If i had to create a totals query grouped by Date and Field1, I will have to remove Field2 in this query right?

Correct!

You only need Date and Field1.

The link below explains how to do a totals query. Look at the Count section:

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

 

[michibahn]

Hi vbaInet, I've done that already. Now, I need to insert Field2 in a new column of the total query. Will this be a new query calling the total query?

Thanks again!

 

[vbaInet]

You are doing very well

It's up to you. You may put it in the same query or as a new query. I would advise a new query just so that the query doesn't run for more than is necessary. Naturally it shouldn't, but just in case.

 

[michibahn]

Hi vbaInet, ok thanks a lot! I will try it and hopefully I will get the correct results. Thanks for your help

 

[vbaInet]

Let us know how it goes

 

[michibahn]

Hi vbaInet, I'm creating a new query to show the Field2 now, How do I get the Field2 now? I called the total query which has date, Field1, and counts columns. Do I have to call again the original query which has all the fields?

sorry for these dumb questions...

 

[vbaInet]

The link I gave you in my first post is for field2.

 

[michibahn]

So in my new query I will need to create the module stated in your first post then I will have the total query + the original table that I have showing all the fields?

 

[vbaInet]

The link explains how to use the module. It should be placed in a new global Module.

So you created a totals query, let's call it qryTotal. You will now create a new query based on qryTotal, pull in the three fields. Then you will use the function inside this new query to get Field2.

See the image attached.

 

[michibahn]

Yup i've created already the module and pasted the contents in your first post.

I'm just wondering where does the contents of Field2 will come from since my qryTotal will only contain Date, Field1 and Counts?

this is what i have right now in my DB.

qryTotal
DATE | FIELD1 | TOTALCOUNTS
9/16/2011 10:17:27 AM | M114-106-172 | 1
9/16/2011 10:17:27 AM | M216-185090-144 | 2
9/16/2011 10:17:27 AM | TP259-147095-136 | 1
9/16/2011 10:24:48 AM | M216-185090-144 | 1

origTable
DATE | FIELD1 | FIELD2 | COUNT
9/16/2011 10:17:27 AM | M114-106-172 | 1362 | 1
9/16/2011 10:17:27 AM | M216-185090-144 | 20 | 1
9/16/2011 10:17:27 AM | M216-185090-144 | 21 | 1
9/16/2011 10:17:27 AM | TP259-147095-136 | 63 | 1
9/16/2011 10:24:48 AM | M216-185090-144 | 9 | 1

 

[vbaInet]

So it will look like this -->

ConcatRelated("Field2", "origTable", "[DateField] = " & [DateField] & " AND [Field1] = '" & [Field1] & "'")

Just change the bits in red.

 

[michibahn]

Thanks vbaInet! will try it.. let you know..

 

[michibahn]

Can't seem to make it work. Would you mind sending me the exact code for the new query that I'm creating based on the qryTotal and origTable that I have?

TIA!

 

[vbaInet]

What exact code?

Explain what isn't working. Is it throwing an error message? Is the column showing nothing? Be more explicit.

 

[michibahn]

Sorry.. I mean the exact SQL query in my new query.

Here's what I did. In my new query, i called origTable then the contents of my code is:

SELECT origTABLE, ConcatRelated("FIELD2","origTABLE","[DATE] = " & [DATE] & " AND [FIELD1] = '" & [FIELD1] & "'")
FROM [origTABLE];

When I run the query, a windows pops out asking me to enter a number in origTable

Thanks!

 

[vbaInet]

Just drop this in a new column inside your query:

ConcatRelated("FIELD2","origTABLE","[DATE] = " & [DATE] & " AND [FIELD1] = '" & [FIELD1] & "'")
FROM [origTABLE];

Move away from the column and it will look like this:

[COLOR=Red]Expr1:[/COLOR] ConcatRelated("FIELD2","origTABLE","[DATE] = " & [DATE] & " AND [FIELD1] = '" & [FIELD1] & "'")
FROM [origTABLE];

You can rename Expr1 to a name of your choice.

 

[michibahn]

Hi vbaInet.. here's the error I have now.

and here's the SQL code result after moving away from the new column where i pasted your code.

SELECT origTable.DATE, origTable.FIELD1, origTable.FIELD2, origTable.COUNT, ConcatRelated("FIELD2","origTable","[DATE] = " & [DATE] & " AND [FIELD1] = '" & [FIELD1] & "'") AS Expr1
FROM origTable
GROUP BY origTable.DATE, origTable.FIELD2, origTable.COUNT;