Trying to Clean up

[Tuckejam]

Trying to clean up my first real attempt at a database
(I started out thinking it would just be a first draft so I was a bit sloppy, but now I have a fair amount of data in it and I don't want to redo everything)


So for example I have a table tbl_HouseType and originally the field names were "ID", and "House Type"


and "House Type" contained the values: Mens, Womens, Mens/c, Womens/C

This all linked to a field in tbl_Houses where I had the dropdown menu to select which house type

NOW in cleaning up I have changed the field names in tbl_HouseType to "HouseTypeID" and "HouseType"

this changed all of the values in the linked field from the tbl_Houses table to The house Type ID

But how can I get it back to showing House Type instead of house type ID

[ranman256]

you must manually change it. Access can rename some items in queries, but not forms and some queries.

[Gicu]

Sounds like you have a lookup field in tbl_House that got broke because of the renaming in the lookup table. You should avoid using lookup fields in tables; the dropdowns should be incorporated in the user interface (forms/reports) via combo-boxes.

Read http://access.mvps.org/access/lookupfields.htm to get more info why most Access programmers avoid lookup fields in tables.

Cheers,

[Tuckejam]

Gicu,

While I agree with all 8 points listed in that link, not having a lookup field in a table completely ruins my understanding of what Access is for.
Not understanding until now that lookup field and relationships are different things

I think you are saying to create/use a form to do the lookup from TableA and enter the ID information into TableB

[Gicu]

Yes, it is basically how the lookup fields kinda' work but without the problems created by them. Most Access developers would tell you that the users should never interact with data at the table or query level, therefore eliminating the need for a "drop-down" at that level. Meanwhile for the forms and reports it is very easy to implement the lookup via a combobox control that is bound to an ID field (in your case tbl_Houses.HouseTypeID) and have a rowsource based on the "lookup table" tbl_HouseType ("Select HouseTypeID, HouseType From tbl_HouseType Order By HouseType;") with Column Count =2 and Column Widths: 0";2" (the first column HouseTypeID is hidden).

Cheers,
Vlad

[Tuckejam]

What about having drop down box in the table but keeping the values stored in the table's row source for data continuity.

there is no real look up this way to another table, and no extra dataID field to contend with, at least that I know of.

[Micron]

there is no real look up this way to another table,

Yes, there is. It is just hidden from you in a deep system table that you can't get at. A table level lookup mimics what is really the better way of doing it. Why have it then? Because it eliminates the need for Access novices to learn how to do it the right way and any time you can do that, it sells. Not sure if you even need this crude example, but here goes

tblPO
PO_NO_ID	PO_NO
1	12456
2	96354
3	87549

tblPOLine
LineItemID	PO_Nofk
1	1
2	1
3	1

In PO_LINE the fk from PO is 1, which relates to 12456. Doing your own normalization, you'd have these 2 tables and join them on their related fields. When you create a table level lookup field, Access creates the second one which you can't see, and shows you 12456, not 1. You think the value shown in your table is 12456 but it isn't. That's where the problems start arising from.
Hope that helps somewhat.

[Tuckejam]

Alright...Well accepting that I need to do more that a little clean up, If I start over with a better layout, and make sure I am rigid about naming protocols.

What is the best way to get my data from DbVersion 1 to DbVersion 2.

I looked at importing, and it seemed like it was a whole tables or nothing, Right now I'm thinking Ill export the data back to my good friend Excel, polish it up a bit and send it back to Access?


And yes I am sure I want a fresh start, I am to new to this, and there are just to many property settings to make sure I get right, and if I at least start from scratch then I know when I ask for help my settings should at least be in an understandable place.

[Micron]

Which way to go depends - on which you consider easier/are more familiar with. To get data into a different table schema using Access might require several update or append queries, which might involve a lot of references to tables and fields. To boot, if lookup fields are involved you will probably have to access the .Value property of the mv field in a query. If you haven't done that before, starting over from Excel might be more expedient. I suppose you could always fall back to the Excel method if the other didn't pan out.