Hello again!
How do I prevent the information in lookup columns from showing up in other tables, queries, etc... as numbers.
Example.
Lookup to Fruits. Where the records for fruits are Apple, Banana, Orange, and Pear.
The lookup column lists all the items {Apple, Banana, Orange...}, but in queries the data just appears as numbers {1, 2, 3}.
Many thanks for anyone's help in advance!
Is this possible.
I'm filling out a table, i want to populate six other columns via the pid i have typed into the column.
Table1
Table2 (lookup)
Table1
pid | pname | paddr | Pstate | psuburb | pcode
Table2 already has this information but i need to add it to Table1 via a lookup routine.
Any help would be helpful.
Thank you
In the tutorial listed here: [URL]....
It states after opening the database in Datasheet view :
In the Navigation pane, double-click the table in which you want to create the lookup column - this opens the table in datasheet view.
On the Datasheet tab, in the Fields & Columns group, click the lookup column.
And it show you some icons.
manipulate the data by changing one of the columns (Customer 1) to a Look Up Field. This has created two challenges for myself:
1) The data is gone ... oops. I can solve this one.
2) More serious, that column (Customer 1) will only display primary key numbers of the data. Before the corruption, I had a combo box on a form. This combo box took its values from a Table called CustomerTable, the value in this combo box was stored in the QuoteListTable. Before it showed names, now only primary key.
I have attempted to:
-change the look-up back to a regular field (got rid of the look-up)
-exported the table to a query, turned it back into a table, no change.
I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.
I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code
So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.
There is a subform called results, and I want to hide certain columns based on what tests are performed.
I tried using an If Then statement (code is being run on subform load)
If Me.Parent.fieldTest = "Test 1" Then
Me.Test1Col.ColumnHidden = False
Else Me.Test1Col.ColumnHidden = True
That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.
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?
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.
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
I have an Invoice & Address file I want to know how I can for example enter customer number in the Invoice file and get the information form Address file in the Invoice file.
Thanks
Hi all
Im trying to create a database where one field has a lookup to another field in another table (easy enough done) the the next field in the first table has another lookup, however the items the user can select change dependent on what was selected in the first feild eg:
SelectA -A1
-A2
SelectB -B1
-B2
So the user can only select B1 in the second field if SelectB was picked in the first field
Does anyone know whow this is done
Thanks
Chris
I wonder if anyone can help.
I have 2 tables.
Table1 (lookup table)has 2 fields: 'rate' and a 'minimum amount'. The rate applies to a range of values eg up to 1000,rate=20, up to 5000, rate = 22 etc.
Table 2 has 3 fields: 'UniqueID', 'current_rate', 'amount'.
The 'amount' field in table 2 increases on a monthly basis and when this reaches the next amount threshold (in Table1), the 'current_rate' needs to be updated in table 2 to the appropriate value.
I have searched this forum and Dlookup seems looks to be the only way to do it. However, I think this only returns one result and some of the suggested solutions use a form to input the 'amount', when I have many entries that need to be updated.
Any suggestions would be appreciated.
i'm sure this must be pretty simple but when i search for lookup i keep finding stuff about translating names into numeric id's which is not what i mean
what i have is a table TBL_EnergySavings with these fields
IDFuel, IDProperty, IDMeasure, EnergySaving
its a lookup table so that given the three ID numbers (of a type of energy saving measure like loft insulation, installed in a certain type of property which uses a certain type of fuel) we can say how much energy is typically saved in GWhrs
I have another table of installed measures (jobs) which has property type, fuel type and measure type amongst its fields and what i need to do is to fill in a column in that table with the energy saved by each measure
so how do i look up the relavant record from TBL_EnergySavings?
Hi, im fairly new to access. im creating my 2nd access project for my 2nd year of A levels and i need a bit of help with lookups on a form im using
heres a screenshot of the form
http://beta9.picturehost.co.uk/cat.jpg
What i want is for when you select an option from the category combo box, for the sub category to have limited options. Example: i choose Drinks from the main category combo, the sub category combo will only have a list of types of drinks available.
This has probably been posted before, but to be honest i didnt know what to search for.
Tell me if you need any more information
thanks
I have a form which is used to enter contact details into my database. This includes the organisation for which each contact works. We have quite a few contacts from each organisation.
To ensure that the organisation name is always entered exactly the same, I'd like to use a lookup box which allows the user to choose the organisation if it's already in the system, or to enter it if it's new and not already there.
Is it possible to have a lookup box which shows the values already entered in the field to which the input will be stored?
Gary
I am setting up a form for a database.
It's supposed to be a form for adding a new booking to a table.
Now there's one table with vessels and a different one with lines/deadlines.
Every vessel is on a line that has a certain deadline.
My question: Is it possible for a user to select the vessel from a list on the form, having the form automatically look up the deadline and showing this in a different part of the form.
The meaning is to add a new booking with vessel and deadline into one table...
Don't know if my story is understandble, if not, please ask me to clarify...
Lion85heart
I have a user options form linked to a table. The table is just one record with an option group on the form. the user selects which report template they'd like to use. the user closes the form down and resumes normal data entry.
when it comes time to represent the data in a report, i'd like it to display according to the template type the user selected in the options form. I have 3 different report templates. They all say the same thing... one is just prettier than the other!! Users like to customise!!
in effect, i'd like the database to check which report template has been selected from the options table, then open the appropriate report.
It sounds kinda simple but i can't quite figure the code. I think i need to use a lookup statement somewhere.
Any ideas are appreciated.
I want to put a lookup box on my switchboard where I can type in Company Name and automatically open up the form in the correct Company. I have tried to do this through a query but this just brings up a full-list, also tried a macro but this wouldn't work. Can anyone help please?
View 4 Replies View RelatedI would like to create a look up for a field from a table - that displays the OfficeID and the officename when it pops up, but only store the value of the id in the table.
OfficeID officename
1 Office 1
2 Office 2
My lookup Row Source is:
Code:SELECT office.OfficeID, office.officename FROM office ORDER BY office.officename;
Only the OfficeID seems to show in the lookup.
How would I get both to show, but only the OfficeID to be put into the field?
Hi all
Im trying to create a database where one field has a lookup to another field in another table (easy enough done) the the next field in the first table has another lookup, however the items the user can select change dependent on what was selected in the first feild eg:
SelectA -A1
-A2
SelectB -B1
-B2
So the user can only select B1 in the second field if SelectB was picked in the first field
Does anyone know whow this is done
Thanks
Chris
I have a 67 binary code string produced from a query which concatenates these 1's and 0's.
What I need to do is have access decide what a particular string value/range is and return the process name. e.g
11000000000000000000000000000000000001000010000000 00000000000000001. The process name would be COMP RESOLVED MSA
10100000000000000000000000000000000001000010000000 00000000000000001.The process name would be COMP UNRESOLVED MSA
00000000000000000111010000000110000000000000000000 00000000000000001
The process name would be MSA NEW
There could be about 60 different Process Names