HELP! Multi field count (1 Viewer)

rmanan

New member
Joined
Aug 13, 2013
Messages
1
Please help me in counting number of appearing records show in query

Name Drinks Date purchased Count
John Milk 8/3/13 1
->Mike Beer 8/4/13 2
John Beer 8/4/13 1
Peter Wine 8/6/13 1
-> Mike Beer 8/6/13 2

Counting conditions: counting will continue if NAME and DRINKS are the same
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,533
Welcome aboard:)
Aggregate (totals) queries work by summarizing data. In your example if you wanted to count the drinks by name, you would eliminate the date field.
Select name, drink, count(*) as DrinkCount
From yourtable
Group by name, drink;

If you want to include the date, then you probably want to do the counting in a report.
 

madefemere

Registered User
Joined
Aug 3, 2013
Messages
80
Hello,

You can use the following query to get your result :
Code:
SELECT (SELECT Count(DatePurchased) 
  FROM Table1 as T2
  WHERE T2.DatePurchased<T1.DatePurchased AND T2.Name=T1.Name AND T2.Drinks=T1.Drinks)+1 AS Count, *
FROM Table1 AS T1
ORDER BY T1.Name, T1.DatePurchased;
Good continuation
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom