basic help to start me to get my queries running

[emandel]

Ok in a a nutshell here is my database
I have a table called 'contacts' that has basic contact and demographics about my members

I have a table called 'events' that lists the events and info about them (date, teacher, location, price)

I have a junction table called attendance where it records who came to what.

Here is what I need.

I want the quary to tell me who the most active people that are coming to events are, what they are coming to, how many events they are coming to, Idealy I would want to run this quary once a month or so, and have it answer these questions. Also Idealy I would want a form to say

Are you looking for the top (x) people?
What age range? (all or specific)
What marital status?
What date range are you looking at activity?



I don't really know where to start.

[emandel]

Anyone care to help me?

[MMcCarthy]

Ok in a a nutshell here is my database
I have a table called 'contacts' that has basic contact and demographics about my members

I have a table called 'events' that lists the events and info about them (date, teacher, location, price)

I have a junction table called attendance where it records who came to what.

Here is what I need.

I want the quary to tell me who the most active people that are coming to events are, what they are coming to, how many events they are coming to, Idealy I would want to run this quary once a month or so, and have it answer these questions. Also Idealy I would want a form to say

Are you looking for the top (x) people?
What age range? (all or specific)
What marital status?
What date range are you looking at activity?



I don't really know where to start.

You start by designing a query to return all records. When you have this query designed post it with the four questions above and we will tell you how it needs to be adapted for each.

Mary

[emandel]

OK here is the query that brings up all records:

SELECT Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], Events.Name, Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, Events.Price

Here are my questions for starters

1) get it to show me only the top 50 attenders in the past month/3 months/year
2) get it to show me how many events they attended in that time period and have that broken down by lets say the [events.type]

[emandel]

any help here?

[MMcCarthy]

OK here is the query that brings up all records:

SELECT Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], Events.Name, Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, Events.Price

Here are my questions for starters

1) get it to show me only the top 50 attenders in the past month/3 months/year
2) get it to show me how many events they attended in that time period and have that broken down by lets say the [events.type]

SELECT TOP 50 Participants.[First Name], Participants.[Last Name],
First question - What are you basing the top 50 on, a count of the number of events attended or what?
Participants.Birthdate, Participants.[Marital Status], Events.Name,
Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator,
Events.Price
FROM Participants INNER JOIN Events
ON Participants.?????????? = Events.??????????????
Second question - What is the field in common with the two tables
WHERE Month(Events.Date) = IIf(Month(Date())=1,12,Month(Date())-1)
This will return records for the previous calendar month

This may need further order by or group by statements but I will need the answer to the above questions first.

Mary

[emandel]

Your first question - yes I want the 50 most active participants the ones that have come to the most events.

There is a third table that is not currently in this query that links the two tables together. Basicaly it lists which participants came to which events.

[MMcCarthy]

Your first question - yes I want the 50 most active participants the ones that have come to the most events.

There is a third table that is not currently in this query that links the two tables together. Basicaly it lists which participants came to which events.

Ok, I need to know the two fields from the third table that link to each of the other tables.

Also we will need to build two queries. One to get the count of the number of events a participant attends and display the top 50. Then the other query can return the related fields for each event the participant attended.

Mary

[emandel]

Events.[ID] and participants.[ID]

in other words both are called "ID" one is from the events table and one is from in the participants table.

In the "atendance" table (which is the junction table) those fields are named "event" and "atendee"

[MMcCarthy]

Events.[ID] and participants.[ID]

in other words both are called "ID" one is from the events table and one is from in the participants table.

In the "atendance" table (which is the junction table) those fields are named "event" and "atendee"

The first query will be:

SELECT TOP 50 participants.[ID], Participants.[First Name], 
Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], 
Count(atendance.event) As NumEventsAttended
FROM Participants INNER JOIN atendance
ON Participants.[ID] = atendance.atendee
GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status]
ORDER BY Count(atendance.event);
 

This will return the top 50 participants ...for now I'm naming the query 'qryTop50Participants'

