Is using the Lookup Wizard for tables a bad idea?

[Foxglove]

I am very new to this forum and also to Access. I wonder if someone can spell out veryclearly to me whether using the Lookup Wizard when creating tables in Access is a bad idea. The Wizard easily creates relationships betweentables and will insert fields from one table (the Child) into another (theParent). (Not sure if that's the correct terminology) But is this good practice? Should the ONLY field inserted into the Parent Table be the ChildID? I have read http://www.mvps.org/access/lookupfields.htm and I think thatis what it is saying, but please, would someone confirm that for me?



I hope I’veexplained my question clearly and that someone will answer!

[June7]

I seldom use wizards for any design effort in Access. I don't like what the wizards do, one of which is create lookup fields with alias in tables. As a developer, when I work directly with table I want to see the real values stored.

Actually, this lookup is not a 'childID'. Child records are related records that are dependent on the existence of another record - the 'parent'. Lookup table records are generally not dependent. As example, a parent record could be a purchase order and the related child records would be multiple records for the products associated with the purchase. This usually calls for a form/subform arrangement. Often, comboboxes in the subform will be 'lookups' to facilitate selection of detail items, such as products. The order detail records (children) cannot exist without the order (parent).

[Foxglove]

Sorry for using Parent & Child in the wrong context. However, what I am asking is whether the only field to be present in the related table should be the FK?

[June7]

That is the viewpoint of experienced developers. The FK of the lookup record should be displayed in the table, not the related alias. However, whether or not should save the FK or the alias descriptor is first decision. If the descriptors are short (like Good, Bad, Poor, Excellent or Home, Cell, Mobile) why not just save the descriptors and discard the FK? This eliminates the complications associated with lookup alias.

[Foxglove]

I've no problem with designing the tables with just FK's and no alias's. The only problem I am finding now is getting the data from Excel into the DB. When I had alias's I could paste into the table, now it's just FK's do I paste it into a Form derived from a query?

[June7]

I've never pasted records through a form but I suppose can be done, just don't see how that resolves your issue with lookups. I don't understand how alias in table enabled (or interferes with) pasting from Excel.

Why is Excel involved? What is the data in Excel - the FK?

[Foxglove]

Sorry, I don't seem to have explained my problem very well. I have a spreadsheet in Excel named Addresses with the data in it. I split it into its component parts and imported the resulting tables, Addresss1, Address2, TownCity, County, PostalCode, Country into Access. Originally I created a table tblAddresses in Access using the Lookup Wizard to make the relationships. The Table included the aliases. I then pasted the data from the spreadsheet into the table and that was fine.
When I created a new tblAddresses and made the relationships in the relationships window, without aliases as recommended, I obviously couldn't paste the data into the table, so how do I do it please? Perhaps I'm approaching it in a completely wrong way, but none of my books or the net is any help!

[June7]

I am confused. If you already had tables set up that were related by numeric PK/FK fields, there was no reason to rebuild tables. Just change properties of field in table to not have a lookup.

What are the tables and their relationships?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Foxglove]

Sorry for being a while getting back to you.
My problem was always getting the data into the database when the tables had no lookup fields.
I think I've worked it out now. I have created the child tables (Addresss1, Address2, TownCity, County, PostalCode, Country) and imported the individual bits of data into them ( please excuse the technical term!!) and then created the relationships. I then imported my Excel spreadsheet, which has the "complete" "Addresses" record to a temporary table. I then created a qryAddresses to pull the fields together and created a form from that. Then I pasted the fields from the temporary table into my form to bring the data together. Seems longwinded but I don't know how to do it otherwise.