LookUp? What Do I Use Instead?

[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.