Two Foreign Keys??? (1 Viewer)

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

Hi. Welcome to AWF! Couldn't you use the Primary Key of the Order Details table?

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

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.

For us to understand that better, are you able to post a sample copy of your db with test data?

Frederickrabbit · Nov 26, 2019

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.

Mark_ · Nov 26, 2019

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.

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

Hi. Would you mind zipping up your file? I can't download it right now because of its size. Thanks!

Mark_ · Nov 26, 2019

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.

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.

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

Frederickrabbit said:
Zip example is attached.

Hi. I don't see it. To which post did you attach it?

Mark_ · Nov 26, 2019

Sales_Order_Lines_Table should have a Sales_Order_Line_ID that is auto inc. This is what you would link to.

Frederickrabbit · Nov 26, 2019

Mark_ said:
Sales_Order_Lines_Table should have a Sales_Order_Line_ID that is auto inc. This is what you would link to.

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.

Mark_ · Nov 26, 2019

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.

Frederickrabbit · Nov 26, 2019

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.

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?

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

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.

Frederickrabbit · Nov 26, 2019

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.

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.

Frederickrabbit · Nov 26, 2019

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.

theDBguy · Nov 26, 2019

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.

Two Foreign Keys??? (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

Longboard on the internet

Registered User.

I’m here to help

Longboard on the internet

Registered User.

I’m here to help

Longboard on the internet

Registered User.

Attachments

Longboard on the internet

Registered User.

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

Similar threads

Users who are viewing this thread