Lookup table troubles in query

 I am using a database, that I admit I created before I was the wiser, and 
used combo boxes (lookup fields via the Wizard) in the database table.  The 
database is now filled with data.
The table has multiple fields, but there are four fields from which I would 
like to extract data and combine into one field.  Field one is a yes/no type 
and the other three consist of combo boxes.  In building a SQL statement in 
the query builder, I am unable to create a statement that uses data in the 
combo box fields as a filter--as you would expect.   It is unable to find the 
criteria that I specify in the query.  Is there anyway around this mess?
0
Utf
8/14/2007 8:08:06 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
542 Views

Similar Articles

[PageSpeed] 16

On Tue, 14 Aug 2007 13:08:06 -0700, Rocky <Rocky@discussions.microsoft.com>
wrote:

> I am using a database, that I admit I created before I was the wiser, and 
>used combo boxes (lookup fields via the Wizard) in the database table.  The 
>database is now filled with data.
>The table has multiple fields, but there are four fields from which I would 
>like to extract data and combine into one field.  Field one is a yes/no type 
>and the other three consist of combo boxes.  In building a SQL statement in 
>the query builder, I am unable to create a statement that uses data in the 
>combo box fields as a filter--as you would expect.   It is unable to find the 
>criteria that I specify in the query.  Is there anyway around this mess?

Create a Query joining the three lookup fields to their corresponding lookup
tables. There will be four tables in the query design window; you'll have all
of the fields available for searching.

             John W. Vinson [MVP]
0
John
8/14/2007 10:57:07 PM
See comments in-line below...

"Rocky" <Rocky@discussions.microsoft.com> wrote in message 
news:0C86FE04-30CC-4662-B0E5-66C1667C59E0@microsoft.com...
> I am using a database, that I admit I created before I was the wiser, and
> used combo boxes (lookup fields via the Wizard) in the database table. 
> The
> database is now filled with data.

Even if you created one/more tables with a lookup datatype, the value 
ACTUALLY stored in those fields is the underlying foreign key.  You could 
modify the table definition to change those fields to their underlying 
foreign key's data type without losing data. (Backup first, of course!).

> The table has multiple fields, but there are four fields from which I 
> would
> like to extract data and combine into one field.

Combining data from multiple fields into a single field is not considered 
good database design.  "One fact, one field" is the approach to follow.

> Field one is a yes/no type
> and the other three consist of combo boxes.  In building a SQL statement 
> in
> the query builder, I am unable to create a statement that uses data in the
> combo box fields as a filter--as you would expect.   It is unable to find 
> the
> criteria that I specify in the query.  Is there anyway around this mess?

Back to the top -- are you trying to use the values from the combo boxes IN 
THE TABLES?  If so, remember that the values stored are NOT the values seen. 
First change the field data types to match their underlying foreign key data 
types.  Then try doing the querying again.

Regards

Jeff Boyce
Microsoft Office/Access MVP


0
Jeff
8/14/2007 11:03:51 PM
Reply:

Similar Artilces:

lookup date, sum up total...
Hello all, Here is my situation: Worksheet 1 A B C D 1.)1/1/05 Data Data P&L 2.)1/2/05 Data Data P&L .. .. .. 365.) 12/31/05 Data Data P&L ............................................................ Worksheet 2 A B 1.) January =IF(Worksheet1=January, then sum all january P&L (Ignore other months)) 2.) February =IF(Worksheet1=February, then sum all February P&L (Ignore other months)) .. .. .. 12.) December =IF(Worksheet1=December, then sum all DecemberP&L (Ignore other months)) what formula do I need to...

In Access 2007 can you display the comments that go with a table?
In Access 2003 I would enter comments which I used often. How do I view those comments in 2007 version? Sharon, Can you explain to me what you mean by comments? Are you talking about comments you've placed in a 2003 db and how you can see these comments in 2007 or are you asking how you can place the comments in 2007? -- Maurice Ausum "Sharon" wrote: > In Access 2003 I would enter comments which I used often. How do I view > those comments in 2007 version? ...

lookup type metadata lost when usining Send To option
When I move a document between folders (even in the same library) any lookup type metadata gets lost. I have already reported this problem in a another post, a couple of weeks ago, but still have no solutions yet. I have tested the Send To option on three different WSS 3.0 and MOSS 2007 farms and I get the same result. I can't find any post reporting such issue. Has anyone succeeded in using Sharepoint' Send To option to move documents with custom content type, having lookup type metadata, whitout losing metadata values? When moving items inside of SharePo...

Query Help 12-07-09
I need help on what I think should be easy query but I'm not getting it. I have two tables Parts, and Assemblies Parts has the fields as follows ID - Long PartCode - Char :If it is a P it is a part if it is an A it is an Assembly Active - Boolean Description - Text Assemblies has the fields as follows ID - Long Assembly ID - Long : this is the ID of the Assembly Part ID - Long : This is the ID of the parts and/or Assemblies used to make up the assembly What I want to do is have a query that will show me all active assembly parts that are not used in another active a...

Pivot Table with MS Access as DataSource
Hello all. Thanks for the help in advance. I am trying to use a UNC to pull data from a MS Access database as a external datasource for a pivot table in MS Excel 2003. I can actively pull the data, since I have a network drive mapped to the location, but others who have the drive mapped to another letter are unable to open the document and refresh. I am trying to figure out the best way to create this pivot table utilizing the shared network UNC. Please help! Regards, Me Hello, I would use a SQL query to pull the data out of the Access db. Then everyone can push...

Problem with pivot table on large spreadsheet in Excel 2007
I have a worksheet with 320,000 rows. I make a pivot table on one of the columns and then generate another column with sums of the values in that colum. I then try to sort on the sum column and Excel hangs, consuming 50% of the CPU in a dual-core processor. The only way I can get out of this is to go into Task Manager and kill excel.exe. What's interesting is that when this happens, excel is using 256 MB of system memory. The total memory is 2 GB; there's clearly more than 256 MB available, but something seems to think that it can only use 256 MB. Is there a setting somewhere th...

Setting recordsource to secured table
Hi, I have a form that I am trying to re-use to display data from 2 different tables and the information is changed in the click event from the menu that selects the form and it sets the recordsource, label captions, and textbox sources. This works fine on my userid (the owner), but there are no permissions on those tables for other user groups. I thought I could get around that using the "with owneraccess option", but it doesn't seem to be working. I have confirmed that the owner of both tables has full permissions. What am I doing wrong? Thanks, beth Here is a snippet o...

add fields to query in closed data base
thank you Can I change (record source ) for field in report in (close) data base by code using current (open ) data base. Field1 record source Or field source = Playername Change to Field1 record source Or field source =PlayerN --- Is possible to add field to query in closed data base by code using current open database notes query is created in close database the query contain fields I want add field to this query ...

Using linked tables in forms
Hello, I have 3 tables. Stock Accesoires and a table to link them as a record in stock can have multiple accesoires. Now I want a form where I can see a record from the table Stock, with all the Accesoires that belong to that record. How can I do this? Is this possible? It doesn't matter in what kind of object the data is displayed, I just need it displayed. The relationship looks like this, for if it wasn't clear: Stock.ID -- linkTable.StockID linkTable.AccesoiresID -- Accesoires.ID I hope someone can help. I thought it would be Record -> accessories and put the stock ...

Recipient address rejected: User unknown in virtual mailbox table?
I'm having issues with sending an email from an outside service to my Exchange server email addresses. When the outside service attempts to send the email it gets the following error returned: Error: 550 : Recipient address rejected: User unknown in virtual mailbox table The outside service believes this is being returned by Exchange. Does this sound familiar to anyone? On Sat, 20 May 2006 06:57:01 -0700, Achilles Heal <AchillesHeal@discussions.microsoft.com> wrote: >I'm having issues with sending an email from an outside service to my >Exchange server email addre...

