Database design help for multiple contacts

[newbieX]

I have a spreadsheet that has multiple contact information. Many of the contact names and addresses are repeats, but not always. Some of the contacts show up for multiple records. Sometimes the contact names are the same but the address and phone numbers differ. Not all contact information is complete for all columns.

I created two tables. A projects table and a contacts table. The contacts table has each individual contact information as a separate record with ContactID as the PK. The projects table contact information was substituted for 4 fields (HomeID, MailID,BillingID, CompanyID) and the ContactID value placed in its corresponding field. I thought this would work but I am having problems creating a query in Access 2007 to mimic the spreadsheet. Because the Contacts table references 4 fields in the table, my query is only finding those records where all 4 of the fields match.

Any suggestions as to what I am doing wrong and how to approach this issue better?Attachment 14697

Sample of data reflecting what I am trying to accomplish attached.

[orange]

Access and Excel are two different software packages with different purposes and object models.
Using Access to mimic a spreadsheet seems a non-starter.

You may want to read up on database design.

If you have the same contacts with various addresses, isn't that a sign of "out of date" data?

[June7]

Possibly two different contacts named John Doe?

The query needs to include the ContactsTable 4 times, each joining to one of the ID fields in Projects Table. The join type for each must be 'Include all records from Projects Table ...' (NOT an inner join).

Post your query for analysis if still need help.

[newbieX]

I tried that. The problem is I don't know how to set up a query that displays the following question in a form and/or report given the design of my database:

Display the names, addresses and phone numbers for the four contacts for project X.

Attached is a mdb that shows what I am trying to do.

e.g. Using attached sample database, if user selects Project 2, my form or report might look like this:

Project 2
Home Contact
Frank Smith
456 First St
555-5555

Mail Contact
John Doe
123 Main St
555-1234

Billing Contact
None listed

Company Contact
None Listed

It seems like this should be simple but I just can't seem to get the information to display correctly. I'm about ready to go back to having this stuff in spreadsheet format since normalizing it is proving to be too complex for me.

[orange]

Read and work through this tutorial to get your database designed to match your business requirements.

[June7]

The query is fine. Just drag Names, Addresses, Phones fields from the 4 Contacts tables to the grid. Can give them alias names.

Maybe some data entry errors. For instance, new Projects table has Mary Jackson in HomeID for ProjectID 6 instead of George Jones as shown in the original table. That's the only one I can see.

[newbieX]

Yes. That did the trick. Now that you've explained it, it makes perfect sense. I can't believe I did not see it. Arrrggghhh. Sometimes you just get stuck on an idea and can't see beyond it. I was pretty sure I set up the tables right but couldn't figure out how to display the data.

Also, never thought to create an alias. For those of you who don't know how to do this (I didn't) Right click on the field name in the query design grid. Open the properties box and enter the alias under Caption.

Whoo hoo.

Thanks.

The query is fine. Just drag Names, Addresses, Phones fields from the 4 Contacts tables to the grid. Can give them alias names.

Maybe some data entry errors. For instance, new Projects table has Mary Jackson in HomeID for ProjectID 6 instead of George Jones as shown in the original table. That's the only one I can see.