The next query will be:

SELECT qryTop50Participants.[First Name], qryTop50Participants.[Last Name], qryTop50Participants.Birthdate, qryTop50Participants.[Marital Status], Events.Name,  
 Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, 
 Events.Price
 FROM ((qryTop50Participants INNER JOIN atendance
 ON qryTop50Participants.[ID] = atendance.atendee)
INNER JOIN Events ON atendance.event = Events.[ID])
 WHERE Month(Events.Date) = IIf(Month(Date())=1,12,Month(Date())-1)
 

This will return records for the previous calendar month

[NeoPa]

Anyone care to help me?

any help here?

Emandel,
We all need to bump threads from time to time when questions are still outstanding and a number of days have passed since any activity.
Bumping a thread after little more than an hour is not something I'm happy with and is likely to win you no friends among the experts on this site.
Please wait at least a whole day before bumping threads in future.

MODERATOR.

[emandel]

First of all,
Thank you for the pointer, I certainly don't want to be percieved as pushy. Now I know the guidelines.

Mary:

The first query will be:

SELECT TOP 50 participants.[ID], Participants.[First Name], 
Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], 
Count(atendance.event) As NumEventsAttended
FROM Participants INNER JOIN atendance
ON Participants.[ID] = atendance.atendee
GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status]
ORDER BY Count(atendance.event);
 

This will return the top 50 participants ...for now I'm naming the query 'qryTop50Participants'

The next query will be:

SELECT qryTop50Participants.[First Name], qryTop50Participants.[Last Name], qryTop50Participants.Birthdate, qryTop50Participants.[Marital Status], Events.Name,  
 Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, 
 Events.Price
 FROM ((qryTop50Participants INNER JOIN atendance
 ON qryTop50Participants.[ID] = atendance.atendee)
INNER JOIN Events ON atendance.event = Events.[ID])
 WHERE Month(Events.Date) = IIf(Month(Date())=1,12,Month(Date())-1)
 

This will return records for the previous calendar month

The first query is working but for the second query what I want is the top 50 people for the past month (or another date that I will put into the parametar). What the query is giving me is the 50 most overall attenders (since the beginning of the recorded events) and than from those 50 giving me when they came over the past month.

I think that the second query needs to come first. i.e. who came at all in the past month, and from those give the me the 50 top attenders and how many times they came.
for that first (or second the way you had it) query I want to have a parameter where when I run it I tell it what the date range is: so it asks me "For the past how many days?"

[MMcCarthy]

The first query is working but for the second query what I want is the top 50 people for the past month (or another date that I will put into the parametar). What the query is giving me is the 50 most overall attenders (since the beginning of the recorded events) and than from those 50 giving me when they came over the past month.

I think that the second query needs to come first. i.e. who came at all in the past month, and from those give the me the 50 top attenders and how many times they came.
for that first (or second the way you had it) query I want to have a parameter where when I run it I tell it what the date range is: so it asks me "For the past how many days?"

SELECT TOP 50 participants.[ID], Participants.[First Name],
Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status],
Count(atendance.event) As NumEventsAttended
FROM ((Participants INNER JOIN atendance
ON Participants.[ID] = atendance.atendee)
INNER JOIN Events ON atendance.event = Events.[ID])
WHERE Events.Date BETWEEN Date()-[For the past how many days?] AND Date()
GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status];
 

This should return Top 50 participants and a count of how many events each attended ...for now I'm naming the query 'qryEventsAttended'

The next query will be:

SELECT qryEventsAttended.NumEventsAttended, qryEventsAttended.[First Name], qryEventsAttended.[Last Name], qryEventsAttended.Birthdate, qryEventsAttended.[Marital Status], Events.Name, Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, Events.Price 
FROM ((qryEventsAttended INNER JOIN atendance
ON qryEventsAttended.[ID] = atendance.atendee)
INNER JOIN Events ON atendance.event = Events.[ID]);
 

Mary