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

[irethedo]

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:

[pre]
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 [/pre]

Can this be done easily with a query?

Thanks

 

[dhookom]

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.

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

 

[MajP]

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

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

[tt]Name	      TotalPurchases
Jim Jones	2
Larry Lewis	3
Sam Smith	3[/tt]

Then you can join this to your table

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

 

[MajP]

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

 

[MajP]

And the final method using a dcount function

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

 

[irethedo]

Thank you Duane and MajP for great responses.

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

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?

[pre]
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[/pre]

Thanks again

 

[MajP]

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

 

[dhookom]

Try:

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

 

[irethedo]

Duane & MajP-

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

You guys are awesome