Please help me understand lookup field and related fields

[triplee23]

Hi,

I am new to Access 2007 and need some major help in understanding some essential concepts. I am an experienced Filemaker user and in Filemaker there are two distinct situations when it comes to related tables and the process of populating fields with information, either the look-up or just the relationship. With looked-up data the data is physically copied to the secondary table meaning that it does not change even if the value in the primary table changes. The other situation the data i merely referenced through the relationship and will change when primary table is changed.

Now to my question: How does this concept work in Access 2007. How do I physically copy data using the relationship and how to I use the other situation referenced data. How are these two instances handled in Access 2007?

This is especially important in f.ex. an invoice solution with tables: tblinvoice <-> tblinvoice_rows <-> tblproducts. If I want to create an invoice with data from tblproducts, how do I copy information to the tbl_invoice row and not only as referenced information? For example, I do not want price to change in older invoices even if the price changes in tblproducts over time. The form doing this will probably have a combo box for every row in the tblinvoice_row with the products in a drop-down list. Choosing a product in the combo box then populates typical fields as price, description etc.

Can anyone help me understand these basic concepts?

 

[HiTechCoach]

have you looked at the Nortwind Trader ecamole?

I would avoid the table level lookup option. They are "evil".

 

[HiTechCoach]

see

http://www.mvps.org/access/tencommandments.htm

 

[triplee23]

Ok, I see the solution in the Northwind example. I should use a dlookup function when adding rows to the invoice "placing" actual prices in the order_row table. Thanks for helping me.

When you say avoid lookup on table level, do you mean not use the field type lookup?

 

[gemma-the-husky]

not sure about filemaker

but in access, when you have a table linked to another table, there will be a primary key in the main table, which is referenced to a freogn key in the subordinate table.

say, we have tables for customers, and reps

then each customer will be allocated to a rep. The entry in the customers table for the rep, will be a foreign key, (ie it will be the key for the appropriate entry in the rep table)

Now normally this key in the rep table will be a meaningless surrogate key - generally numeric keys are recommended - rather than the actual rep's name - so lets call it a repcode. So to get it clear - the customers table stores the repcode, whicvh "points" to the record in the reptable where the rep's name can be obtained.

now - when you design a form to manage the customers, you dont want to see the repcode, you want to see the rep name - but you DO HAVE to store the repcode in the customers table. So you design a combobox - or a list box, which does this. Behind the scenes it stores the repcode, but visually it allows the user to interact wth the repname.


NOW - to take this further - this functionality can be easily added to a form. What MS did, though was enabled you to declare this lookup in the customers table. When you select a data type, at the bottom there are two tabs - one for general, and one for lookup.

In lookup it will show the field as a textbox, but you can change this to combobox, and then access will automatically add a combo box to the form whenever you use this field.

It is this functionality that HiTechCoach is referring to, as for a variety of reasons it may cause more problems than it solves.

Now I am not sure exactly what filemaker does, and just how it bears comparison with Access, but hopefully this will give you some insight.