Hi

I need help with my database.

Im uploading my database and if you open frmCompanyDetails you will see what Im trying to do, I had some help through this forum and also privately from a member of UtterAccess. The problem is I am teaching myself access as I go along and it is difficult - but Im not going to let that stop me.

The main form of my database seems perfect at this stage and so is the subform for contacts - ideas for these areas of my database will be welcome.

But now I come to the ADDRESSES part of my form (its also a subform) and thats where I get stuck.
For any of my companies I will have at least 3 addresses. A physical address, a postal address and a delivery address.
Natrually the physical address will be a street address and the postal address and delivery address could be either a street address or a P.O. Box address.
A customer may also make a request that a certain address be delivered to - this needs to be kept on record since I may need to deliver to that address
There also maybe another physical addressagain.
I was thinking that a field (to be later placed on a form) needs to be created for every part of the address (i.e. The 4 lines of a usual address and only those relevant displayed. Difficult to describe since I want to separate the lines of each address and dont particularly want to display "empty" columns. The unheard of may happen - what if a certain address is 5 or 6 lines long, what do I do?
Examples of address (Physical)
123 Saxon Road, Heights, 7300 - 3 lines
1542 Plain Street, Bromley Manor, Johannesburg, 2456 - 4 lines
Office 2A, 15 Walker Avenue, Meadowfields. Apple Valley, 1246 - 5 lines

Example of postal address
P.O. Box 175, Cape Town, 8000

There also maybe another physical address - perhaps the company has another branch, also there maybe another postal address because the company may have another mailing address.
Now a delivery address and a postal address can be the same as the physical address, things could be delivered to a P.O. Box and a special delivery address could be specified which could be a physical address or a postal address.

I have been trying to rack my brain on how to do this in access but I cannot.

I can see the need for three separate tables for such a datamodel . . .

tblCompany: coID (pk-autonum); coName (store JUST the company name and a unique id)

tblCoAddresses: addrID (pk - autonum); coID (fk); coAddress; coCity; coState; coPostalNum (zip); AddrTypeID (fk)

tblAddrType: AddrTypeID (pk - autonum); addrType ("delivery", "mail"; "POBox"; etc)

Relate tblCompany.coID to tblCoAddresses (one-to-many)

Relate tblAddrType.AddrTypeID to tblAddresses.AddrTypeID (many-to-many - OR just refer to the tblAddresses.AddrTypeID with tblAddrType.AddrTypeID via form combobox control)

You may even want to add a 'tblContacts' with appropriate break-down of similar variants as above.

For more information on Normalization and other Access tutorials/references, see:

• Crystal's ACCESS Basics for Programming
• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry's FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Database Answers (free data models)
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables
• Sharing/Splitting a Database
• Access Tutorial
• Access VBA Tutorial

hope this helps

Please ignore my previous post.

Ive edited my postcode table with queries (thanx to Doug Steele) - I now have the table as I want it.

What I am trying to do now is to design an address subform for my main form.

To save future headaches Ive decided to have a 5 field address for each customer. Each customer will have at least one physical, one postal and one delivery address, The lines of the address (wether postal or physical or delivery) will be AddressLine1, AddressLine2, Place, City and Code. I have tried something and I hope Im on the right track. I want the City and the Code fields of my address subform automatically populated when I select a place in the Place combobox - I have alot of customers who dont know the postcode of the area they live in. Also I would like to sort the Address Type combobox entries on my subform so that all the delivery addresses of a particular customer are grouped togther as are the physical addresses and the postal addresses. It will not be neccessary to have a setup as I have with the contacts.

I am also going to add a textbox or something for comments - mainly to handle "unusual addresses" (those with lots of lines in them) Suggestions on this will be very helpful.

My database is attached.

When I am going through the combobox wizard what does it means when it says something like "store that value in this column"

Hi Nick

See the modified Db attached

When you select your Town using the Combobox Wizard you can select the "Place, City & Code"

When it comes to the section "store that value in this column" you would select the field where you need
to store the value of the Town Selected - in this case "TownID". This means you will be storing
the Primary Key of the Town and NOT the Name of the Town.

To display the values of the City and Code I used an Unbound Combobox and used the following to obtain the related Data.

I named the Combobox "cboPlace"

=[cboPlace].Column(2)
=[cboPlace].Column(3)

Combobox's Column Numbers begin at 0

Therefore your Combobox allows you to select TownID - Place - City - Code

TownID = Column 0
Place = Column 1
City = Column 2
Code = Column 3

Click to view attachment

This is perfect Mike. However on second thoughts Im questioning myself on whether a setup like I have for contacts would be the way to go. Also have you got any suggestions on how I can handle (unusual addresses) - Im thinking a testbox on the address tab of the form. Good idea or not?