By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

basic help to start me to get my queries running

emandel
P: 65
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.
Jan 2 '07 #1
Share this Question
Share on Google+
12 Replies


emandel
P: 65
Anyone care to help me?
Jan 2 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 2 '07 #3

emandel
P: 65
OK here is the query that brings up all records:

Expand|Select|Wrap|Line Numbers
  1. 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]
Jan 2 '07 #4

emandel
P: 65
any help here?
Jan 2 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
OK here is the query that brings up all records:

Expand|Select|Wrap|Line Numbers
  1. 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
Jan 2 '07 #6

emandel
P: 65
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.
Jan 2 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 2 '07 #8

emandel
P: 65
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"
Jan 2 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 50 participants.[ID], Participants.[First Name], 
  2. Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], 
  3. Count(atendance.event) As NumEventsAttended
  4. FROM Participants INNER JOIN atendance
  5. ON Participants.[ID] = atendance.atendee
  6. GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status]
  7. ORDER BY Count(atendance.event);
  8.  
This will return the top 50 participants ...for now I'm naming the query 'qryTop50Participants'

The next query will be:

Expand|Select|Wrap|Line Numbers
  1. SELECT qryTop50Participants.[First Name], qryTop50Participants.[Last Name], qryTop50Participants.Birthdate, qryTop50Participants.[Marital Status], Events.Name,  
  2.  Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, 
  3.  Events.Price
  4.  FROM ((qryTop50Participants INNER JOIN atendance
  5.  ON qryTop50Participants.[ID] = atendance.atendee)
  6. INNER JOIN Events ON atendance.event = Events.[ID])
  7.  WHERE Month(Events.Date) = IIf(Month(Date())=1,12,Month(Date())-1)
  8.  
This will return records for the previous calendar month
Jan 2 '07 #10

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 2 '07 #11

emandel
P: 65
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 50 participants.[ID], Participants.[First Name], 
  2. Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status], 
  3. Count(atendance.event) As NumEventsAttended
  4. FROM Participants INNER JOIN atendance
  5. ON Participants.[ID] = atendance.atendee
  6. GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status]
  7. ORDER BY Count(atendance.event);
  8.  
This will return the top 50 participants ...for now I'm naming the query 'qryTop50Participants'

The next query will be:

Expand|Select|Wrap|Line Numbers
  1. SELECT qryTop50Participants.[First Name], qryTop50Participants.[Last Name], qryTop50Participants.Birthdate, qryTop50Participants.[Marital Status], Events.Name,  
  2.  Events.Date, Events.time, Events.Type, Events.Subject, Events.Educator, 
  3.  Events.Price
  4.  FROM ((qryTop50Participants INNER JOIN atendance
  5.  ON qryTop50Participants.[ID] = atendance.atendee)
  6. INNER JOIN Events ON atendance.event = Events.[ID])
  7.  WHERE Month(Events.Date) = IIf(Month(Date())=1,12,Month(Date())-1)
  8.  
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?"
Jan 2 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534

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?"
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 50 participants.[ID], Participants.[First Name],
  2. Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status],
  3. Count(atendance.event) As NumEventsAttended
  4. FROM ((Participants INNER JOIN atendance
  5. ON Participants.[ID] = atendance.atendee)
  6. INNER JOIN Events ON atendance.event = Events.[ID])
  7. WHERE Events.Date BETWEEN Date()-[For the past how many days?] AND Date()
  8. GROUP BY participants.[ID], Participants.[First Name], Participants.[Last Name], Participants.Birthdate, Participants.[Marital Status];
  9.  
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:

Expand|Select|Wrap|Line Numbers
  1. 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 
  2. FROM ((qryEventsAttended INNER JOIN atendance
  3. ON qryEventsAttended.[ID] = atendance.atendee)
  4. INNER JOIN Events ON atendance.event = Events.[ID]);
  5.  
Mary
Jan 3 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.