Relational Databases??

[Muthafishy]

Hi Everyone,

This is probably one of those question types that always come up, but if you feel like being charatible today then please help me out.

I have list of postcodes and suburbs imported from an excel table. And I have a form that I use to enter name, address info about doctors on my mailing list based on one main table.

I want to be able to enter a suburb and have the postcode found and entered from the imported table automatically into the form and underlying table.

The main table is called Contact, the Suburbs and Postcodes table is called that, and the fields are Suburbs, Suburbs and Postcodes, Postcodes.

Could you teach me how to link the tables so that the form enters the postcode as per the suburb entered? Could you please use the labels too so I don't get really confused?

I also use a business name (you know, Blah Blah medical centre) field which always has the same address and telephone numbers. I've been just copying the info down, within the table after I've entered the info for the first doctor.

Where I am at is that I know the names of the basic things like forms, combo boxes, relationships etc. but not much more than that. I designed the form and made the combo boxes lookup the main table for frequently duplicate fields.

Thank you,
Tim
P.S. Please ask any questions that might help you help! Thx again

 

[ecniv]

I'd go n google some tutorials on dbs, connections and searching. I assume you are using Access and this sounds like an assignment, in which case you should be discovering how to rather than us telling you... you won't learn if we tell you

Have a browse online and try to make up the tables and form. When you get stuck and you have code, and aplan of what you are trying to do, post up on here (this thread or a new one) and people will help you.

Question: What happens if there are more suburbs for the postcode? Does it pick the first, list all, offer a choice to the user???


Vince

 

[Muthafishy]

Hi,

I'm ah a psychologist of 24 and I'm really trying to do up a database, rather than a student. I really don't know how to link these tables but I've heard a lot of talk around it.

I really need to know exactly how to do it because I have no tutor.

As soon as that's done I'll be out of your hair. I have no aspirations to be a computer programmer.

Please help me anyway

P.S. I am using Access XP and I have all the possible postcodes in Australia, if not I'm perfectly capable of updating the table, or downloading the new list from Australia Post.

I just simply don't understand how this thing works! Should I have a suburb ID to link the tables or what? All the explanations I have found have involved a Business or Product ID etc, to link that one to this one. I've just got suburbs, postcodes and a unique case id for the first table, Contact. Meanwhile, I want to enter a suburb name and get a postcode to fill in on the form so I don't have to look it up all the time (particularly as I have a table with them in from the Aussie post office).

Cheers
Tim

 

[monkeytunes]

Hey Muthafishy,

You sound like you're on the right track, and willing to do the legwork, but let us clear up a couple of terminology/general practice things for you. Hopefully this will get you on your way!

First, the term "linking" tables usually refers to having a seperate Front-end and Backend. Your tables would be on the backend, probably stored on a server, and the queries, forms and reports would be on front-ends, distributed to every user's individual computer. I'm assuming that's NOT what you're looking for here.

What you're looking for is "Relational Integrity", and it will allow you to have those postcode and suburb fields auto-populate, but we've got to make some changes to your table structure first.

(By the way, it might make it easier if you start using naming conventions, as outlined here . It will make it easier down the line to fgure out if you're looking at a table, or a query, or whatever if you make changes to your forms and reports.)

What we need to solidify is "Normalization". Do a search for it on these forums for more info - it's probably the #1 topic that gets discussed, and, oddly, you'll never see it in Access help. What you're trying to do now is store duplicate data across tables, which is a big no-no. (Also, if a suburb can have more than one postcode, or a postcode can have more than one suburb, you'll have to take that into account).

Your main table is your Contact table, and your Postcode table is a reference table to that. Since many contacts might reside in one postcode/suburb, it doesn't make sense to enter that data over and over; it's inefficient, and one data-entry error will screw up your searches. Instead, make an autonumber ID field in the Postcode table (don't just call it "ID" like the default, though; change it to PostcodeID to make it clear), make a corresponding field in the Contact table (call it "RefPostcodeID" or "tblPostcodeID", something so that you know it is referencing the ID field of another table), and THAT'S where your repeating values would go.

In other words, say PostcodeID value "5" meant the suburb was "HappyTown" and the Postcode was "12345". If you have 40 contacts that live in HappyTown, you'd have to type in HappyTown 40 times, and 12345 40 times, and any mistakes would throw off your data. With an ID field, you just type in "5" 40 times, and you have a RELATION.

To make Access recognize this, you have to draw a relation between the two fields across those tables. For this, look for a button called "Relationships" in your Main Database Window. This will bring up the relationships window. Make sure your tables are showing, and then connect the two fields (PostcodeID and it's relation in table Contacts.) (By the way, your autonumber PostcodeID should be a Primary Key. The lookup reference in the Contacts table is now considered a "Foreign Key".). Click the "Enforce Relational Integrity" options, and you should be good to go.

Let us know if you get all that to work, and then you might want to check out my explanation of dropdown-combo boxes over here .

Hope that helps!