Two Foreign Keys???

F

Frederickrabbit

Registered User.
Local time
, 18:49
Joined
Nov 26, 2019
Messages
10
I have been trying to figure out the :banghead: proper way to structure my table to reference 2 foreign keys and I could really use some help from the forum. So lets say I have a Sales Order #, in a Sales Order table and I have multiple Lines on each sales order, in a second table linked by the Sales Order #. How can I link a third table that uses Sales Order and Line number to reference one item on the sales order? For example, I am performing quality checks in another table and I want to link it to one specific Sales Order and line number? Any help would be appreciated.

I need to be able to create a report for each Sales Order that would list all Quality Checks performed.
 
Hi. Welcome to AWF! Couldn't you use the Primary Key of the Order Details table?
 
Yes. That makes sense, but I need to reference it by both numbers. Example, I have a form that I use to enter the Quality Checks and I have the SO# and Line #. I do not want to look up an ID# for the reference. I need to enter this known information and have them link automatically.
 
Frederickrabbit said:
Yes. That makes sense, but I need to reference it by both numbers. Example, I have a form that I use to enter the Quality Checks and I have the SO# and Line #. I do not want to look up an ID# for the reference. I need to enter this known information and have them link automatically.
Click to expand...
For us to understand that better, are you able to post a sample copy of your db with test data?
 
This is not currently working as I want but it might give you an idea of what I need to do. I want to enter the SO# and Line# into my form and later when I print the report for SO#, all of the testing data will be listed. Thanks for taking a look.
 

Attachments

A line item belongs to a Sales Order.
You link to a line item. You use the Sales Order ID from the Line item to look up the Sales order, no need to have a copy.

I think you may be making more work for yourself.
 
Question:

So, in my sample table, I want to enter SO# in one field and Line number in another field. How does this reference the specific lineID? I don't know what I am missing here.
 
Hi. Would you mind zipping up your file? I can't download it right now because of its size. Thanks!
 
Frederickrabbit said:
Question:

So, in my sample table, I want to enter SO# in one field and Line number in another field. How does this reference the specific lineID? I don't know what I am missing here.
Click to expand...

What advantage does having your SO# saved in the child record of a child record have for you? What prevents you from making a query on the Detail line you are attaching to, and having the query look it up for you?

One reason you are having an issue is that you are trying to do things in a non-normal way. How you are doing this doesn't show a benefit that I can see and only allows you to confuse yourself if you get the wrong SO# or detail ID into your table, then can't reconcile where you are actually looking.
 
I might be confused, true. But I still don't understand how it works. So, when I am entering my sample, I cannot use line#. If I put line 1, for example, I would have several Sales Orders with line 1. So, what number can I enter on the sample form that would reference line 1 on Sales Order '123456'?

Zip example is attached.
 
Sales_Order_Lines_Table should have a Sales_Order_Line_ID that is auto inc. This is what you would link to.
 
Mark_ said:
Sales_Order_Lines_Table should have a Sales_Order_Line_ID that is auto inc. This is what you would link to.
Click to expand...

Mark,
Sorry if I don't understand what your are getting at with this. Sales_Order_Line_ID, can only reference single items on line No Table. I would have multiple line numbers each with their own testing.

Now, it's attached.
 

Attachments

Normally when you design a table, you have an "ID" field that is numeric, auto numbered. This generates a unique number that is associated with that record in that table. This field is ONLY used to link records and maintain referential integrity.

You would have a unique ID like this is in your parent SALES table. Each child record in your Sales Line would have a copy of this.

Currently you are using short text fields. This is not normal. I would highly recommend allowing the system to generate unique numbers to link together your tables rather than using strings like this. Once you do, your current issue goes away.
 
Mark_ said:
Normally when you design a table, you have an "ID" field that is numeric, auto numbered. This generates a unique number that is associated with that record in that table. This field is ONLY used to link records and maintain referential integrity.

You would have a unique ID like this is in your parent SALES table. Each child record in your Sales Line would have a copy of this.

Currently you are using short text fields. This is not normal. I would highly recommend allowing the system to generate unique numbers to link together your tables rather than using strings like this. Once you do, your current issue goes away.
Click to expand...

I do realize this. I am only using it in my test setup to reduce the number of fields, until I understand how it is going to be linked. The SO number is preceded by an 'S' like this 'S123456' and it is a unique number. There will never be 2 'S' numbers this same. This should not affect the way I am setting this up for my test. I have a larger more complicated database already in use that this will be injected into once it is working. So, I am only looking for information on how to link to a single item on the SO_Line_Table by using two known pieces of information SO# and Line# and with these two pieces of information in my sample table it will link to a specific line on that SO#. Again, I may be totally confused about how I am going about setting this up. But that is why I am asking for help. Can you provide a working example or a link to an explanation that shows how I can do this?
 
I could use 'S123456-1' as my reference number in the Sales_Lines_Table, but this seems redundant and unnecessary. I should be able to use

Sales Order # 'S123456'
LIne Number # '1, 2, 3, 4... etc.

Now, in my sample table
Sales Order # 'S123456'
Line number # '3'
Testing Date 1/1/2020
Tested by Some Person
etc.

I just don't understand how to link the sample table to the line item table.
 
Hi. I downloaded your file, but I also need to understand what business process your database is trying to model. I can then, perhaps, create some mockup forms for you.
 
theDBguy said:
Hi. I downloaded your file, but I also need to understand what business process your database is trying to model. I can then, perhaps, create some mockup forms for you.
Click to expand...

That would be great.

Starting with a Sales Order Table the Sales order has the following fields:
SO_ID (PK)
SO#
Customer_ID (FK) Not shown here
Delivery_Address
Contact_Name, etc...

Each Sales Order could have multiple lines
Each line would have :
Line_Item_ID (PK)
Sales_No_ID (FK)
Line_No
Item_Code
Description
Quantity

Finally the samples table where I want to link to one specific line_item in the Sales_Lines Table
Sample_ID (PK)
Sample_Date
Sampled_by
Sales_Order_ID (FK??)
Sales_Order_No. (linked reference via Sales Order ID)
Sales_Line_ID (FK??)
Sales_Line_No (linked reference via Sales Line ID)
Sample_Weight etc...


Thanks again, I hope this is clear.
 
So, again, I am taking a sample for the product listed on Sales Order S123456, on line 3, and recording this in the sampling form. The two numbers, I would think I can access via a combo box that would be linked via a FK.
 
Hi. I think this is the same as what Mark was trying to say earlier. Meaning, you only need one foreign key in the Samples table, which would be the Primary Key of the Order Details table. When dealing with one-to-many relationships, the recommended user interface is a form/subform setup.


For example, you could have the main form bound to the Orders table and have the subform bound to the Details table. To enter Samples on another form, you can use another form/subform setup. The main form is bound to the Details table and the subform to the Samples table. You can also use a button or double-click from the Details subform to open the Samples form to enter test data.


When I get some time after Thanksgiving, I'll see if I can create a demo file to show what we mean.
 
You must log in or register to reply here.

Similar threads

C
Join key of '***' not in recordset
Replies
9
Views
295
ChrisLeicester
C
A
Solved Form Combo Box Doesn’t Store Selected Value (Foreign Key Issue)
2
Replies
20
Views
414
GPGeorge
GPGeorge
G
Relationships and Tables conundrums
Replies
9
Views
699
MajP
MajP
D
Solved Creating transaction records via OnClick - (Sales Order to Invoice conversion)
Replies
10
Views
698
pbaldy
pbaldy
N
Solved MS Access Switch Board Menu
Replies
5
Views
532
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom