Tables/Queries

Hello,

I've seen some posts regarding a similar problem to what I'm having but cant 
find the resolutution!

I have a database with tableA which contains basic inventory info.  I have a 
field in tableA named location.  There is a second table (tableB) which has 
a list of all the locations our company delivers to.

I've been warned to stay away from lookup fields, so I'm using combo boxes 
on the form to look up the location and then populate the location field in 
tableA when the value is selected.

Here is the issue (and again, I've found some posts similar):  Running a 
query seems to be an issue.  If I enter NEW YORK, I get no results.  And the 
other strange thing is that the location field in table A does not contain 
the state name but a number.

I need to be able to keep the locaiton field a drop down so my users can 
select a state, but I must be able to report off of that info by entering 
the name of a state.

How can I achieve this??

Thank you everyone. 


0
Scott
6/17/2007 3:14:54 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
632 Views

Similar Articles

[PageSpeed] 51

You generally use a control on a form to filter a query. Use a combo box as 
this control. You can display the name and bind to the number.

-- 
Duane Hookom
Microsoft Access MVP


"Scott Nash" wrote:

> Hello,
> 
> I've seen some posts regarding a similar problem to what I'm having but cant 
> find the resolutution!
> 
> I have a database with tableA which contains basic inventory info.  I have a 
> field in tableA named location.  There is a second table (tableB) which has 
> a list of all the locations our company delivers to.
> 
> I've been warned to stay away from lookup fields, so I'm using combo boxes 
> on the form to look up the location and then populate the location field in 
> tableA when the value is selected.
> 
> Here is the issue (and again, I've found some posts similar):  Running a 
> query seems to be an issue.  If I enter NEW YORK, I get no results.  And the 
> other strange thing is that the location field in table A does not contain 
> the state name but a number.
> 
> I need to be able to keep the locaiton field a drop down so my users can 
> select a state, but I must be able to report off of that info by entering 
> the name of a state.
> 
> How can I achieve this??
> 
> Thank you everyone. 
> 
> 
> 
0
Utf
6/17/2007 3:35:00 AM
On Sat, 16 Jun 2007 23:14:54 -0400, "Scott Nash" <sconash1@hotmail.com> wrote:

>I need to be able to keep the locaiton field a drop down so my users can 
>select a state, but I must be able to report off of that info by entering 
>the name of a state.

The Report itself should be joined on a query joining your main table to the
location table, using the location name from the latter. As a search
criterion, you can use an unbound Form (let's call it frmCriteria) with a
Combo Box (cboLocation let's say)  displaying the human-meaningful name but
using the ID as its bound column; your query criterion would be

=Forms![frmCriteria]![cboLocation]

and the form would have a command button to launch the report.

             John W. Vinson [MVP]
0
John
6/17/2007 4:34:53 AM
On 17 Giu, 06:34, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Sat, 16 Jun 2007 23:14:54 -0400, "Scott Nash" <scona...@hotmail.com> wrote:
> >I need to be able to keep the locaiton field a drop down so my users can
> >select a state, but I must be able to report off of that info by entering
> >the name of a state.
>
> The Report itself should be joined on a query joining your main table to the
> location table, using the location name from the latter. As a search
> criterion, you can use an unbound Form (let's call it frmCriteria) with a
> Combo Box (cboLocation let's say)  displaying the human-meaningful name but
> using the ID as its bound column; your query criterion would be
>
> =Forms![frmCriteria]![cboLocation]
>
> and the form would have a command button to launch the report.
>
>              John W. Vinson [MVP]

www.carlogiove.altervista.org

0
CANTANTE
6/17/2007 10:03:03 AM
Thanks all!


"CANTANTE DOCK UNA VERA SCOPERTA!" <carlogiove@alice.it> wrote in message
news:1182074583.537968.277860@g4g2000hsf.googlegroups.com...
> On 17 Giu, 06:34, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
> wrote:
> > On Sat, 16 Jun 2007 23:14:54 -0400, "Scott Nash" <scona...@hotmail.com>
wrote:
> > >I need to be able to keep the locaiton field a drop down so my users
can
> > >select a state, but I must be able to report off of that info by
entering
> > >the name of a state.
> >
> > The Report itself should be joined on a query joining your main table to
the
> > location table, using the location name from the latter. As a search
> > criterion, you can use an unbound Form (let's call it frmCriteria) with
a
> > Combo Box (cboLocation let's say)  displaying the human-meaningful name
but
> > using the ID as its bound column; your query criterion would be
> >
> > =Forms![frmCriteria]![cboLocation]
> >
> > and the form would have a command button to launch the report.
> >
> >              John W. Vinson [MVP]
>
> www.carlogiove.altervista.org
>


0
Scott
6/18/2007 6:44:31 PM
Reply:

Similar Artilces:

Pivot Table Help #3
I have a lot of data that I am trying to analyze with a pivot table and am not sure how to go about it. Columns are (1) District (2) Store # (3) 2003 Score - these are #s or text ("incomplete") (4) 2004 Score - these are #s or text ("incomplete") For each district, I am trying to find out 3 things: (1) % of stores incomplete (2) Average score for 2003 & 2004 - I've got this one working properly (3) % change between 2003 & 2004 I can successfully analyze the data in a spreadsheet but there is too much to go through and thought a pivot table was the way to ...

Subtract colums in pivot table
I have a pivot table that has the following characteristics (Excel 2007): -rows (down the left) are values: "# Employees", "Total Pay" -Columns (across top) are Dates I want to calculate the difference between different date columns. Example: I have: Date 5/23/2010 5/16/2010 5/24/2009 Total # Emp 10 15 5 30 Pay 1000 15000 500 16500 I'd like: Date 5/23/2010 5/16/2010 5/24/2009 Total Vs. Last Wk % Change Vs. Last Yr % Change # Emp 10 15 5 30 -5 -33% 5 33% Pay 1000 5000 250 6250 -4000 -80% 750 15% Can anyone tell me how to cre...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Renaming table in a dB
Is there a short way in which i can modify all references to a table after i rename it? Or would i have to open every query and form and manually change the table references?Thanksramesh Access doesn't provide a way to do this.There are commercial products that do, e.g.: http://www.speedferret.com/-- Allen Browne - Microsoft MVP. Perth, Western AustraliaTips for Access users - http://allenbrowne.com/tips.htmlReply to group, rather than allenbrowne at mvps dot org."Ramesh" <ramesh2020@gmaildotcom> wrote in messagenews:uGgN$EuZHHA.4000@TK2MSFTNGP02.phx.gbl...> Is the...

Dummy series and data table
Hi, I have a chart that presents 2005, 2006, 2007 summary data as a column chart and then 2007 by month as a line. To show the yearly data I have a yearly category, after which I have individual months where the yearly data is zero - sort of like a dummy series - because I only have one value for them. The 2007 detailed data has zero in the yearly column but all the individual values in the monthly columns. It worked fine until I was asked to add a data table to the chart. Now, since it has 2007 twice - once as the summarized for the year and the other as all these individual months - some us...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

Sumif across a table
I am looking for a function that works using a =sumif function to add things that are not in a range that are next to each other as seen below the letters in () are the columns that the values are in... so I am looking for a sum in column A "X" of the total work out time if the appl column is "Y" total work Running (D) Walking (F) Elliptical (H) out time (C) Appl (D) Time (E) Appl (F) Time (G) Appl (H) Time (I) X Y 20 N 0 Y ...

Aging report table for accounts receivable
Can somebody tell me what is the table for a/r aging..I want to make query/view in sql server 2005 Michael, You need to use RM20101 and RM10201 tables. You need to use date functions to get the aging for your view based on document date or due date field. If your aging is setup to be by Doc Date, Consider Doc Date and If it is due date, you should be taking it by Due Date. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Michael@nyresume.com" wrote: > Can somebody tell me what is the table for a/r aging..I want to make > query/view in sql server 2005...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

pivot tables #3
I am trying to change the order in how the tables display. I don't want an accending or decending alpha order as it is set up now. I would like to pick and choose how I want them listed. How do I change the order? ...

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

pivot table, How to add 1 column
I just need to add one column only but pivot table create another one? I thinkc because I have a column with 2 parameters. I can turn on / off with the field drop down menu How can I get around with this problem? Thanks Daniel ...

delete dupl from 1 table that match table 2
I have 2 tables joined by an acct # but diff data in the 2 other columns. I want to delete the row of information from table A that have a matching acct # in table b. ie: appl acct # amount (table a) appl acct # amount (table b) b 1234 $1.00 b 1234 $5.00 c 111 $1.00 c 12345 $5.00 c 1001 $2.00 c 1001 $3.00 want to delete rows from table a. for accts #1234 & #1001 DELETE [Table A].[Acct #] ...

Using formulas for pivot table
I know you have the count field but is there away to input a formula? For example customer ordered 25 cases and each case weighs 4 lbs and the end result would need to be total pounds ordered. Thanks! If you do the calculation in the pivot table you may not get the result that you expect. If possible, add a field to the source data, and calculate the order total there. Then, add the OrderTotal field to the pivot table's data area. tskb wrote: > I know you have the count field but is there away to input a formula? > For example customer ordered 25 cases and each case weighs 4 lbs...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...

Hide Navigation pane during linking & unlinking tables
I have tables which are linked during statup of my access application. However whenever the linking happens, the naviigation pane(which is setup to be hidden is displayed. Please let me know how can i fix that I haven't seen this problem using A2007 on Vista or Windows Server 2008. Perhaps you have a problem with a different operating system? Perhaps the relink code is showing the navigation pane? Perhaps you would paste your relink code to help us answer your question. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Pradeep" <agarwallp@g...

Published pivot tables, with interactivity, only work once
I am using Office 2000 & Windows 2000. I publish a pivot table, with "interactivity", to a server via a mapped drive. If I ask, when I Publish, that it 'show in browser', all is OK, but that is about all that seems to work. I have tried both extracting directly from an Access database and building & saving a cube, then working from that. I have saved the various elements both on the client PC & the Web server. I have even done the whole thing on the server. All very confusing & I cannot really identify the root problem, but the 2 main issues whe...

Help with Lists (tables), Filters, & Worksheets
I'm hoping that I'm just missing something and somebody out there can help me. I'm using Excel 2007, but I'm trying to do something that would also be backwardly compatible. I've used the table (list) function to create a list of campers & associated data for my son's summer camp. What we want to do is have tabbed worksheets within the workbook that shows that data in different views. So, for instance, the first tab is the master list, but the second tab is filtered for the kids in the 1st session youngest class, the third tab is filtered for the kids in the...

Pivot Table problem
hi I have one doubt. I have Month & week date as asingle dimension. Week date is the child of Month. using a macro can i unselect the all the weekdate whose year is 2003 ? below is the current set up MONTH WEEKDATE AMT JUNE 01/06/2003 23 06/06/2004 56 11/06/2003 91 11/06/2004 78 JULY 01/07/2003 23 06/07/2004 34 11/07...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

contribution percentage column in pivot table
I have a pivot table I've created in Excel from 120,000 lines in M Access. It's basically across time at the top level, with months from Jan t Dec, starting: January Category A Category B Person A 10 5 Person B 7 12 I need to get a running percentage contribution column for each MONT from each category, ie a column next to Category A showing 67% (bein 10/15) and again next to Category B for 33%; without it being the tota of that entire row. Help!! -- kpritchet ---------------...

table doesn't update until after I press the escape key
I have a form with subform working but with one issue - when I enter data into a row I start on the next row, but get error about duplicate key. I press the escape key, it clears the data I just tried inputing, and it updates the key field. The key field causing the error uses this in the default value property field- =Nz(DMax("SongID","MusicOnPC_Songs"),0)+1 Is there something missing to make it update after I tab out of the last field for that row, and move into the next row? try removing the expression from the DefaultValue property of the SongID field. instead, ...