How to update foreign keys in a table.

[RainLover]

Here is a quick exercise for you.

Using a Table that has a Combo Box as a lookup to another Table.

Create a simple select query using that Field plus one or more others.

Now sort Assending and then Desending.

Did it work properly.

PS Do you need more help or are we done.

 

[mahenkj2]

I have made a dB based on your advise and attached. Please see if this is what you wanted me to test.

I have checked and it was working fine.

best regards.

 

[RainLover]

This is not what I expected to see.

Normally with a Combo one would select the PK and a Data Field. The Column widths would be set at 0,.5 so that the PK was hidden.

Sorting would be on the PK not the Data which is what you would see in Datasheet view.

I was attempting to show you an example of why not to use lookups in TABLES.

See attached.

 

[mahenkj2]

I understood your thoughts.

In fact, I just forgot to do Combo thing otherwise I would already have understood. Kindly excuse me for underestimating the theme of this exercise. Anyway, after learning populating the foreign keys in a table by you by selecting 2 columns and hiding first column (PK) by virtue of column widths, I am using that in my dB.

But above exercise taught me the real impact of look up of tables.

Many-Many thanks for your kind support and time.

I hope this should conclude this thread. (so that I can think to start with a new post)

 

[RainLover]

Thanks for that.

 

[mahenkj2]

I think I need something more from this thread.

It is related with being able to see some desired field by setting column width to 0 for some field.

Two questions:
1. What if we use the combo field in calculation? Because, what we see is just another column but in actual, it may be just a PK? calculation results wrongly as in attached dB.

2. What if we need to use text box instead of a combo? Text box does not have column width setting.

I think using column counts and column width is normal practice, I hope there should certainly be solutions for above issues. Please suggest.

I attach a sample dB.

 

[RainLover]

Columns can be refered to as Column 0, or 1, or 2 etc.

This is often done in code.

Search Access help for more information.

 

[mahenkj2]

Would it be possible for you to do in the sample dB attached by me in my last post, I can take some idea from that.

 

[RainLover]

I can't open 2007.

At this Forum I would advise to always post in 2003 as not every one has 2007 or 2010.

Some people still use A97.

 

[mahenkj2]

Attached please.

 

[RainLover]

The Database you sent is not set up correctly to demonstrate.

I obtained the following from Access Help.


To learn more about combo box properties, see Microsoft Access Help, or click the property box for the property and press F1.
I want to refer to a column in the list box or combo box other than the bound column.
In a form, to refer to a column other than the bound column, use the Column property. The Column property is zero-based. For example, to refer to the second column in the combo box called SuppliersCombo on the Products form, use this syntax:
Forms![Products]![SuppliersCombo].Column(1)

 

[sammers101]

I am having a similar problem, trying to understand the example you posted of why not to use lookups in tables. In tblStock Query, I tried sorting each row, seems to work to me..am I missing something?

 

[RainLover]

sammers

Have a look at the link in post #15

 

[rave07]

I have a single form used and one master table which is connected to many child tables and child table is connected to master table by a linked table containing both table primay keys.

When I am going to fill all details on to the form, it is taking data from other tables and storing in the main table but not updating the linked tables in which two table's primary keys are stored.

I am not able to generate a report because linked tables are not updating.
Could anyone please help me out. I am new to MS access.

 

[Pat Hartman]

Updating a field in tableA does not propagate a change to a similarly named field in tableB. I looked at your database but I didn't know what you were trying to do but in a properly normalized database, "data" appears in one and only one table. When you need that "data" in another place, you use a foreign key so you can join to the table to get the data. Let's use a simple order entry example. You have a customer table with customer name, address, phone, credit limit, etc. The customer places an order. The order table contains the CustomerID. It does NOT contain the customer name or other customer information but the Order form can show that info because you base it on a query that joins customer to order.

I attached a database that shows how to relate tables and how to use a subform as well as a pop-up form.

 

[Cronk]

@rave07 You have added to an old post. Pat's response might be in relation to the database posted by the originator of the thread in 2012.

If you have a simple parent table/ child table, don't need a join table, just include the parent's ID field in a foreign key field in the child table.

If you wan to persist with a join table, you need another field in the join table to indicate that the join is to a particular child table.