Working With Long Lookup Lists

Nov 3, 2006

Have simple continuous form to create a pick list. One combo field looks up products from a list of about 5000 items.

Even though the combo homes in as letters are typed there is still a lot of typing needed to identify the target product. This is because many products have the same 10 to 15 characters to start, then have different endings.

It would be great if in adding a new item the combo remembered what was typed in the previous entry, as the picklist is built alphabetically.

Any ideas

View Replies

Help With Multiple Lookup Lists

Nov 9, 2004

Apologies - i'm a "rookie" at Access - but i have searched for a while now on the site for the answer to my question... I'm sure there is a simple solution somwhere here but i cant find it!

I am putting togther an venue database - with all the usual fields: Name, Address, Capacity etc... of which i have set up YES/NO, Free Text and Number fields. No problem!

I have also set up various lookup lists which work fine, apart form the venue "Suitability" lookup list.

The choices are:

Corporate
Private
Drinks Reception
Dry Hire
Music Event
Conference

Now, some venues may be suitable for SEVERAL of these uses - so how do i select multiple uses from this list as opposed to just one?

Many thanks in advance for your help - im learning fast! :rolleyes:

View 2 Replies View Related

Tables :: Lookup Value Lists In Table Fields?

May 16, 2013

I know experienced DB developers say never to use a lookup in a table for a foreign key and instead to use it on the form level. For this reason, I am going through my tables and removing lookups from the table level now.

But how do y'all feel about lookup value lists? (so the list is typed-in instead of looking up a table value)

If you think they shouldn't be used then what should I do instead? Should I make a table for the handful of values and link with a FK field?

Or is there a better way? I would rather not have to make a million tables for these short, stable value lists.

View 2 Replies View Related

Lookup Not Working

Feb 24, 2005

I have created a table which has a lookup box linked to another table with a bound column reference. The lookup works in the table.

On the form I have linked the original table to a query table which does a calculation based on the result of the lookup and another field.

Everything was working until I tried to put it in a report and deleted something by mistake. Now the drop down and calculations are visible on the form but I can't add a record or change the values in the drop down. The actual field appears blank. I have had to replace a lot of the relationships so I think the problem's something to do with that, but I can't seem to find it.

Does anyone have any idea what I can start looking at?

Sorry to be so vague, but I have tried to attach the database in a previous query and it is too big.

This is really frustrating 'cos I finally managed to get it working, then broke it, so any help would be gratefully appreciated.

View 3 Replies View Related

Lookup Wizard Not Working

Nov 3, 2005

I tried to chosee Lookup Wizard as data type in my table so I can have drop-down options from another table. However, I received an error message, "ActiveX component can't create object". What does this mean, please help :confused:

View 1 Replies View Related

Lookup Table Not Working In My Main Form

Jul 14, 2006

I have my main form which is connected to my main table - Query 1

I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.

In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.

I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.

Apparently not. It just has the #Name? in there.

Anyone have an opinion on this ?

I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.

Please help ! It seems so small and its driving me ceeerazy !

View 1 Replies View Related

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

Jul 24, 2007

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

View 4 Replies View Related

Saving Lists

Jan 23, 2008

Hello,

I have a main form where the user can click on a button that opens another form. In this form, the user can make selections from one listbox and they are copied to another listbox.
How can I save the contents of the listbox into a field that is part of the record on the main form? Can this be done?
I have a listbox on the main form where the selected items will be displayed and each record of the main form will have different values for the listbox.

Any ideas?

Thanks,
Mike

View 1 Replies View Related

Drop Down Lists

Apr 20, 2006

I am building a query for a university and the table i am working from has a column with every course run by the department.

How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.

View 1 Replies View Related

Lists Of Names

Sep 2, 2006

Hello all, I wonder if anyone can help me.

I have two queries which are both similar. They consist of a list of names with a �count� function to see how many times that the name occurs. Some of the names in the two lists are identical and some are different.

I am looking to create a query (or smoothing) that takes the two sets of numbers, matches them by the name (NameID). If the name does not exist in one of the lists then put a 0 (see example below). Once I have this I then want to create a stacked bar graph to show the results.

Example:

Smith, Iain23 7
Bowman, Dan12 2
Rixon, Simon06
Cooper, Lee10
Jones, Andy21

I am very new to MS Access so please make any explanation descriptive.

Any help with is will be very grateful.

