Multiplier's and counts

[mazzarin]

Hello all,

With the help of John Vinson's post in
http://groups.google.com/group/microsoft.public.access/browse_frm/thread/61eb474b08438275?hl=en
I was able to make way on a query I'm trying to build.

I imported an excel worksheet (~20,000 entries) into Access, and I am
trying to extract some information. First I wanted to find duplicates,
and rank by duplicate appearance. I have gotten that part.

In my db, I have a column 'Qty' that specifies the number of times the
event occurred. At the moment, I only have the number of times the
event appeared in the db. I would like to factor in the number of times
now.

An example of my db would look like:

ID Event# Qty
1 A 10
2 B 100
3 C 30
4 A 60
5 A 50

So in my initial query, I get A with a CountofID value of 3, B with 1,
C with 1.
However, A actually needs to be 120, B needs to be 100, C needs to be
30.

My idea is to make a second query based off the first query, where one
item would be the Event# from Query1 (because that cut out all the
duplicates), and the second would be the all the number of occurences
of that event added up.

I'm not sure how to go about it. Any help is appreciated!

 

[Guest]

You can update all the Qty values to the sum of the values for each Event#.
In Access you can't do this with a subquery, however, as you would in other
flavours of SQL because any query with an SQL aggregate function such as SUM
is read only in Access. The way to do it, therefore, is with the VBA DSum
function, like so:

UPDATE YourTable
SET Qty = DSum("Qty", "YourTable", "[Event#] = " & [Event#]);

This assumes that Event# is in fact a number data type, but if it is text as
shown in your example you'd wrap it in quotes:

UPDATE YourTable
SET Qty = DSum("Qty", "YourTable", "[Event#] = '" & [Event#] & "'");

This will give you a table with duplicates of the Event#/Qty values. To
remove the duplicates you can use a DELETE query with a subquery, but you can
also simply append the data into an empty new table in which the Event#
column is indexed uniquely (no duplicates). Only one row per Event# will be
appended due to the unique indexing on this column.

Be sure the data is backed up before undertaking any large scale update
operation like this!

Ken Sheridan
Stafford, England

 

[mazzarin]

Thanks for your assistance.

In this case, it would always update the table only? I could not output
the values to a query?

 

[mazzarin]

Simple punctuation error on my part... finally got it. Really simple:

SELECT Event, SUM(Qty) AS ["Total Qty"]
FROM Sheet1
GROUP BY Event;

Thanks for looking!