Access 2007 Help Please

Forums › IT Pro and developers › Access 2007 Help Please

Windkyle

4 posts

Wannabe Geek

# 31377 15-Mar-2009 23:16

I have a database with:
Tables
Contacts {Name, Address, Phone Number, ID} etc
Jobs {Job Number, Name, Adress Phone Number, Job Description} etc

How do I create a JOB form where when I enter the name which is linked to the contacts and the address from that name comes up Automatically.

bitumen

10 posts

Wannabe Geek

# 201371 16-Mar-2009 09:20

Tables
Contacts {Name, Address, Phone Number, ID} etc
Jobs {Job Number, Name, Address Phone Number, Job Description}

You need to add a relationship between the tables.
[Contacts] 1 or many [Jobs]
[Jobs] must have 1 [Contact]
(not going to deal with many to many possibility)

Hence:
Contacts {Name, Address, Phone Number, ID} etc
Jobs {Contact_ID, Job Number, Name, Address Phone Number, Job Description}

Contact_ID in the Jobs table is the foreign Key to the Contact table ID field

Sample query (not tested or checked)

Select [Contacts].[Address]
From [Contacts],[Jobs]
Where [Contacts].[ID]=[Jobs].[Contact_ID] And [Jobs].[Name] = input

Hope this helps

edit reason: formating

Ragnor

8034 posts

Uber Geek

Trusted

# 201419 16-Mar-2009 14:26

I would imagine you will invariably have a situation where a contact has many jobs and a job can have more that one contact.

For many to many relationships you just need a joining table like JobContact, eg:

Contact [ContactID, Name, Address, PhoneNumber, WhenCreated, WhenModified, CreatedBy, ModifiedBy]

Job [JobID, Name, Adress Phone Number, Job Description, WhenCreated, WhenModified, CreatedBy, ModifiedBy]

JobContact [JobID, ContactID, WhenCreated, WhenModified, CreatedBy, ModifiedBy]

A couple of coding conventions that I normally stick to are: don't make table names pural, add some basic logging fields so you can at least tell who last modified it and who created it (Created/Modified).

bitumen

10 posts

Wannabe Geek

# 201422 16-Mar-2009 15:07

Windkyle You seem to be learning many different technologies and languages and concepts,

I recomment you look at FunctionX website as they have alot (450mb) of tutorials that i think will assist you.

Good luck in the learning!

stevonz

397 posts

Ultimate Geek

Trusted

# 201426 16-Mar-2009 15:17

Also bytes.com (formerly know as thescripts.com) is another forum with a wealth of information & quick/ friendly advice relating to MS Access/ Excel queries...

Cheers, Stevo

Windkyle

4 posts

Wannabe Geek

# 201514 16-Mar-2009 22:14

Yep definately trying to learn as much as possible. Ive recently started my own little PC repair business, im pretty good at Diagnosing problems with hardware and software. This is my website let my know what you guys think, and what do you think it would cost to build it. www.pcworld.co.nz. I really appreciate your guys help