LookUp? What Do I Use Instead?

[wiklendt]

I will probably try turning the lookup fields to text fields where the looked up table is a simple 1 field table without a subquery that hides the ID. I am working in Access 2007

the lookup fields should be data type of Number, and would store your corresponding primary key (PK; presumably autonumber) field from your related table, creating what is known as a foreign key (FK). you should ideally not use any field other than the autonumber field from the related table to become the foreign key (as a number field) in this table.

e.g., if you want to relate a new order with an item to be ordered, and who's ordering it...

tblOrders (hypothetical)
-----------
OrderID (Autonumber, PK)
OrderNumber (Text)
CustomerID (Number, FK)
ItemID (Number, FK)
OrderDate (Date/Time)
.
.
.

in the example table, the 'foreign' stored data would be CustomerID and ItemID. The IDs are known as "foreign" keys, because although they are not a primary key of this table, they are a primary key of a different table. the primary key of the tblOrders table is OrderID.

this assumes, then, that you have two other tables: tblCustomers and tblItems.

those tables would look something like:

tblCustomers
----------
CustomerID (Autonumber, PK)
CustomerName (Text)
.
.
.

and

tblItems
---------
ItemID (Autnumber, PK)
Code (Text)
Description (Text)
ListPrice (Currency)
.
.
.

now, when you have evil table-level lookups, the data stored in your FKs of your tblOrders table would normally STILL be the FK ID of the corresponding table, BUT the DISPLAY of the data is like a query - you just have to remove the 'combobox' in the lookup tab (i think the only other option is 'textbox' - which is what i think you meant in your post, but i feel i must clarify) of that table field and it will remove that 'display', but keep the ID.

the only time you'll need to update forms or queries is if you've imposed criteria on those tables based on the displayed data rather than the stored data. depending on the size and complexity of your database, you might be lucky to not have to do anything at all...

hope all that makes sense - it's a bit early in the morning still here!

edit: beg yours, there is also a 'listbox' option in the lookup tab for table fields... i've never used this but can only imagine it would cause horrendous headaches downstream.

 

[bali3377]

Thank you Robbie, I believe if lookup fields on tables are removed and put on the queries or forms it speeds up the the database. So I am going to have a shot at it.

 

[bali3377]

Thank you Agnieszka for your indepth response regarding F Keys and Primary Keys.
I started off using just autonumber for ID keys but quickly found them a pain in the neck. For instance
Transactions Table (Main Table)
Payment Method (normalization Table)
If I use the autonumber Id. and the subquery to get the payment method name, Whenever I queried this field I had to go looking for the autonumber id for the method i wanted to query for. So for simple tables I stuck to just the word as the ID and have enjoyed the database better for it.
Do you know the reasons for not using this method, so if I stick with it at least I will be making an informed decision. At some stage in the next year or two I might be moving my database to a server - that might or might not be relevant in this instance.
Thanks again

 

[bali3377]

PS I have used listbox on the lookup tab on tables but if I wanted to change or add to the list I found myself having to fish around in the design view of the backend of the database and I agree for that reason they are not that useful normally.

 

[wiklendt]

If I use the autonumber Id. and the subquery to get the payment method name, Whenever I queried this field I had to go looking for the autonumber id for the method i wanted to query for. So for simple tables I stuck to just the word as the ID and have enjoyed the database better for it.
Do you know the reasons for not using this method, so if I stick with it at least I will be making an informed decision.

in your query, have both tables, then, you can place the text into the query instead of displaying the ID. this is ok in a query.

e.g., see pic below. when you want to make a query to display info for a human, you can select the text portion of that table, instead of the ID.

of course, it's a good idea to leave the ID in there for when you need access to make its own connections, and the text can be there for the human side (e.g., a combobox often has both ID and text fields, with the ID field set to 0cm width) - but this depends on what the query is for.

once you have this, you can then apply criteria based on the 'text' field. (e.g., in the above example pic, i can limit to title "Mr" or "Miss", rather than ID "1" or "4"...)

normally, i don't make a separate stand-alone query for my comboboxes, i use the "built-in" option.

 

[gardengal444]

Please bear with me if my questions are juvenile. I am very very new at this (day 1) and am barely following along. I'm guessing at some of your terminology even!

Is this what you are referring to? Is this good or bad?

 

[SOS]

Is this what you are referring to?

Yes, it is.

Is this good or bad?

Bad

 

[gardengal444]

Amazing... first day first mistake...and I felt like I had done something very cool. I guess I better stop playing around and RTM! (and these forums)

I suspect that part of the reason I went in that direction is that designing the tables comes before designing the input forms. I wanted the datasheet view to be useful now for data input while designing the table.

 

[Rich]

You can display a form as a datasheet

 

[gardengal444]

so there's a bit of a catch-22? you have to create a form first before seeing the table. Or, is the form creation a simultaneous activity with table design? I guess its all in the planning.

