How do I count records from a Statement, however if the same record is multiple, then count them together

want to show a count of some records

however some records values will be the same, so I want these counted as multiple.

eg
SYD 1
MEL 2
DPS 4

not
SYD
MEL
DPS
MEL
DPS
DPS
DPS

is there a way of showing this?
Graemewebber4technologiesAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
YourNumbercolumn is the column from your SQL statement which would contain the value you want to show. It would be either "eng_dest", or the COUNT(eng_dest) column. Since I assume it's the COUNT column, you might change the SELECT statement to this:

SELECT enq_dest, COUNT(enq_dest) AS DestCount FROM enq WHERE enq_date etc etc

Then refer to it like this:

kpIsDest("DestCount")
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
using a query

select [FieldName], Count([FieldName])
from tableName
group by  [FieldName]
0
 
Graemewebber4technologiesAuthor Commented:
Thanks for that I have correct my query so that it works

Set KPIsDest=oConn.Execute("SELECT enq_dest,COUNT(enq_dest) FROM enq WHERE enq_date BETWEEN #"&SOMonth&"# AND #"&EOMonth&"# GROUP BY enq_dest")                                          

How do I show the count of each record?

eg
SYD 1
MEL 2
DPS 4
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Wilder1626Commented:
You can also try:
select distinct  [FieldName], Count([FieldName]) from tableName
group by  [FieldName]
0
 
Wilder1626Commented:
Or
select count(distinct FieldName), FieldName
from tableName
group by  FieldName
0
 
hnasrCommented:
Since this is just a simple function in access, I might suggest using the query wizard to find Duplicates.
Then you may modify the query by removing the >1 criteria, either in design view or sql view.

This helps in improving your access programming. You may modify the query to get other functions, like Sum or Count, by replqacing Count or adding to it.
0
 
Graemewebber4technologiesAuthor Commented:
Hey all, just need the code to show the records and how many of each
0
 
Rey Obrero (Capricorn1)Commented:
where do you want to show the records?
0
 
Graemewebber4technologiesAuthor Commented:
As a list

eg
SYD 1
MEL 2
DPS 4

is this what you meant?
0
 
Rey Obrero (Capricorn1)Commented:
As a list , where ?
0
 
Graemewebber4technologiesAuthor Commented:
Sorry I do not get what you mean by 'where' ?

Here is the code:

<%
Set KPIsDest=oConn.Execute("SELECT enq_dest,COUNT(enq_dest) FROM enq WHERE enq_date BETWEEN #"&SOMonth&"# AND #"&EOMonth&"# GROUP BY enq_dest")							
%>
	<tr>
		<td colspan=8 align=center>Destinations</td>
		<td colspan=2>&nbsp;</td>
	</tr>
	<tr>
		<td height=20 width=20% colspan=2>0%</td>
		<td height=20 width=20% colspan=2>25%</td>
		<td height=20 width=20% colspan=2>50%</td>
		<td height=20 width=20% colspan=2>75%</td>
		<td height=20 width=20% colspan=2>100%</td>
	</tr>
<%
	Do While Not KPIsDest.eof
%>
	<tr>
		<td colspan=8>
			<table width=100% border=0>
				<tr>
					<td height=20 bgcolor=black width=<%=FormatNumber(((!!!!!!!/EnqCount)*100),2)%>%></td>
					<td height=20>&nbsp;</td>
				 </tr>
			</table>
		</td>
		<td><%=KPIsDest("enq_dest")%></td>
		<td><%=FormatNumber(((!!!!!!!!!/EnqCount)*100),2)%>% Destinations Strikerate</td>
	</tr>
<%
	KPIsDest.MoveNext 
		LOOP
	KPIsDest.Close
%>

Open in new window


where i have the !!!!! is where i need the sum count total of the destinations
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you want to show this on a web page. That's what Rey was asking. We don't know what programming environment you're using, so asking us "how to  show a list" doesn't make much sense to us.

You'd have to loop through the recordset and add rows to your table:

Do Until kpIsDest.EOF
<tr>
            <td colspan=8>
                  <table width=100% border=0>
                        <tr>
                              <td height=20 bgcolor=black width=<%=FormatNumber(((kpIsDest("YourNumberColumn")/EnqCount)*100),2)%>%></td>
                              <td height=20>&nbsp;</td>
                         </tr>
                  </table>
            </td>
            <td><%=KPIsDest("enq_dest")%></td>
            <td><%=FormatNumber(((kpIsDest("YourNumberColumn")/EnqCount)*100),2)%>% Destinations Strikerate</td>
      </tr>
Loop
0
 
Graemewebber4technologiesAuthor Commented:
hello and thanks for clarifying...

KPIsDest("YourNumberColumn") being what ?

what do you need from me to figure out which column i am looking for?
0
 
Graemewebber4technologiesAuthor Commented:
Cheers all for your help!

works a treat!
0
All Courses

From novice to tech pro — start learning today.