how to let ppl get maried ?!?

[Dinus]

With help off this forum I have succesfully created my first database . I made a address / birthday database.

But now I want to show a wedding day between 2 ppl from the same table and column. Can anyone point me in the good direction how I can do this the best way?

My database is like this -->

tb:Address:
ID (AutoNumber)
address (text)
zip_code (text)
city (text)
telephone (text)
country (text)

tberson
ID (AutoNumber)
addressID (Number)
first_name (text)
surname (text)
mobile (text)
e-mail (text)
date-of-birth (date)

There is already a one-to-many link from tb:Address ID to tberson addressID.

So I want to connect 2 persons with one wedding date.

 

[Sergeant]

1. Add a field to your people table called like "spouseID" or something.
2. Add another field called "weddingDate" or something.
3. Try this out on the query grid by placing people table on query grid and then placing people table AGAIN on query grid. Make a relationship between people:spouseID and people[2]:ID

Read up on "self-join".

...you could also do this with a junction table containing ID, weddingDate, spouseID.

HTH

 

[Dinus]

Wow, tnx. I'm just a beginner in Access but those are some powerfull tips
I will try it this way.

 

[Dinus]

My database is like this now -->

tb:Address:
AddressID (AutoNumber)
Address (Text)
ZipCode (Text)
City (Text)
Phone (Text)
Country (Text)

tberson
PersonID (AutoNumber)
AddressID (Number)
FirstName (Text)
Insertion (Text)
Surname (Text)
Mobile (Text)
EMail (Text)
DateOfBirth (Date/Time)

tb:Wedding
WeddingID (AutoNumber)
Spouse1 (Text?!?)
Spouse2 (Text?!?)
WeddingDate (Date/Time)

There is already a one-to-many link from tb:Address AddressID to tberson AddressID.

But now I'm a kind of stuck. Do I also have to put a column WeddingID (Number) in tberson?? And sry , but i dont know how to create a foreign key .

 

[Dinus]

Hmmmm.... there aren't a lot of ppl looking at this thread anymore. Ill make another one. Look at How to let ppl get maried part 2

 

[Dinus]

How to let ppl get married!! (part 2)

My database is like this now -->

tb:Address:
AddressID (AutoNumber)
Address (Text)
ZipCode (Text)
City (Text)
Phone (Text)
Country (Text)

tberson
PersonID (AutoNumber)
AddressID (Number)
FirstName (Text)
Insertion (Text)
Surname (Text)
Mobile (Text)
EMail (Text)
DateOfBirth (Date/Time)

tb:Wedding
WeddingID (AutoNumber)
Spouse1 (Text?!?)
Spouse2 (Text?!?)
WeddingDate (Date/Time)

There is already a one-to-many link from tb:Address AddressID to tberson AddressID.

But now I'm a kind of stuck. Do I also have to put a column WeddingID (Number) in tberson?? And sry , but i dont know how to create a foreign key . And can I do this with a form and subform??

 

[neileg]

Your Spouse1 and 2 fields should hold the PersonID of the two partners (so should be numbers, not text).

I would create a form bound to tb:Wedding with two combo boxes for Spouse1 and Spouse2. These combos should be based on a query that pulls the names and PersonID from tberson and stores the PersonID in tb:Wedding. The combo box wizard will take you through this.

Note: Since you don't hold the sex of the person, each combo will return all names. Of course this will be required if you are recording same sex marriages, too.

 

[ColinEssex]

Why start a new thread on the same topic? - if people don't know Pat Hartman gave you a reply on your other thread, then they will miss out on her wisdom and advice

Col

 

[Dinus]

Hmm... sry. Didn't think about that

 

[ScottGem]

But now I'm a kind of stuck. Do I also have to put a column WeddingID (Number) in tberson?? And sry , but i dont know how to create a foreign key .

First a foreign key is a field in a related table that holds the value of the primary key of the related record. You already created foreign keys. For example the AddressID in your person tabvle is a FK.

No you don't need WeddingID in the Person table. Your Wedding table has FKs for the 2 people getting married. you link them through that table.

 

[Dinus]

I wont do it anymore tnx

 

[Dinus]

I have read all your tips. Tnx guys. But im not finished with my database so im sure that i have some questions later

 

[Dinus]

I hope that you guys don't get sick of my questions . But I still can't get it right.

I tried:

tb:Wedding
WeddingID (AutoNumber)
SpouseID1 (Number)
SpouseID2 (Number)
WeddingDate (Date/Time)

But this way I cant get the relationsships right for the Querie.

Then i tried:

tb:Spouse1
WeddingID (AutoNumber)
PersonID (Number)
WeddingDate (Date/Time)

tb:Spouse2
WeddingID (AutoNumber)
PersonID (Number)

And this way I cant get the Querie working.

I'm stuck on this wedding thing. If someone got some tips that I can understand , they are most welcome.

 

[Dinus]

OK, I got this running.

I used:

tb:Spouse1
WeddingID (AutoNumber)
PersonID (Number)
WeddingDate (Date/Time)

tb:Spouse2
WeddingID (AutoNumber)
PersonID (Number)

I used this with a form.
But i use a dropdown menu for the PersonID. Isn't there a way that i can use a dropdown menu for the tberson FirstName and that it stores the tberson PersonID to in Spouse1??

 

[bseche]

Can you tell me how you setup your access database with the birthdate. I'm trying to create an access database that will have user name and their birthdate and i want a report that will give me a monthly birthday list. (assuming this is how you did it)

 

[ScottGem]

Birthdate should be stored as their actual date of birth. To get a list of birthdays in the current month, there are a few different ways. The way I usually do it is by adding a column to my query with the expression:

CurrDOB: DateSerial(Year(Date()),Month([DOB]),Day([DOB]))

Then set the criteria for that column to:

BETWEEN DateSerial(Year(Date()),Month(Date()),1) AND DateSerial(Year(Date(),Month(Date())+1,0)

 

[bseche]

birthdate field problem

Hi Scott
I understand that, quick question how would i setup the field for the birthdate i tried just changing the imput mask to be 11/11 its forcing a 2005 at the end. i just wanted to enter the birthday month and day. (would that work)
thanks in advance

 

[ScottGem]

As I said you enter the birthdate as the correct date. You can't just enter month and year in a date field, it has to be a valid date.