UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Counting Increments And Crosstabs, Access 2013    
 
   
oleander
post Aug 7 2019, 05:58 PM
Post#1



Posts: 23
Joined: 11-March 14



I'm trying to do a couple of different things and I'm really out of my league in trying to figure this out.

I have a pretty standard select query:

SELECT tbl_Main.ID, tbl_Main.Course_ID, tbl_Main.Originator_Email
FROM tbl_Main;

tbl_Main.ID is a unique ID. Originator_Email can appear multiple times. So we basically have data like this:

ID CourseID Originator_Email
1 ABC-1 1@email.com
2 ABC-2 1@email.com
3 ABC-3 2@email.com
4 ABC-4 3@email.com
5 ABC-5 1@email.com


I'm trying to get the query to count each increment of a repeating e-mail address, so that the query returns results like this:

ID CourseID Originator_Email Count
1 ABC-1 1@email.com 1
2 ABC-2 1@email.com 2
3 ABC-3 2@email.com 1
4 ABC-4 3@email.com 1
5 ABC-5 1@email.com 3


Then, lastly, I'm trying to get it to do a cross tab so that the e-mail addresses are the rows, the count are the columns, and the CourseID are the fields in the crosstab, so it looks like this:


Originator_Email 1 2 3
1@email.com ABC-1 ABC-2 ABC-5
2@email.com ABC-3
3@email.com ABC-4


Can someone help? I'm been wracking my brain trying to figure it out and so far I'm failing lol
Go to the top of the page
 
RJD
post Aug 7 2019, 06:13 PM
Post#2


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


Hi: See the demo attached, with your data. See if this is what you are trying to achieve.

HTH
Joe
Attached File(s)
Attached File  CountingIncrementsAndCrosstabs.zip ( 18.54K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Aug 7 2019, 06:26 PM
Post#3


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


Just another note: This can be done with all the courses in a single field, comma separated - and sequencing is not necessary then. An alternative thought ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Aug 7 2019, 06:29 PM
Post#4



Posts: 3,299
Joined: 27-February 09



You win. I had to do it in SQL Server, because Access doesn't have ROW_NUMBER() or windowing functions. Instead of CASE WHEN...END you could use IIF([test],true part, false part). I think you have to use DCOUNT() for the ROW_NUMBER part, though.

CODE
SELECT Originator_EMail
    , MAX(pe.rn1) AS v1
    , MAX(pe.rn2) AS v2
    , MAX(pe.rn3) AS v3
FROM (
    SELECT oe.Originator_EMail
        , CASE WHEN rnX = 1 then CourseID else NULL END rn1
        , CASE WHEN rnX = 2 THEN CourseID else NULL END rn2
        , CASE WHEN rnX = 3 THEN CourseID else NULL END rn3
    FROM
        (SELECT ID
            , CourseID
            , Originator_Email
            , ROW_NUMBER() OVER (PARTITION BY Originator_EMail ORDER BY ID) AS rnX
        FROM #Listing) oe
    ) pe
GROUP BY pe.Originator_EMail;
Go to the top of the page
 
RJD
post Aug 7 2019, 06:37 PM
Post#5


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


Hi Pieter: Yes, I did use DCount (although there is another method to get the sequence without a domain function) ..

qryMainWithSeq:

CODE
SELECT tbl_Main.ID, tbl_Main.CourseID, tbl_Main.Originator_Email, DCount("*","[tbl_Main]","[Originator_Email]='" & [Originator_Email] & "' And [ID]<=" & [ID]) AS EmailCount
FROM tbl_Main;


CODE
TRANSFORM Max(qryMainWithSeq.CourseID) AS MaxOfCourseID
SELECT qryMainWithSeq.Originator_Email
FROM qryMainWithSeq
GROUP BY qryMainWithSeq.Originator_Email
PIVOT qryMainWithSeq.EmailCount;


Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
oleander
post Aug 7 2019, 06:58 PM
Post#6



Posts: 23
Joined: 11-March 14



THANK YOU

I have to do this a million times during the year. You've just saved me a ton of time and effort. I can't tell you how excited I am lol
Go to the top of the page
 
RJD
post Aug 7 2019, 07:02 PM
Post#7


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that works for you.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Aug 7 2019, 07:22 PM
Post#8



Posts: 3,299
Joined: 27-February 09



So maybe I need a cheat sheet on Domain functions in Access?
Go to the top of the page
 
RJD
post Aug 7 2019, 07:39 PM
Post#9


UtterAccess VIP
Posts: 10,024
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dale.fye
post Aug 8 2019, 05:35 AM
Post#10



Posts: 160
Joined: 28-March 18
From: Virginia


Using domain functions in queries can be time consuming, especially with large data sets

Another method to get the sequence, without the domain function would look like:

CODE
SELECT M1.Originator_Email
, M1.CourseID
, Count(M2.ID)
FROM tbl_Main as M1
INNER JOIN tbl_Main as M2 ON M1.Originator_email = M2.Originator_email
AND M2.ID <= M1.ID
GROUP BY M1.Originator_email, M1.CourseID


This is a non-equi join, which cannot be visualized fully in the query designer. To create this type of join, I generally start out in the query grid, add the same table to the grid (twice) and then create = joins on the two fields being used (Originator_email and ID). Then I go to SQL view and modify the join for the secondary join.

IN this case, what I've done is created a query that joins a table to itself on the email and from the 2nd instance of the table, I want to include all records where the email is the same but the ID value is <= the ID in the main instance. If I included all fields from both tables, and left out the grouping, this would result in a recordset that looks something like:
CODE
1 ABC-1 1@email.com   1 ABC-1 1@email.com

2 ABC-2 1@email.com   1 ABC-1 1@email.com
2 ABC-2 1@email.com   2 ABC-2 1@email.com

3 ABC-3 2@email.com   3 ABC-3 2@email.com

4 ABC-4 3@email.com   4 ABC-4 3@email.com

5 ABC-5 1@email.com   1 ABC-1 1@email.com  
5 ABC-5 1@email.com   2 ABC-2 1@email.com
5 ABC-5 1@email.com   5 ABC-5 1@email.com


When you add in the Count and the Group By, it will give you the list you want.

CODE
TRANSFORM Max(Q.CourseID) AS MaxOfCourseID
SELECT Q.Originator_Email
FROM (
SELECT M1.Originator_Email
, M1.CourseID
, Count(M2.ID) as EmailCount
FROM tbl_Main as M1
INNER JOIN tbl_Main as M2 ON M1.Originator_email = M2.Originator_email
AND M2.ID <= M1.ID
GROUP BY M1.Originator_email, M1.CourseID
) as Q
GROUP BY Q.Originator_Email
PIVOT Q.EmailCount;

This post has been edited by dale.fye: Aug 8 2019, 05:36 AM

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 03:23 PM