I have been racking my brain with this and I just can't seem to figure it 
out. Here is what I am trying to do. I have 3 tables named Registration, 
Events, and Customer.

I want to produce a page that lists all events and the number of attendees 
and who is attending each event. I assume that the only way to do this is 
by using an inner join and I am having a hard time grasping it. 

My registration page has three fields (customernumber,eventid,eventdate)
My events page has five fields 
(eventid,eventdate,place,description,numberofattendees)
and my customer table has three fields that I need to use 
(firstname,middlename,lastname,customernumber)

I am at a complete loss as to how I would use the inner join to link them 
and then spit out each record on the page.

Anyone have any idea?

Thanks in advance

Steph

Stephanie,

I would change the way your tables are designed (based on the limited
infomation that you have presented). I also suggest you pick up a book on
database design - one that explains ER (Entity-Relationship) modelling, and
also Normalisation.

Part of the problem you will have writing any sort of query that outputs
data is that the SQL language simply isn't designed to cope with improperly
defined tables.

I would suggest that you have three tables:
TABLE Customers
    CustomerNumber (PK)
    FirstName
    MiddleName
    Lastname

TABLE Events
    EventID (PK)
    EventDate
    Place
    Description

TABLE CustomersEvents (or Registration)
    EventID (PK) (FK references Events)
    CustomerNumber (PK) (FK references Customers)

Ditch the "number of attendees" field in the Events table - you can get this
by doing a COUNT() - see below.
Also, ditch the EventDate field in the CustomerEvents/Registration table
since you can get this by doing a JOIN on the events table.

So, to get a list of all events, and the number of attendees, you do:

SELECT
    COUNT(b.EventID),
    a.[Description]
FROM
    Events AS a
LEFT JOIN
    Registrations AS b
ON
    a.EventID = b.EventID
GROUP BY
    a.[Description]


To get a list of all attendees names for all events, you'd do:

SELECT
    a.[Description],
    c.FirstName,
    c.MiddleName,
    c.LastName
FROM
    Events AS a
INNER JOIN
    Registrations AS b
ON
    a.EventID = b.EventID
    (
    INNER JOIN
        Customers AS c
    ON
        b.CustomerNumber = c.CustomerNumber
    )

(Note the use of brackets - only Access needs these in the JOIN clauses -
I'm not sure I'm put them in the correct place - you can use the Access
query builder to get the brackets in the correct places)


Cheers
Ken




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Stephanie" <stephanieweigel@y...>
To: "Access ASP" <access_asp@p...>
Sent: Saturday, April 20, 2002 9:05 PM
Subject: [access_asp] Inner Join


: I have been racking my brain with this and I just can't seem to figure it
: out. Here is what I am trying to do. I have 3 tables named Registration,
: Events, and Customer.
:
: I want to produce a page that lists all events and the number of attendees
: and who is attending each event. I assume that the only way to do this is
: by using an inner join and I am having a hard time grasping it.
:
: My registration page has three fields (customernumber,eventid,eventdate)
: My events page has five fields
: (eventid,eventdate,place,description,numberofattendees)
: and my customer table has three fields that I need to use
: (firstname,middlename,lastname,customernumber)
:
: I am at a complete loss as to how I would use the inner join to link them
: and then spit out each record on the page.
:
: Anyone have any idea?
:
: Thanks in advance
:
: Steph
: