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

Question

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?

Answer 1

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 "ID" from tblcustomer, and the 2nd column (for easy user display) would of course show/display the customer name. But, that "value" of the combo box will be "id". Then when you save tblmain record, you save the "ID" 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 "edit" and "save" 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.