How to build a relationship between three tables.

English
, ,
#1

I have a table that lists information about an order, a table that contains the items in each order and a table that contains information on all the items.

The objective is to get the availables (imagine the work “available” as the word “order” for this to make more sense) and available_items tables in the same form, with a lookup field that will automatically populate data into the available_items table by taking it from the itemlist table.

I know the first step in this process is to create a relationship but every time I try to link the three tables and save it, when I go back into the relationship window all of them are gone.

For demonstration purposes imagine the tables this way:

Tables

(These relationships don’t save, if I save them and then go back into the relationship window, they’re gone)

Primarily I first need to know how to actually build this relationship but after building the relationship I need to figure out how to get the first two tables on a form with the third as an information lookup for the second so if you know how to do that as well I would really appreciate the information.

Also, maybe I don’t even need the third table on this form because the user never needs to enter information into it, I just need to get the information from the third table to populate the second table with the correct info for the item that’s being entered into the order. Can I do that with a query?

setting up database
#2

It’s possible your fields are not of the same type for your relationships. Setting all as Integer, I had no problem:

image description

As for the remainder of the question, I don’t understand why all the duplication of information in your available_items table is needed. When this information is needed just use a query to get the information from the itemlist table. One of the objectives of a relational DB is not duplicating info.

#3

I don’t know why the relationships wouldn’t save earlier but they wouldn’t and they just started saving suddenly, so the relationships are saved. As for the query I’m not sure how to do that, I tried every way I can imagine to get a query that references the third table to work in the form that contains the first two tables but I can’t find any way to get any information from the third table except a list of the columns. Also, I thought I needed the duplicate columns so that once I finally get

#4

the query working it would populate those fields with information from the third table, I don’t need to do that?

#5

I was now actually able to use a combo box combined with a query to get a list of the Case UPCs from the itemlist table. Now how do I display other information so they can know which UPC they are picking and transfer it into a record in the second table once they’ve chosen one?

#6

Your last comment has me baffled. I know nothing of the system you are trying to generate. “A list of Case UPCs” means nothing. Obviously there must be a great deal more to what you are doing. Without this information I am discarding the sample I was working on. It is probably not viable based upon the comment. It almost appears every item in itemlist is unique seeing that you have a list of UPCs.

#7

@Umaroth Have not yet seen any response on what your processing actually entails. Cannot help any further without that information. Possibly you have resolved the situation?

#8

Hello Ratslinger. Thank you for your answer. I did not open another question because is the same that this question. I am creating a DB for school use, so I create 4 tables. One is the Student information, with Student ID and address, phone, email, program.
Other is Classes, class ID, the name of classes, number of hours, number of credits.
Other is Semester. Semester number, intake, and finish date
Other is enrollment, with the payment information.

So, the first thing that the user does is create the student profile.
After that user needs to identify which semester is, the intake, and finish date.
After the definition of the semester, the user needs to include the what classes the student must to study to graduate in that semester.
After all, the financial information.

My question, and my problems, starts when the user needs to input the class information because I don’t know how to configure the form to find the student information, and then the semester, and then the classes.

If you could help I’ll appreciate.

Thank you,

Victor

#9

@VHM Did you not read my comment to you on this post? Questions do NOT belong as Answers. I also stated there some answers & if you still had a problem to submit a NEW question. Also, this is not really the same. The question is primarily about relationships getting removed.

#10

@Ratslinger: I fully agree. Why did you not downvote the answer?

#11

@jimk In the few years I’ve been doing this I have only down voted one question and that because of rude and uncalled for comments. I don’t upvote much either because frankly I don’t see much value in the asked questions. Most of the questions (I deal mostly in Base/DB) are already answered somewhere else and it’s just a matter of a little searching or reading the docs. Have responded to ‘JRE Missing’ question about 40 times - by that it should be down voted.

#12

BTW, the vast majority of my up votes are to give the person enough karma to upload a sample or image.

#13

@Ratslinger: I upvote to encourage well-written questions and useful answers, and downvote to discourage unclear or otherwise problematic questions and answers. I would also beg to differ about the usefulness of the site. In fact, I have found more than one of your answers on this site to be beneficial, and while it may be true that the information could be gleaned from other sources, many of your answers help clear up misunderstandings about the best way to solve a particular problem.

#14

Regarding ‘JRE Missing’ questions, my understanding is that they should be closed as duplicates, and then there is no need for downvoting. This makes it easier to find the best related question.

#15

Granted, I have only been a user on this site for a few months. Perhaps my views are overly idealistic, but these concepts work well on other (better designed) sites, so I’m hoping they can help here as well.

#16

Thanks for the comments. I think with some time your opinion may change. Don’t misunderstand. I continue to answer all questions I can; duplicate or original (?) :slight_smile: If you start marking items closed as duplicate it may discourage posting; if you include a link to another post no need to close as duplicate. Some battles aren’t worth it. I bet I answered the above question a dozen times but I’ll answer it again if it is posted as a New question as asked for.

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.