Primary Key Fields

strawberry

Registered User.
Local time
Today, 15:22
Joined
Apr 1, 2008
Messages
43
Hi, i am creating a new database. here is a brief description

Table 1 - DrawingNoSequence (Primary Key) ie 6054
Supplier
Model

Table 2 - DrawingNoSequence (Primary Key) ie 6054
DrawingNo (Primary Key) ie 1
Revision (Primary Key) ie 0
Date
Title

the relationship between these two tables is the DrawingNoSequence

Table 2 has 3 primary keys to make a unique reference ie. 6054/1/0 or 6054/1/1 or 6054/2/0 for example

The third table i am creating is called customer table and this will
comprise of

Customer Name
Date
DrawingNoSequence/Drawing/Revision ie 6054/1/0

This is the bit i am stuck on. I have created the customer table doing a look up for all 3 fields separately but when i create the form it doesnt seem to work properly

i want to create a customer form where by there is a field which lists a combination of the three primary keys from table 2 where the operator scrolls down to choose which drawing they have. I also am not sure on what relationships need forming between table 2 and customer table

I am guessing that the customer table needs to be created in a particular sort of why in the first place for this to work

I hope someone can point me in the right direction and hope the above makes sense

Many thanks
 
Simplify -

Use an autonumber for the primary key (let Access manage the keys) and if you want no duplicates on a certain set of fields set a multi-field index to keep that. But, I would avoid creating your own primary keys as it will likely not be maintainable.

How to Create a Multi-Field Index
 
Simplify -

Use an autonumber for the primary key (let Access manage the keys) and if you want no duplicates on a certain set of fields set a multi-field index to keep that. But, I would avoid creating your own primary keys as it will likely not be maintainable.

How to Create a Multi-Field Index


Many thanks Bob for your reply, i will have a go. i may be back again later with another question or two but thanks again, really appreciate your help
 
Many thanks Bob for your reply, i will have a go. i may be back again later with another question or two but thanks again, really appreciate your help

Hi I have now created as a multifield index as you suggested but i still am having problems, heres a quick overview so you have an idea of what i am on about

Table 1 - Drwg No Sequence (keyfield)
Model
Material etc etc

Table 2 - ID
Drwg No Sequence (lookup to table 1)
Drwg No
Revision
Date
Attachments

I have created an index so that combination of Drwg No Sequence and Drwg No cannot be the same ie 6054/1 and 6054/2 are ok (and this works fine) if i try and enter in 6054 then 1 as the drwg no it tells me that i cant cos it already exists so this is all fine

I then created a 3rd table - Customer Table

Table 3 - Customer
Customer ID
DrwgNoSequence (lookup from Table 2)
DrwgNo (lookup from Table 2)

I then created a report from a query

In Table 2 against each DrwgNoSequence/Drwg No record i have attached a PDF drawing under the attachment field

I want to create a report showing

Customer, Drwg No Sequence, Drwg No, Attachment

I created this report from a query, ie first 3 fields from customer table and attachment from table 2

the information returned is all muddled, i have tried tweaking things but it is not running the report correctly. The main problem is the attachments, its not showing the correct attachments as what is stored in table 2

sorry to twitter on, hope you can help me
 
You aren't using lookups at table level, are you? You are just using a form to put the data in, correct?
 
You aren't using lookups at table level, are you? You are just using a form to put the data in, correct?

Hi Bob, yeah when i create the table i use the lookup option then, ie. on table 2 i choose lookup when i created drwgnosequence and selected the table 1 and the relevant field

again, on customers, i choose lookup on drwgnosequence and drwgno to get the info from table 2

is this not right ?
 
As it is 2007, I don't have 2007 at work here so hopefully someone else can help and if not by the time I get home, I'll try to do it from there.
 
As it is 2007, I don't have 2007 at work here so hopefully someone else can help and if not by the time I get home, I'll try to do it from there.


Cheers Bob, i look forward to hearing from you
 
Okay, I didn't have time to work on the forms but your tables needed some redesign work. I'm not 100% sure that this is the best but you had redundant data stored (you should not be storing the same data twice in tables except for one field and that is the Primary key from one table in the other as the foreign key).

See the screenshot of relationships and also the attachment I am attaching.

drawing01.png
 

Attachments

Okay, I didn't have time to work on the forms but your tables needed some redesign work. I'm not 100% sure that this is the best but you had redundant data stored (you should not be storing the same data twice in tables except for one field and that is the Primary key from one table in the other as the foreign key).

See the screenshot of relationships and also the attachment I am attaching.

drawing01.png

Hi Bob Thanks for working on the tables. i have looked at the attachment. i dont really understand how the tables work (i guess thats because i am so used to look ups). i cant see the drawing no sequence in the sub table. i am very confused now. sorry to be a pain but can you help me further please
 
i cant see the drawing no sequence in the sub table. i am very confused now. sorry to be a pain but can you help me further please
The Drawing No Sequence should ONLY be in ONE place. The relation, is the ID field - MainID which ties the Drawing No Sequence together with the subtable.
 
The Drawing No Sequence should ONLY be in ONE place. The relation, is the ID field - MainID which ties the Drawing No Sequence together with the subtable.

I have been looking a bit more around the tables and yes i know understand a bit more. Additionally to what you did i added a multifield index for the MainID and Drawing No in the Sub Table

was this correct?
 
I have been looking a bit more around the tables and yes i know understand a bit more. Additionally to what you did i added a multifield index for the MainID and Drawing No in the Sub Table

was this correct?

No, you don't need to do that. I had already added an index to Drawing No so it couldn't have duplicates. The MainID will never have a dup.
 
I just downloaded yr zip file again and deleted all my old forms so i was just left with your 3 new tables. i created a quick form from the sub table and it allows me to duplicate the record, ie i have now have two records with main Id as 2 and drawing no as 1.
 
I just downloaded yr zip file again and deleted all my old forms so i was just left with your 3 new tables. i created a quick form from the sub table and it allows me to duplicate the record, ie i have now have two records with main Id as 2 and drawing no as 1.

Okay, maybe I missed that and THOUGHT I had added the index. Go add an Index on dr no as Indexed Yes(No Duplicates) and then it shouldn't do it any more.

MainID shouldn't be repeated.
 

Users who are viewing this thread

Back
Top Bottom