[Solved] Pull data from another table

[hannahelastic]

Hello, I started working on my first database ever yesterday and also had my first fun with some SQL. Whee! Everything is working great except there is one thing that I would really like to do. Here is my situation:

Table A (Materials) contains fields such as "Width" and "Height"
Table A's primary key is Material_ID
Table A has a one to many relationship with Table B (TableA.Material_ID to TableB.Material_ID)

Table B (Purchases) records information about purchasing the materials from Table A (date, price, etc)

I have forms for both tables. The form for Table A (with a subform of Table B) works perfectly. Yay!

What I would like to do with Table B's form is to automatically pull information from a unique record from Table A based on what I enter in Table B's Material_ID field. So, for example, if I entered "4" in the Material_ID field in Form B, it would automatically pull certain information (e.g. width and height) from record 4 of Table A. I want to do this so I can quickly confirm that the Material_ID I entered in Table B is correct.

Can anyone point me in the right direction? I'm new to this so I'm having trouble even figuring out what to search for. I appreciate any guidance you can give me! Thanks

(p.s. feel free to get complicated on me if necessary...I like the challenge. Also, I apologize if I'm using incorrect or confusing terminology)

[Villeroy]

Is it true that each purchase can involve more than one material?
Or does each material belong to one (or no) purchase?

[hannahelastic]

The way I have it set up (for this very simple purpose) is that one purchase will be associated with one material.

One material will be purchased many times over time. But each purchase refers to only one material.

Does that clarify things?

[Villeroy]

Yes, thank you for this important clarification. So we discuss a one-to-many relation and your database design with a single relation applies well.
Starting from scratch, use the wizard to create a new form for the purchase table including both ID fields, the Purchase ID and the Material ID of the purchase.
Open the form for editing (Edit>Edit...) or when the wizard comes to the end, tell it that you want to modify the new form rather than work with it.
View>Toolbars>Form Design
Button #5 opens a special form navigator.
Pick the text box which is bound to the Material_ID from the navigator (or Ctrl+Click it on the form)
Right-click>Replace With>List Box
Right-click>Properties (or right-click>"Control..." from the selection on the form sheet)
Tab "General", property "Drop Down" = Yes
Tab "Data":
Data Field: Material_ID [the field that will receive the selected ID number]
Input Required: Yes [the purchase has to have some Material, right?]
Type of Content: SQL [we will fill the list by its own simple 2-column select statement]
List Content: SELECT "Name","Material_ID" FROM "A" ORDER BY "Name" [you can also hit the small [...]-button and use the query designer to compose this. I assume that your material in table "A" has some "Name" field.]
Bound field: 1 [which is the second column "Material_ID". The first visible one is field 0]

Turn off design mode (button #2 on the toolbar) to test the list box.
The list box should show the material name and when you select a material by name, what gets actually written into the data field is the corresponding Material_ID.
If it works, save the form document and its database document. If not, turn on design mode again and try to find a reason. Don't hesitate to ask here.

Like all the other Base wizards, the form wizard is almost useless since it is not able to do anything with list boxes. You always have to do list boxes by hand.
Notice toolbar "Form Design" and the navigator you can get from it. The toolbar provides all the tools you ever need to create any amount of forms with subforms, sub-subforms and controls on the same form document (it is an ordinary Writer doc embedded in the Base doc).

[hannahelastic]

Hi, thank you so much for your very helpful step-by-step response. I really appreciate the time you took to help me. I could make that solution work...however, there are some materials that are identical except for their size, so I will have to rename them in some way to indicate their size. That's fine...I can do that...it's a good solution.

Still though, I know there has to be a way to do what I was originally asking...no? I don't need a step-by-step, but could you point me in the right direction? I'm really excited about this database bizness...especially since I realized now that I made this simple one that most websites are just big databases...duh! I need to start learning php and sql, huh? =]

Thanks again.

[Villeroy]

Either rename those items or do something I use to do with personal data where persons may have the same name:

Code: Select all

SELECT "Name" || ',' || "Forename" || ',' || Year("Birthdate") AS "Concatenation", "ID" FROM "Patients" ORDER BY "Concatenation"

The visible column of the list box will look like:
Smith,Adam,1723
Smith,Adam,1966
Smith,Kathleen,1988
Smith,William,1923
Works pretty well with some 1000 of persons. Just type "smith,k" to fetch Kathleen or type "smith," and then Alt+Down to pick the right Smith from the list.

[hannahelastic]

Hey, that works great. Thank you!