Access Primany Key question

[GenocideOwl]

so I want my database to be naturally ordered by company then product. Problem is companies own multiple products and products can be owned by different companies. Thus I can't set the primary key to Company like I want to.
How do I make my table ordanize by company?

[szlamany]

Company should be the first column in this table.

Product should be the second column.

The primary key should be both of these two columns - a two segment primary key.

Is that what you mean?

[GenocideOwl]

how do you do a mutli-segment primary key?

[szlamany]

Sorry - I'm MS SQL SERVER - not ACCESS - so I can't specifically answer that question - are you using a GUI or wizard to create the table?

[GenocideOwl]

I use the "Create table is Design View" option

[techgnome]

Select the two Columns and hit the Primary Key Button.

Having Said that.... would it make more sence to normalize it a little and have a Company table and then a Product table? .... eh... but then Company name would get duplicated in the Product table.... is there other info about the Company that you track other than the name, like addresses or phone numbers? If so, then it might make sense to use two tables, other wise, just use one, and a composite key.

-tg

[szlamany]

TG - I was just about to PM you - thought you might know the ACCESS answer to this question...

I assumed that there was only a CUSTOMER ID in this table - I agree with you that a separate table for customer demographic data makes sense if it's needed.

[GenocideOwl]

actually they will be organized by three total things, Company - Product - Barge product came on
Barge is always unique, but I want Company then product to be the main way of sorting the list

[techgnome]

If all you are looking for is sorting, then add a ORDER BY clause to the end of your SELECTS. Don't rely on keys to do that for you automagicaly, you'll never get the result you want. KEys don't sort.... only CLUSTERED INDEXes will sort the data, but that's only because it physically moves the data into order... but again, that's a poor way to default the sort order. Your best action for sorting is to add a ORDER BY clause.

-tg

[GenocideOwl]

I don't know a lot about access...so how do you add a "by order" to it?
I have all the various keys set to index

[techgnome]

It would be part of your SELECT statement, not part of the table design.

How are you selecting your data to display?

-tg

[GenocideOwl]

I don't know what you mean by "select statement" really