Primary Key accross 2 fields

[Neil]

Hello All,

I have a question with regards to Primary Keys. I have a table that has the
following fields:

lngMachineID (Primary Key Field)
lngDescriptionID (Primary Key Field)
dblPrice
blnIsStandard
blnIsRequired

This table is a Junction table used in a Many-To-Many relationship. I dont
think i need to provide any more information as my question is this:

For a unique record to be entered it needs to have a MachineID and a
DescriptionID (as with all other Junction tables), does Access treat these 2
values as one seeing as though both these fields make up the primary key? If
so, how does it and how can i use it (in queries for example). At the
moment, if i was to do a query on this table i would have to check to see if
lngMachine = some value and lngDescriptionID = another value. Could this be
made simpler by checking to see if Primary Key = some value?

Hope my question makes sense and thanks for your help in advance.

Neil.

 

[Jeff Boyce]

Neil

As far as I know, when you use more than one field as a "primary key", you
have more than one field! Besides, if you are using IDs from the respective
"many" tables (Machine, Description), who would ever know the id values to
ask for a concatenated ID-ID?

I am a little concerned about a table named "Description". Are you saying
that a Machine can have many descriptions, and a single Description can have
many machines? Could you provide an example?

 

[Van T. Dinh]

Sorry, no.

Think of the Multi-Field PK is an unique index combining the 2 Fields.
However, to specify a unique Record, you still to specify the values of the
*TWO* Fields.

You can use a surrogate PK Field, e.g. AutoNumber Field and also the Unique
Index (but not PrimaryKey) on your 2 component Fields.

 

[Neil]

Hello Jeff,

The Description holds all descriptions of available options for forklift
trucks. This is linked to a table named category (one to many relationship -
one catagory has many descriptions). An example of a way that these 2 items
can be linked together is as follows:

Categery = Engine
Linked Description = Perkins
Linked Description = Cummins
Linked Description = Mazda

I am trying to make a 'quote builder' program if you like that has every
option available for a forklift truck (Options or Items that i have named
them are built up of a Category name and then many individual descriptions).
You select the truck you want to make a quote for and this returns all
available items for this truck. From there, you can select the items that
you want and then a price could be calculated from the items selected. My
description table holds information on the category, as in my example above.
My Item table holds pricing information on each description for each truck
(As more than 1 truck can have the same option description). The description
table is for category items and not machine descriptions which, i think, is
what you were thinking this inforamtion would be used for. Example as
follows:

Machine = 1 Ton forklift - In Machine table
Description = Perkins - In Description table
Item Price = £1,000.00 - In Item table

Machine = 2 Ton Forklift
Description = Perkins
Item Price = £1,500.00

There are a few other fields in the item table which will control how an
item behaves (ie is a standard item for example).

In the end, when I have all the available options and all machines entered
with the correct prices etc, i have another 2 tables which is a log of quote
numbers and customer details. A linked one-to-many table holds the item
ID(s). This would be so that I could 'retrieve' a quote by filtering all
available options with the options selected in this table. For example, i
have a table which holds a quote number then the table that is linked to
this holds all the item ID's (the machine and description values). If
someone wanted to look at an old quote, i would then query all the available
items but only include the records that were in the table of ID's on the
quote side. This was my reason for the question in the first place. I
figured a query using the IN statement would run quicker if it only had to
check in one field. However, it would not be too much of a problem if it was
in 2 fields, it would just take longer to process. How long this would take
is yet to be seen...

If you have any comments on how to make this work a lot better, then please
let me know. I think this way is do-able but as i havnt tried and tested it
yet there may be a stumbling block lurking ahead...

TIA,

Neil.

 

[Neil]

Thanks Van,

I thought that this may be the case. I was thinking of a way to store both
the ID numbers into one field by joing them some how (or maybe Access did
this somehow to create an Index of the 2 fields - not too sure how an index
is made) then thought this would cause a lot more problems than it would
solve.

Neil.