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

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

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

Sorry, Group By > Sum

mmcdonal

can you please explain that more?
thank you

MalekMac

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

Sorry, Count for Location, and Sum for Zones

mmcdonal

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

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

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