form design, query design, db design

[verdes]

I'm not sure which area to choose for my problem.

I’m using Access 2007. I have 4 main tables :

Entry-authorization-tbl
entryID
entryaddressID
entrycontactID

Address-tbl
addressID
addressStnumber
addressStname
addressStprefix
addressStsuffix

Contact-tbl
contactID
contactName
contactaddressID

workDone-tbl
workID
workaddressID
workDate
workempID
workDesc

All addresses are in the address table. An Entry Authorization address may or may not be the same as a Contact address. A Work address may or may not be the same as either of the other addresses.

I need an Entry Authorization form that will allow the user to select an address record for the entry address and the ability to select an address to use for the contact address. Is that just a combo box that lists the address fields concatenated? If it is, would I use vba to populate individual address fields on the form?

The form I have now will allow you to add both addresses on a new form but it only lets me edit 1 address on an existing record. It says I can’t add a value to a blank field on 1 side of an outer join. And….I’m not able to load a new form with any address via open args. My current form uses a query that contains a query and a table (to avoid the anbiguious join error).

I made an entry query that selects all fields from the entry , address, and contact table. Then I made another query that started with the entry query I just told you about and used the address table records associated with the contact Id.

I need to be able add a brand new address from the form or edit an existing address. Additionally, I’d like to be able to select an existing address to use on the form.

I’m sure I have a design issue – but I’m at a lost on how to fix it……

 

[verdes]

i agree with your point on the addresses. It's part of the reason why I'm struggling with this design.

These clients don't care about people, they only care about premise addresses - they can't do their work until they get authorization to enter the grounds of the address. They don't even care if it's the property owner that gives the authorization.

The last time I posted information about a table the respondent blasted me about simply usind "ID" and not qualifying the key..........

Everybody has a preference, and I'm OK with that........can you help me with my real problem or point me to an article or website that can?

Las