How many items can a pivot table hold? i.e. a worksheet taps out .
Does anyone know how much data a pivot table will hold???? I can get it to accept 30,000 items but not 50,000... What are it's constraints? The following MSKB articles outline PivotTable limitations for different versions of Excel: XL2000: Limits of PivotTables in Microsoft Excel 2000 http://support.microsoft.com/default.aspx?id=211517 Limits of PivotTable Reports in Microsoft Excel 2003 http://support.microsoft.com/default.aspx?id=820742 j wrote: > Does anyone know how much data a pivot table will hold???? I can get it to > accept 30,000 items but not 50...

Table default value question
Can a table default value be the value from a loaded form? Here is my dilemma, whenever I create a new record via a form, I have codes to detect the user’s LAN ID and store this information in a field called CreatedBy. However, now I can collect data via e-mail and automatically upload the information into Access and you already know what there will not be a CreatedBy data. Therefore, I think if I could place the CreatedBy default value directly from the table to look up the user name already loaded in the database “Welcome Screen” that will save user LAN ID regardless how the da...

Exchange 5.5 Attribute Query
Hi, I’m in the preparation stage of Migrating Multiple Exchange 5.5 Site to Single Exchange 2003 Site. I would like to query the Account’s Alternate Recipient(under delivery option) and Forwarder Field in Ex55. Is there any tool that I can use to perform such query? You want to use Header.exe and then export pointing to that file. The tool can export every attribute of the 5.5 directory. See: http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.exchange.admin&mid=844ef2b2-8020-4265-a8a3-1ce73ae8722d&sloc=en-us "pte&quo...

Money 2004 --> Money 2007 == nothing but trouble
I can log in and do anything I want manually with Money 2007 but now that it's hooked to Windows Live I can do very little online. The conversion process from 04 -> 07 is poor and now I can no longer download transactions from my bank. The only reason I upgraded to 2007 is that 2004 was telling me that online services were no longer supported in that version. What a crock of ****!!!! Poor, poor, poor, Microshaft! You force me to upgrade to a version of your software that is severely handicapped and now what? Do I have to pay you money to get it working by a support call? I see th...

Conversion of MS Access queries to SQL queries
Hi All, Anyone knows of a utility or software that will convert MS Access queries to SQL Queries. I need to convert my existing ms access queries to SQL queries as i need to use them in a software which only takes SQL queries. I tried to copy and paste "SQLVIEW" of the queries from MSaccess but they dont' work as they require modifications. I don't have much knowledge of SQL queries, wondering if someone can point me in right direction. Thx In article <1181230883.726006.250120@q66g2000hsg.googlegroups.com>, raonhassan@yahoo.com says... > Hi All, > Anyone knows...

Creating a leauge table
Hi all I'm doing a dream team for all the guys at work for Euro 2004. I have set up a spreadsheet working out all the players scores etc. I' just wondering how I could go about creating a sort of 'League table based on each person's scores, in the usual format of highest score a the top - lowest at the bottom. I want it to work out the positions i the chart accordingly to the updated scores. Any help or advice would be very grateful. Thank you all in advance. And -- Message posted from http://www.ExcelForum.com Andy, There is an example at www.xldynamic.com/source/xld...

Tab Order when Word Table Rows are hidden
I have created a Word Form with 4 tables in which based upon certain values selected rows are hidden. In my testing I noticed that when I tab from one field to another that the tabbing appears to be going through ALL of the hidden form fields before finally arriving at the unhidden form field. Is there any way to control the tabbing when you have rows hidden in a table? Thanks in advance. If you are going to use Tab, no. It will tab through the formfields, regardless of whether they are hidden text, or not. Worsty wrote: >I have created a Word Form with 4 tables in which...

Lookup formula help #2
Alraedy posted in microsoft.public.excel.functions newsgroup. Server is returing duplicate post error so I am posting my corrected version of the question here. Sorry, I messed up and here is what I intended (hope it will come all right this time). In a table, three columns contain the following: Col A Col. B Col. C Row 1 Employee Name 1% 7% Row 2 Williams 5 Blank Row 3 Peter Blank 42 Row 4 David Blank Blank and so on. For each Row, both Col. B and C can be blank but ...

Help with pivot table
I want to know how many employees I have working in a given half hour with the state licensure they have. I have a table that has my employees, state licensure and each individual half hour segement they are working. When I view as a pivot table, the results are giving me all that have that specific state licensure. Example: @ 6am, I have one person with Alaska licensure, but the pivot table say 11 (that is my total staff with Alaska) How do I make this happen? Thanks in advance. HOW TO GET FURTHER HELP WITH A WORKBOOK For further help with it why not join our forums (s...

need help with query
Dear Guru, I need help with sql query that combine two tables, and filter. I have following tables: tblInbox, tblInboxThread, tblInbox ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate tblInboxThread ID, iInboxThread, ThreadUserFrom, ThreadUserTo, vThreadMessage, dCreateDate where the iInboxThread is referring to ID of tblInbox =============================================== I would like to extract the most recent message (by max creation date) to be displayed in inbox for each inbox ID Example of table contents: tblInbox ID UserFrom UserTo...

Bug with cell merging in tables using Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Once you have merged cells in a table in Word 2008 using Applescript, any further reference to the table's cells in the script produce an error. <br><br>As an example, open Word 2008 and run this script: <br> tell application &quot;Microsoft Word&quot; <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;make new table at active document with properties {number of rows:4, number of columns:4} <br> &nbsp;&nbsp;&nbsp;&nbsp...

Monthly transactions in query
Please help me in creating Query form below records: RecDate Details Debit Credit 01-Jan-07 Sales 2 03-Jan-07 Sales 3 10-Jan-07 Payment 5 12-Feb-07 Sales 9 15-Feb-07 Sales 10 with query Like this: RecDate Details Debit Credit 01-Jan-07 Sales 2 03-Jan-07 Sales 3 10-Jan-07 Payment 5 31-Jan-07 Month Total 01-Feb-07 Balance B/F 12-Feb-07 Sales 9 15-Feb-07 Sales 10 28-Feb-07 Month Total 01-Mar-07 Balance B/F in my table i have transactions for all months; i want to make month total and for new month i want to carry the balance as the beginning date of the month. All yo...

Pivot table has source data has exceeded 65536 records type misma
once my report exceeded 65536 this command no longer works yielding an error type missmatch. the command worked fine until the size exceeded 65536 and I know RWS variable is where I am having problems. But I do not know why or how to overcome the problem. below you will find the code which is broken. Workbooks(SSRname).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)), Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1), TableName:=&qu...

Update Supplier ID By Deparment ID SQL Query
Hello, I am trying to figure out an SQL query to update supplier ID. I purchase all items from 1 department from only 1 supplier. So I need to update all items in Department X to Supplyer Y. -Michael Santore Test on a test system before doing the following on your live system. Always backup your live system before performing any updates. Perform the following SQL queries in Store Operations Administrator ====================================================================== Step 1. Find the DepartmentID by running this query. SELECT * FROM Department Step 2. Find the SupplierID by ...

Pivot Table Field List "crashed"
For lack of a better word. So, I basically live in Pivot Tables in Excel, so I'm a little distressed. When I go into a file and create a pivot table (or I go into an existing file with a pivot table, the field list does not show up. This isn't a "just right click and select field list from the menu" type of not showing up. Rather, the outline of the field list box shows up, with heading that says "Field List", but no list inside. Instead it shows whatever was in the background when you asked for the field list (usually it shows the cells behind it, but I've had...