Load name from two different table in a single combobox (1 Viewer)

[hanarina]

Hello,

I have a problem with loading student names from two table. I want my cmbobox to view the student name for searching their form. I'm not sure how to give value from 2 table.. Can anyone help me??

Thank You

 

[Isskint]

I would have to ask why you have student names in 2 different tables? Duplication of data will (and you have encountered one manifestation) cause you problems. Search the internet for articles on Normalization.

So long as you have a common field in both tables you could use an INNER JOIN on these common fields. Your SELECT statement should list all the fields from both tables (EG SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2 etc) and then follow with the INNER JOIN (EG INNER JOIN Table1.Field1 ON Table2.Field1)

 

[hanarina]

I would have to ask why you have student names in 2 different tables? Duplication of data will (and you have encountered one manifestation) cause you problems. Search the internet for articles on Normalization.

So long as you have a common field in both tables you could use an INNER JOIN on these common fields. Your SELECT statement should list all the fields from both tables (EG SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2 etc) and then follow with the INNER JOIN (EG INNER JOIN Table1.Field1 ON Table2.Field1)

I separate it so that my table wont be so crowded, should i just combine them?

Can you guide me how to create the SQL code? I'm not sure how to use it..

 

[Isskint]

Yes i would suggest you combine these 2 tables. If the second is a duplicate of the first (as far as fields go) then you can use the Query Wizard to create an Append Table query. If there are different fields you may have to do this manually.

Once you have a single table, simply use the combobox RowSource builder to select the fields you want to display in the combobox.

 

[BigHappyDaddy]

Search this forum for normalization. If your data tables are set up correct, then everything is easier after that.

 

[hanarina]

Let say I combined the 2 tables, how can i search student name from intake 1 only?

Here is the sample of database I'm working on..

 

[BigHappyDaddy]

What makes the data different between these two tables that you need two tables?

For example: If Table1 contains "Seniors" and Table2 contains "Freshman", then you could create one table with another field like "GradeID" that could be a FK. Then you could alter your combobox rowsource to only return the grade you wanted see in the combobox.

Second, I am assuming that the values in Table1 and Table2 are unique and would never duplicate. If that is not the case, then we need to back up some more on your original tables.

 

[hanarina]

These two tables have the same field, I separate it so that I can search student detail from table1 only or table2 only (that what my lecture told me to do), then she asked me to add another search button so that she can search from those two table.

This is just a simple database to record student details. My lecturer just want to search the student details..