Harold Clements

View 3 Replies View Related

Compare Two Lists

Mar 5, 2007

I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id?
For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?

View 4 Replies View Related

Combo Box Lists Everything

Jul 21, 2005

Ok! So I've binned the pivot table and gone for a continuos form option that allows the double click event that I'm after. A new problem has appeared.

On my main form I have 3 combo boxes (programme, product & company). When I click on any of the combo boxes i get a list of everything in the column it relates to. That works fine but it looks very cluttered. I'd like to have everything listed once only so that when i click on a programme it will filter the subform to show only companies working that programme.

Hopefully this little thingy I've put below will explain it a bit clearer

At the moment the programme combo box is displaying....

AAA
AAA
CCC
BBB
CCC
AAA
BBB

but i'd like it to display like this....

AAA
BBB
CCC

Every programme listed once only.

Any idea's folk's??? :confused:

View 6 Replies View Related

Combo Box Lists

Aug 5, 2006

I havea combo box on a form which looks up values from another table. The table containing the values which are looked up has another field where the data type is Yes/No. I want to limit the options in the combo box to just those which are set to Yes in the source table.

I know I need to use WHERE in the code for the combo box, but cannot find the correct syntax.

Please help!

Thanks,

Gary

View 3 Replies View Related

E-mail Lists

Apr 4, 2006

Hi, help please!

I have an access database with one column containing e-mail addresses.

How can I use them to easily send out multiple emails from this database?

If I copy and paste, the whole columns/cells are copied, but to copy them individually is clearly very time consuming.

All I need to do is somehow get them to a format that I can paste them into my e-mail address box.

Am I being thick or is this option not available?

Richdv

View 1 Replies View Related

Creating Call Lists

Nov 15, 2006

I am trying to create call lists which will compile one-to-many relationships.

For example, suppose I have 1000 employee records with the following fields:

Manager's Name
Manager's Phone Number
Direct Report's Name
Direct Report's Phone Number

Each Direct Report has only one designated manager, but managers may have more than one direct report. Further, for roughly half of the records, the manager and the direct report are the same person (independent workers).

When I run the report, I want it to print the Managers' information, and have the Direct Report information below. I have added an IIf query stating that if the Manager's Name is the Same as the Direct Report's name, leave all of those cells blank. When I create a report, however, the labels for Direct Report will still show but the data does not. It looks somewhat shoddy.

View 2 Replies View Related

Getting Email Lists From Access

Jul 19, 2007

I want to be able to create a email list from a query. I.E Say you need all the company's that meet certain criteria and then you have a list of company's complete with all the emails under 'email'. Is there a quick and easy way to export all these emails into the TO: field of outlook?

View 2 Replies View Related

Comparing 2 Lists - How To Setup

Jun 14, 2007

I would really appreciate if someone could help me with the following.

I have one list (3 million records)

Table = DNC
Field 1 - Area code
Field 2 - Phone number

I have another list that has say 1000 records

Table = Phone list
field 1 - area code
field 2 - phone number

What type of relationship do I need to setup in order for me to see which records in the Phone list table are not in the DNC table?

thanks for any help

Josh

View 1 Replies View Related

Cascading Lists On A Form?

Feb 6, 2006

Hello all,

I�m building a database for my studies and have hit a major snag with the final form build/design. The db is for a mug ordering system. The mugs are defined by�

The supplier � The Category � The Type � The Style
(whereby The Style is the final name for a mug).

The relationships have been designed as per the first attachment.
(see first screengrab)

Before I go on, do these relationships look realistic?

What I want for the final form to provide is 3 option boxes on the left, which would have to be setup as cascading fields. So when a Category name was chosen, it would then list the relevant Types in the next option box, and when the Type was chosen, the relevant Styles would be listed in the Style box. When the customer had chosen the final mug style, I would then assign a macro to copy the details to an adjacent form which would include customer details and final purchase cost.
(See attached second screengrab)

Can anyone please shed some light on this, particularly the cascading field�s option, as I am at my wits end trying to solve this?

I have looked at the help file �Cascadinglistsdemo� to no avail.

Cheers

SR

View 1 Replies View Related

Building Custom Lists

Mar 15, 2006

Hi,

My users wish to have a feature in my db that allows them to compare the records within it and view a report on it.

