Newbie question 1

[Shilohcity]

Hi there

This is a very basic question that has been niggling away at me for ages and I just canst seem to find the answer.

I have tables setup for a shopping cart site as follows
iteminfo
itemorder
custdetails

At present I have no relationships between them at all and am handling queries through multiple connections using asp. Many of my fields double up for instance orderid appears in both iteminfo and orderinfo.

Now I know this is a REALLY BAD way to do this but I was rushed when I set it up. I know there is a way to relate fields and have data appear in more than one table but how?

I have messed around with this a little but am still confused. A few pointers would be most appreciated.


Thanks

Justin.X-) "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[RickSpr]

First you should decide on a primary key for each table. This is a field or set of fields whose combined values are unique among all the rows in the table. Chances are, "orderid" is probably unique in the orderinfo table, so it could be a primary key.

Sometimes there is more than one field or set of fields that could serve as a primary key. In orderinfo, for example, you might have the customer's order number, which (we may presume) is also unique. When you have two or more "candidate" keys, you choose the one that you expect to use most often to look up information. "Most often" should include consideration not only of how your users will look up information, but how your application will do so "under the covers", using some of those duplicated fields like, say, item number.

Other times there is no combination of fields that is unique, or there is such a combination but it contains data you often wouldn't know when you want to look something up. In such cases, it's usually wise to add an Autonumber field to the table, and make that the primary key.

Once you've created primary keys, you will probably notice that the key fields tend to be the ones you've duplicated in other tables. These duplicates of the keys are called "foreign keys", and your important relationships are based on them.

With your keys, set up, choose Tools>Relationships from the menu. This opens the Relationships window. Some of your tables may already appear in the window, some may not; you can add additional tables with the Relationships>Show Tables menu item, or by the toolbar button. (Note: You have to show the tables here to create relationships, but if you later delete the tables from this window, the relationships aren't deleted, just hidden. This often confuses new users.)

To create a Relationship, you drag (a copy of) a field from one table and drop it on a field in another table. Access displays a line between the fields and opens the Relationships editor dialog. At this point, you can just click the OK button to create the relationship. Before you do, though, you usually would want to check the Enforce Referential Integrity option. If you do, you're telling Access to ensure that every foreign key is either null, or contains a valid value, that is, one that exists in the table where these fields are the primary key. This ensures that the fields you use to refer between tables are always valid, no matter what you do on a form or in code; if you try to delete an order from the orderinfo table, and it still has items in the orderitem table, Access will either give you an error message or delete the related orderitems for you. (Which one it does is determined by the other check boxes: Cascade Update and Cascade Delete; see the help file for more information.)

For tables that have primary keys composed of more than one field, you can still create relationships. You just highlight all the key fields together, then drag them to the other table. If they have the same names in both tables, Access will match up the fields by name. Otherwise, you'll have to indicate how to match fields in the list of fields in the Relationships editor dialog.

After you've created these relationships in the Relationships window, Access will "know" how the tables are related. From then on, if you put two tables in a query grid that have a relationship defined, Access will draw the join line for you. Not only does that save you a step, but it also gives Access hints about the most efficient way to wearch for data in the tables, which can speed up your application. Plus, you'll get the relational integrity options that keep your tables synchronized, if you chose them when creating the relationships.

One more note: The relationships I've been talking about so far are based on keys. You can also define relationships that are not based on keys, both in the Relationships window and in query grids, by drawing relationship lines (also called "join lines&quot between matching fields. You can't get relational integrity on such relationships (because they aren't unique in either table), but you can still use these relationships to find matching columns in two or more tables. Queries based on such relationships often won't execute as fast as key-based relationships, and they're not used as often, but occasionally they're indispensable.

Well, that's a primer on how to set up the relationships. There's still the topic of how to use them, but you're better picking that up by building queries and forms. Don't worry--it's not that hard to figure out. Rick Sprague

 

[Shilohcity]

Hi there Rick

Thanks for you in depth answer it covers some of the main areas that have been bugging me. I also have just discovered that in setting up my relationships and queries I was receiving an error in Join field type message due to my allocation of autonumber foreign keys as text and not numbers.

I think that now I have fixed this (the smallest things are the most annoying) it should be easier going. My problem before was not being able to form relationships but rather just not being able to do anything with them due to the join error.

Well thanks very much for your help so far it is one of the most informative answers I have had in my time using Tek-Tips.com

Cheers

Justin.X-) "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz