How do I read/write record in Access Database that has table relationships?

I am creating an Access Database, that has the following tables and field

TableMain           TableCustomer             Record in TableCustomer         
   ID  Auto
   fDate(Date/time) CID (Auto)                4
   fCustomer -------fCustomerName (short)     John Smith

the relationship is Field fCustom in TableMain to field fCustomerName in TableCustomer

How do I go about creating a new record in TableMain, referencing John Smith in TableCustomer (ID=4)

````command.CommandText="INSERT INTO TableMain (fDate,fCustomer) VALUES (@dt,@cust)";
````command.AddParameterWithValue( "dt", #10/15/2023#);
````command.AddParameterWithValue( "cust", ???);  <- what do I put for the value
   do I put "John Smith" or the number 4 (ID for John Smith)?
   
   2) How can I select a record that will have the related data returned to me,
   is it just SELECT * FROM TableMain, or does it need joins?


</details>


# 答案1
**得分**: 1

fcustomer列应该是一个普通的长整型数据类型。

所以，是的，您将4插入到该记录中。

在任何报告中，或者甚至可能是一个查询？然后您只需拥有主表，然后基于ID连接到客户表，然后您可以通过使用该SQL连接来欣赏/使用/显示/查看全名。

在编辑tblmain表的表单上，您可以使用下拉列表框（组合框），它将有两列，第一列（隐藏的）将来自tblcustomer的“ID”，第二列（用于用户显示）当然会显示客户名称。但是，该组合框的“值”将是“ID”。然后当您保存tblmain记录时，您将“ID”值保存到名为fcustomer的列中。

更好的做法可能是为这些列采用命名约定，因此

在tblmain中，tblCustomer_ID将是更好和更具描述性的名称。

所以，是的，尽管在这个示例中，您作为开发者必须将4保存到tblmain中，但用户当然不必键入4，而是您将提供某种下拉列表（组合框）或类似的东西，以允许用户以友好的方式选择客户。

因此，用于编辑tblmain的表单只有一个记录，并且当您保存时，您保存该表单的数据列。

然而，虽然您编写的代码是这样工作的，但您在该表单上提供了一个UI，允许用户通过名称选择/查看/使用/享受选择客户，但是您的代码后台当然会使用客户ID值，并将该数字保存到tblmain记录中。

这意味着您的用于“编辑”和“保存”tblmain记录的表单仍然只保存一个记录，尽管该表单上有一些控件，可以从客户表中提取并显示相关数据。

所以，在您的后台代码中？

作为一般规则，您仍然仅更新tblmain记录的单个记录，它将基于该表，而不是某种SQL连接。

您仅需要那个SQL连接来进行类似网格显示的情况，或者可能是报告，但对于编辑此类数据的表单？不，该表单将在一个记录上工作，并且一些控件，如列表框或下拉列表框，用于提取和显示相关数据。

<details>
<summary>英文:</summary>

fcustomer column should be a plain jane long data type. 

So, yes, you insert 4 into that record.

In any report, or maybe even a query? Then you simply have table main, and then join on the customer table based on ID, and then you can enjoy/have/use/display/see the full name by using that sql join.

On a form to edit tblmain, you could say have a combobox (drop down list), and it would have two columns, the first column (hidden) would be &quot;ID&quot; from tblcustomer, and the 2nd column (for easy user display) would of course show/display the customer name. But, that &quot;value&quot; of the combo box will be &quot;id&quot;. Then when you save tblmain record, you save the &quot;ID&quot; value into a column called fcustomer.

Probably better to adopt a naming convention for those columns, and hence

tblCustomer_ID would be a better and more descriptive name in tblmain.


So yes, while you the developer have to save 4 in this example to that tblmain, the user would of course never have to type in 4, but you would provide some kind of drop down (combbox) or some such to allow the user to select the customer in a nice user-friendly approach.

So that form to edit tblmain is ONE record, and when you save, you save the columns of data for that form.

However, while the code you write works this way, you provide a UI on that form that would let the user select/see/have/enjoy the selecting of a customer by name, but your code behind would of course work with the customer ID value, and save that number into the tblmain record.

This means that your form to &quot;edit&quot; and &quot;save&quot; tblmain records still is only ever saving one record, despite the fact of having controls on that form that can pull + display that information from the customer table.  

So, in your code behind?

As a general rule you still ONLY updating a single record for that tblmain record, and it will be based on that table, and not some type of SQL join.

You only need that sql join for a grid like display, or perhaps a report, but for a form that edits such data? No that form will work on the one record, and some controls like a listbox or combo box (drop down list) are used to pull + display that related data.




</details>