I have implemented such a form but the functionality is not great. Basically it's a form with two subforms - the top where you choose the projects and the bottom where you view a list of the projects you've chosen. You can search the top subform using combos and select all the records currently in view (a button runs an update query based on the combo box values)

To add them to the list the user is setting a yes/no field to true. So the top subform is where yes/no field is false (and bottom subform is where yes/no is true).

They can then report on this as the report has a criteria of where the yes/no is true.

This works brilliantly, so long as there is only one user!! Otherwise they would interfere with eachother's list-building.

Does anyone have any ideas for an alternative method so that multiple users can use it? I was thinkin of making tables or something to store IDs in but don't know much about this or where to begin!!

Hope that makes sense!

Thanks in advance,

Bobadopolis

View 1 Replies View Related

Look Up Lists And Primary Keys

May 3, 2005

I have a table (call it Table1) with a primary key that is a number and one other text field which is what the user would recognize the field by.

Another table (table 2) looks up to table 1. And yet another (table 3) looks up to table 2. I want the text name to show in the lookup list of Table 3 - NOT the number.

Right now, when I look at the sql results in the lookup field for table 3, the text appears rather than the number. But when I open table 3 and try to use the lookup list, the number appears.

Any ideas how to fix that? Do you need more details and sql?

View 3 Replies View Related

Tables :: Different Lists In The Same Column

Jul 11, 2013

I am looking to add 3 or more different list values into the same column on the same table.All lists are stored in seperate tables within the same database.

View 6 Replies View Related

Filtering Lookup Value Based On Other Lookup Values

Jul 27, 2015

I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".

Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.

View 4 Replies View Related

Query Lists Repeat Data

Nov 22, 2005

Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...

I have a query that pulls from 3 transaction tables.

tblTransactions - lists purchaser, amt tendered, other details
tblPurchased_Items - lists products and services paid for
tblTherapy_Treatments - lists treatments paid for

(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)

I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.

For example:
Joe Blow comes in and has physical therapy and buys 2 books.

My query will show (bogus fields for illustration purposes):
TransactionID Name TherapyID TherapyCost ProductID ProductCost
33 Joe Blow 10 180.00 15 12.00
33 Joe Blow 10 180.00 17 19.00

So, if I total TherapyCost it really gives me twice the correct amount.

Can someone point me to a solution?
Pardon my rank ignorance, yet again.

View 2 Replies View Related

Convert List In Query, Like IIF But With Two Lists...

Apr 14, 2006

Hi,

I need to output data in a specific format for a certain client..
for example where

Select * from Falls - would normally give me:

01/01/05 Joe Smith ICU, Assisted

I need in this intance to get:

01/01/05 Joe Smith 5, 2

where locations, or types etc are converted to a corresponding number.

I can imagine a query like:

Select FallDate, PatientName, somefunction([Location],(MS2,ED,OB,CAFE,ICU),(1,2,3,4,5)), somefunction([Fall_Type],(Accidental,Assisted,Unanticipated),(1,2,3))

Is there some more powerful version of IIF or way to write this in the query without having to create a report with a bunch of functions?

View 5 Replies View Related

Combine Multiple Properties Lists

Apr 2, 2008

OK, not sure how to search for this so I am just going to ask. I have two properties that send me excel sheets with the name of the employee, employee number, department, and title. What I would like to do is make this into one list. So just one table that would list these fields and maybe get a new field that marks what property that person works at.
Example
Jon Smith, 12345, admin, office ( this is from one list)
Jane Smith 54321, admin , office (this from the other list)

What I want it to look like (query)

Name, Emp #, title,department,property
Jon Smith, 12345,admin,office, prop1
Jane Smith,54321,admin,office, prop2

How would i go about doing this?

View 2 Replies View Related

Queries :: Drop Down Lists Or List Box

Dec 2, 2014

In my table I have the following fields that are tick boxes

Vee
Cathedral
Round Bilge
Bilge Keel
RIB
Semi-Displacement
Keel
Lifting Keel

Each record can take a couple of these options. For example record 1 could be ticked for a 'vee' and a 'cathedral' whereas record 2 could be ticked for all of them.

I have been trying to use these to make a search form. I want to be able to chose on ie. 'Round Bilge' and it will search for a record that is ticked 'round bilge'. I have done this for both a combo box and a list box and have used the formula for each field [Forms]![Boatsearch]![hulltype]

When I have clicked run query I have an error message saying: 'This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables'.

View 3 Replies View Related