Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2005, 03:20 PM
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to malekmac
Default counting unique items

I would really appreciate a help in this topic.
Here is the deal;

item location zones
1 4 8
2 5 5
7 9 1
1 8 5


I wanna write a query that counts the enumber of times item x visited different locations and different zones. So basically, I wanna have a similar result to the above query;

item location zones
1 2(num of locs) 2(num of zons)
.....
ANybody may help?
Thank you

 
Old November 25th, 2005, 08:29 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

In your query designer, select View>Totals and then in each of the two fields where you want your totals, select Group By>Count

HTH


mmcdonal
 
Old November 29th, 2005, 12:55 PM
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to malekmac
Default

the problem is, its not counting the unique number of records that correspond to a certain recrod. Let me try to explain this further, here is an example

item location zones
1 4 8
2 5 5
7 9 1
1 8 5
1 4 8

item location zones
1 2(num of locs) 2(num of zons)

For some reason, when i ask to count, access is returning 3 for location and 3 for zone. Its not counting the unique locations and zones for item 1.
That my problem. What do you think?

MalekMac
 
Old November 29th, 2005, 01:40 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, Group By > Sum

mmcdonal
 
Old November 29th, 2005, 02:15 PM
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to malekmac
Default

can you please explain that more?
thank you

MalekMac
 
Old November 29th, 2005, 02:28 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sure:

This Query:

SELECT tblItems.Item, Sum(tblItems.Location) AS SumOfLocation, Sum(tblItems.Zones) AS SumOfZones
FROM tblItems
GROUP BY tblItems.Item;

yields this dataset:

Item SumOfLocation SumOfZones
1 16 21
2 5 5
7 9 1


Just create your query, select View > Totals, then in the Total: row that shows up in the designer, select Sum for Location, and Sum for Zones.

HTH




mmcdonal
 
Old November 29th, 2005, 02:29 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, Count for Location, and Sum for Zones

mmcdonal
 
Old November 29th, 2005, 02:31 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Count for Location and Sum for Zones (leave Item at GroupBy) yields:

Item CountOfLocation SumOfZones
1 3 21
2 1 5
7 1 1

mmcdonal
 
Old November 29th, 2005, 02:35 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

In your post, you say it is counting 3 for Location, and 3 for Zones. That is a correct count. The number of times "1" goes to a location and zone is 3 each. Is that not what you wanted? If you want the total value of either of these columns, then you want a Sum. I am not sure what you want now.

Using your sample dataset, what do you want the query result to look like?

(Count)
1, 3, 3
2, 1, 1
7, 1, 1

Or

(Sum)
1, 16, 21
2, 5, 5
7, 9, 1

Or some other combination of counts or sums?


mmcdonal
 
Old November 29th, 2005, 02:43 PM
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to malekmac
Default

Actually, I dont want the total count, I want the unique number of zones and unique number of locations the item went to. So if item 1 goes to zone 1 twice, i wanna see something like item 1 went to 1 unique zone.

MalekMac





Similar Threads
Thread Thread Starter Forum Replies Last Post
counting unique entries Figgis Access 5 November 23rd, 2005 07:01 PM
Counting unique orders rlull SQL Server 2000 1 November 12th, 2005 12:34 AM
Counting unique record dlamarche Access 5 March 22nd, 2005 08:51 AM
displaying 6 items only having 20 items Lakshmi KS VB Components 1 February 17th, 2004 10:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.