auto insert data

[paulsimo]

Could anybody please help with a problem I have regarding extracting data from 2 tables.
I am trying to build a database that will allow users to complete their details to enter an event. The way I would like it to work is for the user to register their personal details on the database including a username and password (the username would be the primary key). After they have completed the initial registration, on subsequent visits to the database they can simply enter their username and password to log in.
From the log in area, the user has a choice of events to enter, they select the appropriate event and then complete some additional information through a form.
The problem I have is how can I join the registration details table to the events table without the user having to complete the username details in the events table. What I would like is for the username field to be automatically inserted (to enable me to relate the 2 tables) into the events table. Is this possible and how would I go about setting this up.

The registration table would have the following fields:-
first name
surname
username (primary key)
password
email address

The events table would have the following fields:-
event name (primary key)
date of event
arrival time
special requirements
username (auto inserted from users log in details)

The resulting query would need to include all of the above in both tables and would show which events a user has entered. A user would not be allowed to enter the same event more than once.

Many thanks in anticipation.

 

[jzwp22]

Since many users can register for an event (I assume), that describes a one (event) to many (users) relationship. If my assumption is correct, then you cannot have the username in the event table because that will allow only 1 person to signup for the event. Additionally, I assume that a user can sign up for multiple events which would be another one-to-many relationship. When you have 2 one-to-many relationships between 2 tables you actually have a many-to-many relationship. To handle many-to-many relationships, you need a junction table to join the various users to each event.

Also, it is generally recommended that the primary key should not have any significance to the user. It is a way for the database to uniquely identify each record in a table. Also, it is best not to have spaces and special characters in your field and table names

The registration table would have the following fields:-
-pkUserID primary key, autonumber
-firstname
-surname
-username
-password
-email address

The events table would have the following fields:-
-pkEventID primary key autonumber
-eventname
-dateofevent



tblEventUsers
-pkEventUserID primary key, autonumber
-fkEventID foreign key to events table, long number data type
-fkUserID foreign key to user table, long number data type

I was not sure about the arrivaltime and specialrequirements fields. If these two fields pertain to the user relative to the event then the two fields would go in tblEventUsers.

To represent this in forms, you would have a main for based on the user table and then a subform within that form based on the tblEventUsers. Within that subform, you would have a combo box that is based on the event table where the user can select the event(s) they want.