ms access courses - Field lookup better way

Resolved · Low Priority · Version Standard

Tue 14 Nov 2006, 19:24 Delegate Mark said...

Mark has attended:
Access Intermediate course
Access Advanced course
Access VBA course

Field lookup better way

Lead in---
I have sucessfully made use of suplementary tables to supply lookup to make data entry easier (and of course to secure a limited set of entries in the field in question). However I am finding need for some fields for the data inputer to be able to select from entries already entered in that field whilst also able to enter a new entry. On a form used for the data entry I (thanks to your database intermediate course) was able to enter a combo box (and label) and make it look up values in the very filed and table for which data was being entered and have it update that field. It does what I was looking for except that if there are several entries of the same text/value it lists that value several times. So when the database is bigger it could mean so much scrolling that it is easier to just type it in!

Question---
Surely there is a better perhaps very simple way of achieving same field lookup (similar as works in Excel).

Thu 16 Nov 2006, 10:01 Trainer Richard said...

RE: field lookup better way

What you are looking for here is a grouped lookup. Ways you can do this is create a query of the tabel/field that you want to lookup. Then group the values together using the Group By function (Same button as the Autosum in Excel).

This will list unique values in the new query. (ie. no duplicates). You might also want to enable alphabetical sorting to ensure the list is always in the right order.

Then point the list to lookup the new query, and this will give you an up-to-date list.

Let me know if that helps...

Richard

Sat 18 Nov 2006, 22:20 Delegate Mark said...

RE: field lookup better way

Yes thank you very much indeed Richard, exactly what I needed to know. In fact now that I have seen what happens here I realise it was a simple seeming request but difficult to solve without help. This is because (for the benefit of other readers also) clicking the Totals symbol in the Query Design Toolbar (same symbol in other toolbars is no good) adds a row into the Query Design View table. I don't know why the 'Totals' row isn't just there anyway. For this solution of course I just left it selcted on Group By (although at first I thought I had to select one of sum, average etc and tried all the options---these will probably be useful as well another time).

I have tried clicking various other buttons to see if I could add any more rows to the query design table and havn't found any. ARE THERE ANY MORE?

Thu 30 Nov 2006, 16:02 Trainer Richard said...