Summing Entries in Queries

M

mikediner

I am a new user of Access and have a question (I apologize if it'
already been asked):

For various reasons, I had to create one master table (Code) and relat
the rest of the tables to Code with relationships (I can't just use on
big table).

Let's say I have three tables then: Code, Name, Value (Name having tw
columns: one "code" which relates to Code and the other "name," an
Value functions similarly). I want to summarize Value according t
Name. But my problem comes if I have two entries for one code.

For example in Name I have:

Code Name
1 John
1 John

and in Value I have:

Code Value
1 2
1 3


Then when I use a query, I'd like to get:

Name Value

John 5

But I get:

Name Value

John 2
John 3
John 2
John 3


Any way to sum the values only when a name is repeated? And I'll wan
to do this with numbers and strings (so I'll need to do more than jus
sum numbers).

I'm assuming the answer is in SQL, but I have no experience with it.

Thanks so much for reading this and helping me out!

Michae
 
M

Michel Walsh

Hi,


Remove the extra record in Name.

From your actual query, click on the summation button on the toolbar (the
one with the Capital Sigma, a M rotated by 90 degree), a new line, Total,
will appear in the grid. Keep the proposed GROUP BY under Name.Name, change
the GROUP BY to SUM under Value.Value.

That's the only 2 modifications (removing the extra record, and making a
total query rather than a standard SELECT query).


Hoping it may help,
Vanderghast, Access MVP
 
Top