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

 

[Pat Hartman]

1. ID is a poor choice for a column name, especially since you are creating relationships. Common practice is to use the table name - the primary key of the person table would be PersonID and the primary key of the address table would be AddressID. That way, the primary key and the foreign key will have the same name in most cases. That makes it easier to see relationships without having to study the relationship diagram.
2. Special characters such as the dash should not be used in object names (nor should spaces).
3. The wedding date is an attribute of the wedding entity rather than an attribute of the participants and so is the venue and other stuff. That means that you should add a relation table:
tblWedding:
HusbandID (pk 1, foreign key to tblPerson)
WifeID (pk 1, foreign key to tblPerson)
WeddingDate

Of course if you want to be politically correct, you could use other names for the two participants.

 

[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.

 

[Pat Hartman]

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

 

[Pat Hartman]

I merged the two threads. Dinus, please don't create new threads for the same question.

 

[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??

 

[Pat Hartman]

You need to go back to the original structure. There should only be ONE person table. The wedding table is a relation table that relates two records in the same table instead of relating records in different tables.

When you create a query, you need to add the person table to the QBE grid TWICE. Draw a join line to spouse1 from the first instance of the person table and draw a join line to spouse2 from the second instance of the person table.

 

[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)