INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Is there an easy way to add the sum of a certain type of record to each record of that type?
2

Is there an easy way to add the sum of a certain type of record to each record of that type?

Is there an easy way to add the sum of a certain type of record to each record of that type?

(OP)
I have a table that contains a list of customers and the date that they purchased something
and I need a way to tally up the number of dates that they made purchases and
I would like to add that total to each record of that type such as:

Name		Date		Total Purchases
Sam Smith	1/30/2017		3
Jim Jones	3/1/2017		2
Sam Smith	3/1/2017		3
Jim Jones	4/17/2017		2
Larry Lewis	4/19/2017		3
Larry Lewis	5/16/2017		3
Larry Lewis	7/07/2017		3
Sam Smith	8/24/2017		3	 

Can this be done easily with a query?

Thanks

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

This should work. I hope you really don't have fields named "Name" and "Date" since they are both reserved words and can create issues.

CODE --> sql

SELECT [NameField], [DateField], 
(SELECT Count(*) 
 FROM [YourTableName] A 
 WHERE A.[NameField] = [YourTableName].[NameField]) as [Total Purchases]
FROM [YourTableName]; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

You can do it with a sub query, a calculated field using dsum, or two queries joined together.

The easiest is two queries.

1) build the query Total Purchases. You can do this in the query designer without knowing SQL, but here is the SQL.

TotalPurchases

CODE -->

SELECT Data.Name, 
 Count(Data.Date) AS TotalPurchases
FROM 
 Data
GROUP BY Data.Name; 

CODE -->

Name	      TotalPurchases
Jim Jones	2
Larry Lewis	3
Sam Smith	3 

Then you can join this to your table

CODE -->

SELECT 
 Data.Name, 
 Data.Date, 
 TotalPurchases.[TotalPurchases]
FROM 
 Data 
INNER JOIN 
 TotalPurchases ON Data.Name = TotalPurchases.Name; 

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

Duane beat me, while I was typing. His is the subquery method.

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

And the final method using a dcount function

Quote:


SELECT
Data.Name,
Data.Date,
DCount("Name","data","Name = '" & [Name] & "'") AS TotalPurchases
FROM Data;

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

(OP)
Thank you Duane and MajP for great responses.

I chose this one as it was the first one that I saw:

CODE -->

SELECT [NameField], [DateField], 
(SELECT Count(*) 
 FROM [YourTableName] A 
 WHERE A.[NameField] = [YourTableName].[NameField]) as [Total Purchases]
FROM [YourTableName]; 


To further complicate this a little bit, is it possible to create another column to list the
number of orders that happened on each date so that it looks like the example below?

Name		Date		Total Purchases     Order per Date
Sam Smith	1/30/2017		3                 1
Jim Jones	3/1/2017		2                 2
Sam Smith	3/1/2017		3                 2
Jim Jones	7/07/2017		2                 3
Larry Lewis	4/19/2017		3                 1
Larry Lewis	5/16/2017		3                 1
Larry Lewis	7/07/2017		3                 3
Sam Smith	7/07/2017		3                 3 

Thanks again

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

Is that a field in your original table or is there a child table? How and where is that information stored?

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

Try:

CODE --> SQL

SELECT [NameField], [DateField], 
(SELECT Count(*) 
 FROM [YourTableName] A 
 WHERE A.[NameField] = [YourTableName].[NameField]) as [Total Purchases],
(SELECT Count(*) 
 FROM [YourTableName] B 
 WHERE B.[NameField] = [YourTableName].[NameField]
 AND B.[DateField] = [YourTableName].[DateField]) as [Order Per Date]
FROM [YourTableName]; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Is there an easy way to add the sum of a certain type of record to each record of that type?

(OP)
Duane & MajP-

A big thank you to you for your help with this!

You guys are awesome

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close