Totals Query Help

M

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
How To Compare Two Columns In Excel?
Replies
3
Views
262
Gigiwan
G
P
  • Question
SQL Access Query
Replies
2
Views
458
PBE
P
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
273
skyh3ck
skyh3ck
V
  • Solved
Last Blank cell
Replies
7
Views
214
Alex Blakenburg
A
P
  • Question
Condition that meets one condition
2
Replies
10
Views
463
arthurbr
arthurbr

Forum statistics

Threads
1,214,999
Messages
6,122,645
Members
449,093
Latest member
Ahmad123098

Share this page

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
Back
Top