My lookups were from a stand-alone table that had no relationships (so far). Was that a clue that I was going in the wrong direction?

 

[wiklendt]

so there's a bit of a catch-22? you have to create a form first before seeing the table. Or, is the form creation a simultaneous activity with table design? I guess its all in the planning.

My lookups were from a stand-alone table that had no relationships (so far). Was that a clue that I was going in the wrong direction?

you need to know first what data you are collecting, the flow of information and what data you want out the end.

then you design the tables (on paper) and determine what fields you'll need and how things fit together to make it work for access and for your data needs.

then you open the software, create the tables. then you create the forms. then you enter data.

troubleshooting is done throughout the process.

microsoft has tried to 'simplify' the PK/FK issue for newbies by creating a 'lookup' field at table-level. unfortunately this feature does NOT help at ALL.

the correct process (as i understand it) is to create the tables (as PK autonumber data type and FK 'number' data type), then the FORMS are given the lookups (as comboboxes). it is the COMBOboxes that are allowed to have fancy query type selection of records.

the idea is that users never see the tables and never use the tables directly. users enter data only via forms/queries - it's to prevent them messing with the structure of your database. (edit: the tables let access do its thing, the forms/queries let users do their thing).

 

[gardengal444]

well I thought I understood my data well, but I knew you were right that it makes sense to work with the Form right off the bat. I decided to play around with InfoPath instead of Access for forms creation. I suspect that was much easier than Access Forms. Working at the Form level does put a different spin on my initial view of the data . I did end up moving fields around a bit. Of course, I also ran into a different set of problems (infopath-specific), but eventually was able to populate a combo box from my Access DB. No SQL necessary (on my part).

Thats about as far as I've gotten so far. I generated an XML file for Access to import, but not much happened. I haven't found too much on the detailed how-to's of Infopath&Access2007, but that will wait for another day... this has been a PITA. Have to stop and regroup & rethink. and resist the urge to fall back to EXCEL!

 

[Lightwave]

Garden Gal

Really resist the idea of falling back on excel. I can guarantee that ANY proper database will be better than a spreadsheet database. If you can get over the initial problems Databases will mean you track things much easier and you'll spend a lot less time to do the same task than on a spreadsheet. Like any complicated tool initially it increases the pressures rather than decreases them.

As for not thinking you understand your data - I doubt that please don't let anyone in here convince you otherwise.

I totally understand where you're coming with the one line for each species in each location. Strictly speaking a new line for every plant is the theoretically cleaner option but if you are planting grasses or small annuals that doesn't sound practical in which case altering the quantity could be a work around.

At this early stage getting a prototype up and running even with mistakes will probably be much more effective than trying to see every possible problem before it actually arises.

I started out with databases just going for it and got it pretty wrong at the start but each wrong un led to a better un either that or a better understanding of the issues that made me better able to redo in end.

Understanding of databases and being able to refine your processes/ know what your assets are up to leads to a sense of control that really helps with peace of mind. A prize which I consider unbelievably useful.

 

[wiklendt]

I started out with databases just going for it and got it pretty wrong at the start but each wrong un led to a better un either that or a better understanding of the issues that made me better able to redo in end.

Understanding of databases and being able to refine your processes/ know what your assets are up to leads to a sense of control that really helps with peace of mind. A prize which I consider unbelievably useful.

hear, hear.

if i could quickly say:

while i wouldn't necessarily "fall back on" excel, you should look at the merits of using one over the other. obviously, if access could ALWAYS be implemented over excel, Microsoft wouldn't even develop excel at all and force everyone to use Access.

That being said, if you're using Office 2007, the excel program there had a lot of cool new features that does improve data management. some features were available in previous versions, but they are more accessible (pardon the pun) in the new version (such as creating "tables" to sort and filter data).

You need to know what you want to do with your data before you can make a decision to use Access or Excel (some projects require the symbiotic use of both).

Be prepared to make some wring decisions - as we all have - it's all part of the learning process.

 

[gemma-the-husky]

gardengal

its not that you are designing a form alongside the table

what it is is that in Access (unlike Excel) you very rarely see all the data. Generally you see only a selected subset of the data stored in the table. Bear in mind also that (particularly if you dont use table lookups), then the data you see in a number of fields will be meaningless

so you design a query that selects certain fields from that data, and a certain range of data - eg stuff in a particular date range, and to present it in a certain order. The query can also incorporate data from other tables which makes the content more informative.

Then you have a form to present this subset of data.

Typically you will have a number of forms and reports all providing different views of your raw dfata

The other thing with a form is that you can write code to limit and manage what people can do at certain points to control the integrity of your data.

-----------
So you need to structure your data carefully, to make it normalised - and then you will find that all the various forms you need to manage and present the system become easier to build.