Help with a simple sorting problem...please

S

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
hi,
i'm VERY new to access and i have this sheet which i need to sort...
i have a column with identifiers (they are repeated many times) and in the second column, i have a type.
what i need is to say: for each of those individual first column's identifiers, i want to add up all the same types in column 2!
So if in column 1 i have:

a
a
b
b
b
c

and in col. 2 i have

cat
cat
dog
cat
cat
bird

i want to keep track of the number of cats/dogs/birds each a, b, or c has.
in this case it would be presented as:

a cat 2
b dog 1
b dog 2
c bird 1

is this doable in access? if so, would you pleaes help me?
(y)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
The required result you have given doesn't seem to match the data given.

Apart from that you could use a query like this:

SELECT Table1.Identifier, Table1.Type, Count(Table1.Type) AS CountOfType
FROM Table1
GROUP BY Table1.Identifier, Table1.Type;

Which gives, based on your data the following result:

<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 9"><link rel=File-List href="./Types_files/filelist.xml"><link rel=Edit-Time-Data href="./Types_files/editdata.mso"><link rel=OLE-Object-Data href="./Types_files/oledata.mso"><style></style></head><body link=blue vlink=purple><table x:str border=0 cellpadding=0 cellspacing=0 width=158 style='border-collapse: collapse;table-layout:fixed;width:118pt'> <col width=56 style='width:42pt'> <col width=31 style='mso-width-source:userset;mso-width-alt:1322;width:23pt'> <col width=71 style='mso-width-source:userset;mso-width-alt:3029;width:53pt'> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 width=56 style='height:11.25pt;width:42pt'>Identifier</td> <td class=xl29 width=31 style='border-left:none;width:23pt'>Type</td> <td class=xl29 width=71 style='border-left:none;width:53pt'>CountOfType</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=56 style='height:11.25pt;width:42pt'>a</td> <td class=xl30 width=31 style='border-left:none;width:23pt'>cat</td> <td class=xl31 width=71 style='border-left:none;width:53pt' x:num>2</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=56 style='height:11.25pt;border-top:none; width:42pt'>b</td> <td class=xl30 width=31 style='border-top:none;border-left:none;width:23pt'>cat</td> <td class=xl31 width=71 style='border-top:none;border-left:none;width:53pt' x:num>2</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=56 style='height:11.25pt;border-top:none; width:42pt'>b</td> <td class=xl30 width=31 style='border-top:none;border-left:none;width:23pt'>dog</td> <td class=xl31 width=71 style='border-top:none;border-left:none;width:53pt' x:num>1</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=56 style='height:11.25pt;border-top:none; width:42pt'>c</td> <td class=xl30 width=31 style='border-top:none;border-left:none;width:23pt'>bird</td> <td class=xl31 width=71 style='border-top:none;border-left:none;width:53pt' x:num>1</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=56 style='width:42pt'></td> <td width=31 style='width:23pt'></td> <td width=71 style='width:53pt'></td> </tr> <![endif]></table></body></html>
 
Upvote 0
i'm sorry...you're right about my mis-type!
sorry about that.

but how would i apply your code exactly. i.e where would i write it in? how would i execute? i'm very new to access.

thanks for your help! :)
 
Upvote 0
if access is anything like excel, (which i'm assuming it is), i'm going to go into a VB editor, create a new module, and then insert the code, fix it up and then run it.
But as i did that, it gave me a an error regarding "TABLE1"!
why is that?
also, how would i name the macro?
 
Upvote 0
Nope, Access is nothing like Excel -- any resemblance is coincidental :wink:

Assuming that your table is called Table1, use Norie's query as is.
If not, replace Table1 with your table's name and the field names with the actual names that you use. Then do this:
Select the Query tab in your database, and create a new query.
Click OK to close the dialog that asks you to add tables.
You'll see a blank grid. The left-most item on the bottom toolbar is a datasheet icon.
Click the drop-down arrow next to it, and select SQL View (it may just be SQL).
You'll go to a blank data entry screen with some text. REPLACE the text with the (modified) SQL that Norie gave you (paste it in), and then go to Datasheet view to see the result.
If you like it, save the query and give it a name like qryAnimalCount.
Next time you need to see the information, double-click the query icon.
It will always give you the most up-to-date version of your data.

Denis
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question
How to structure a variable product rating database
Replies
5
Views
833
mdmilner
M
D
  • Question
Sequence custom query
Replies
8
Views
603
petertenthije
P
J
  • Solved
Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?
2
Replies
15
Views
823
Micron
M
K
  • Question
Help repairing a Query Expression show records by a specified year
Replies
1
Views
467
Micron
M
H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
601
heathball
H

Forum statistics

Threads
1,221,834
Messages
6,162,268
Members
451,758
Latest member
lmcquade91

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