AutoNumber Issues or Primary Key Issues, I'm not sure

  • Thread starter Thread starter jdub via AccessMonster.com
  • Start date Start date
J

jdub via AccessMonster.com

Hey guys,

I need some help very bad. I'm creating a registration database for a
convention. I've created 4 tables. There setup is below

AttendeeTable
AttendeeID - Autonumber - Primary Key
Name - Text
Address - Text
Phone - Text

MembershipTAble
MembershipID - Autonumber - Primary Key
LocalChapter - Text
Life Member - YES/NO

RegistrationFeeTable
FeeID - Autonumber - Primary Key
PaymentForm - Text
DatePaymentReceived - Date/Time
PaidAmount - Currency

EventListTable
EventID - Autonumber - Primary Key
AwardsTickets - Text
Breakfast Tickets - Text
LuauPartyTickets - Text
Golf- Text

I then created an AddAttendee justified form through the wizard that would
allow the user to enter all of the registration information on one form. I'm
able to enter all of the info but when I exit and return and add a new user,
I'm missing information from 2 of the tables. I view the tables in Datasheet
view and see that the Autonumbers for the data that is missing from the form
do not match up with the Datasheet views of the Tables that are in the form.
If I enter a second registrant save and exit. When I return, the first
registrant has information from him and info from the second registrant.

What am I doing wrong? Do I need to setup a relationship between these
tables? It appears that there is a relationship between two

Thanks,
JW
 
You have not related the tables in any way. The way I see it you need to
change to something like this --

MembershipTAble
MembershipID - Autonumber - Primary Key
LName – Text
FName – Text
MI - Text
Address1 – Text
Address2 – Text
City – Text
State – Text
Zip – Text
Phone - Text
LocalChapter - Text
Life Member - YES/NO

EventListTable
EventID - Autonumber - Primary Key
Tickets – Text (pick from Awards, Breakfast, Luau Party, Golf, Dues, etc.
Price - Currency

RegistrationFeeTable
FeeID - Autonumber - Primary Key
EventID - number - foreign Key
PaymentForm – Text (pick from Traveler’s check, Personal check, Visa, MC,
AMEX, Discover, Cash, etc.)
PaymentID – Text (Check #, Card last 4, Cash, etc.)
DatePaymentReceived - Date/Time
PaidAmount - Currency

AttendeeTable
MembershipID - number - foreign Key
EventID - number - foreign Key

MembershipID related 1-many events in the AttendeeTable.EventID
AttendeeTable.EventID related 1-many RegistrationFeeTable.FeeID
 
Thanks very much.

I've made the appropriate changes. A couple of questions though.

Do I need to make tables for the fields that have choices (i.e. Tickets,
Paymentform, etc.)? Also, I'm receiving and error that one table is not
related in any way. Should the EventList Table have a relationship?

Thanks,

JW

KARL said:
You have not related the tables in any way. The way I see it you need to
change to something like this --

MembershipTAble
MembershipID - Autonumber - Primary Key
LName – Text
FName – Text
MI - Text
Address1 – Text
Address2 – Text
City – Text
State – Text
Zip – Text
Phone - Text
LocalChapter - Text
Life Member - YES/NO

EventListTable
EventID - Autonumber - Primary Key
Tickets – Text (pick from Awards, Breakfast, Luau Party, Golf, Dues, etc.
Price - Currency

RegistrationFeeTable
FeeID - Autonumber - Primary Key
EventID - number - foreign Key
PaymentForm – Text (pick from Traveler’s check, Personal check, Visa, MC,
AMEX, Discover, Cash, etc.)
PaymentID – Text (Check #, Card last 4, Cash, etc.)
DatePaymentReceived - Date/Time
PaidAmount - Currency

AttendeeTable
MembershipID - number - foreign Key
EventID - number - foreign Key

MembershipID related 1-many events in the AttendeeTable.EventID
AttendeeTable.EventID related 1-many RegistrationFeeTable.FeeID
Hey guys,
Click to expand...
[quoted text clipped - 39 lines]
Thanks,
JW
Click to expand...
Click to expand...
 
Do I need to make tables for the fields that have choices (i.e. Tickets,
Paymentform, etc.)?
Not necessary, use a listbox on your data entery form.
It would help if you stated the exact error message and what you were doing
just before the message. Us folks can not see your screen.
It is related to the junction table - AttendeeTable


jdub via AccessMonster.com said:
Thanks very much.

I've made the appropriate changes. A couple of questions though.

Do I need to make tables for the fields that have choices (i.e. Tickets,
Paymentform, etc.)? Also, I'm receiving and error that one table is not
related in any way. Should the EventList Table have a relationship?

Thanks,

JW

KARL said:
You have not related the tables in any way. The way I see it you need to
change to something like this --

MembershipTAble
MembershipID - Autonumber - Primary Key
LName – Text
FName – Text
MI - Text
Address1 – Text
Address2 – Text
City – Text
State – Text
Zip – Text
Phone - Text
LocalChapter - Text
Life Member - YES/NO

EventListTable
EventID - Autonumber - Primary Key
Tickets – Text (pick from Awards, Breakfast, Luau Party, Golf, Dues, etc.
Price - Currency

RegistrationFeeTable
FeeID - Autonumber - Primary Key
EventID - number - foreign Key
PaymentForm – Text (pick from Traveler’s check, Personal check, Visa, MC,
AMEX, Discover, Cash, etc.)
PaymentID – Text (Check #, Card last 4, Cash, etc.)
DatePaymentReceived - Date/Time
PaidAmount - Currency

AttendeeTable
MembershipID - number - foreign Key
EventID - number - foreign Key

MembershipID related 1-many events in the AttendeeTable.EventID
AttendeeTable.EventID related 1-many RegistrationFeeTable.FeeID
Hey guys,
Click to expand...
[quoted text clipped - 39 lines]
Thanks,
JW
Click to expand...
Click to expand...
Click to expand...
 
Thanks Karl,

This is what I have.

Membership Table -----> Attendee Table (MembershipTable.MembershipID to
AttendeeTable.AttendeeID)
RegistrationFeeTable ------>AttendeeeTable(RegistrationFeeTable.FeeID to
AttendeeTable.EventID)
EventListTable -------> AttendeeTable (EventlistTable.EventID to
AttendeeTable.EventID)

Is this okay?

JW
 
Back
Top