Totals Query Help

muster101

New Member
Joined
Mar 14, 2002
Messages
13
Howdy all...need some expert help. Trying to create a Totals Query.

I have a user_table set up as follows:

Code:
user	old	 new	date          count_user
user1	100	200	10/2/2006            2
user1	200	300	10/2/2006            2
user2	100	200	10/2/2006            1
user3	100	200	10/2/2006            2
user3	200	300	10/2/2006            2
user4	100	200	10/2/2006            4
user4	200	300	10/2/2006            4
user4	300	400	10/2/2006            4
user4	400	500	10/2/2006            4
user5	300	400	10/2/2006            1
user6	100	200	        	          5
user6	200	300	        	          5
user6	300	400	        	          5
user6	400	500		                  5
user6	500	600	        	          5
I am trying to generate a totals query that will show the following:

Code:
date		count_user	total records		unique user
10/2/2006		1		            2			2
10/2/2006		2		            4			2
10/2/2006		4		            4			1
			      5		            5		   1

group by	group by	count of count_user		?

I can get the 1st 3 columns of the totals query to show what i want but can't for the life of me get the last one to work (unique_user).

Any help you could give is appreciated...i did this in design view but do know how to display the generated sql and modify/add to once pointed in the right direction. sorry this is all new to me
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
In order to do a distinct count in Access you need to either break your query in two or do a subquery. Below is how you would do this with a subquery:

<script type="text/javascript" > function myFunc356954455(myData,myVal) { setTextmyFunc356954455("Info356954455" ,myVal); setTextmyFunc356954455("Data356954455" ,myData); } </script><script type="text/javascript" > function setTextmyFunc356954455(myID,myVal) { if (navigator.appName == 'Microsoft Internet Explorer') { document.all(myID).innerText = myVal; } else{ document.getElementById(myID).textContent = myVal; } }</script><STYLE TYPE="text/css" > TD.AccTBInner { BORDER-RIGHT: none; BORDER-TOP: none; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #0000FF; FONT-SIZE: 12pt; FONT-FAMILY: times; FONT-STYLE: bold; COLOR: #ffffff;"} </STYLE><STYLE TYPE="text/css" > TD.AccTBMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #0000FF; COLOR: #000000; FONT-SIZE: 8pt; FONT-FAMILY: times; "}</STYLE><STYLE TYPE="text/css" > TD.AccHDRMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-FAMILY: arial; "}</STYLE><STYLE TYPE="text/css" > TD.AccHDRLeftElem { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; WIDTH: "2%"; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: webdings; "}</STYLE><STYLE TYPE="text/css" > TD.AccDataElem { BORDER-RIGHT: #d4d0c8 0.25pt solid; BORDER-TOP: #000000 0.25pt solid; BORDER-LEFT: #000000 0.25pt solid; BORDER-BOTTOM: #d4d0c8 0.25pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; FONT-FAMILY: Sans-Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyMain { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyInnerLeft { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyInnerRight { BACKGROUND-COLOR: #d8d8d8; WIDTH: "10%"; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: Right; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyButton { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #ffffff 0.5pt solid; BORDER-LEFT: #ffffff 0.5pt solid; WIDTH: "2%"; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Webdings; BACKGROUND-COLOR: #d8d8d8; TEXT-ALIGN: center; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; WIDTH: "10%"; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: Left; "}</STYLE><STYLE TYPE="text/css" > TD.AccInfoBarInnerRight { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; WIDTH: "25%"; TEXT-ALIGN: right; "}</STYLE><STYLE TYPE="text/css" > TD.AccInfoBarData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: left; "}</STYLE><CENTER><TABLE cellSpacing=0 cellPadding=2 align=center width=100% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 5 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><FONT color=white>Query1 : Select Query</FONT></TD><TD CLASS="AccTBInner" align=right >Access 2000</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElem" colSpan= 5 >SELECT
x.date
, x.count_user
, x.total_records
, Count(x.user) AS unique_user
FROM
[SELECT
user_table.date
, user_table.count_user
, Count(user_table.count_user) AS total_records
, user_table.user
FROM
user_table
GROUP BY
user_table.date
, user_table.count_user
, user_table.user
]. AS x
GROUP BY
x.date
, x.count_user
, x.total_records
ORDER BY
x.count_user;
</TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=middle ><CENTER>date</CENTER></TD><TD CLASS="AccHDRMain" align=middle ><CENTER>count_user</CENTER></TD><TD CLASS="AccHDRMain" align=middle ><CENTER>total_records</CENTER></TD><TD CLASS="AccHDRMain" align=middle ><CENTER>unique_user</CENTER></TD></TR><TR ><TD ID="currRec356954455" CLASS="AccHDRLeftElem" >4</TD><TD CLASS="AccDataElem" >10/2/2006</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >2</TD></TR><TR ><TD ID="currRec356954455" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >10/2/2006</TD><TD CLASS="AccDataElem" >2</TD><TD CLASS="AccDataElem" >2</TD><TD CLASS="AccDataElem" >2</TD></TR><TR ><TD ID="currRec356954455" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >10/2/2006</TD><TD CLASS="AccDataElem" >4</TD><TD CLASS="AccDataElem" >4</TD><TD CLASS="AccDataElem" >1</TD></TR><TR ><TD ID="currRec356954455" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >
</TD><TD CLASS="AccDataElem" >5</TD><TD CLASS="AccDataElem" >5</TD><TD CLASS="AccDataElem" >1</TD></TR><TR ><TD ID="IE356954455" CLASS="AccIEOnlyMain" colSpan= 5 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccIEOnlyButton" > 9 </TD><TD CLASS="AccIEOnlyButton" > 3 </TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccIEOnlyButton" > 4 </TD><TD CLASS="AccIEOnlyButton" > : </TD><TD CLASS="AccIEOnlyInnerLeft" > of 4</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 5 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data356954455" CLASS="AccInfoBarInnerRight" >Query1</TD><TD ID="Info356954455" CLASS="AccInfoBarData" > Record Count: 4</TD></TR></TABLE></TD></TR></TABLE></CENTER><script type="text/javascript">if (navigator.appName != 'Microsoft Internet Explorer') { var a = document.getElementById("currRec356954455"); a.innerHTML = '
'; var b = document.getElementById("IE356954455"); b.textContent = 'Table format has been altered for non-IE browsers '; b.style.fontSize = 10;}</script>

hth,
Giacomo
 

Forum statistics

Threads
1,137,207
Messages
5,680,194
Members
419,888
Latest member
Prasad K

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top