How to return preset values based on query result?

Greetings,

I have a database table that has a row called "Company", which may
return different variations on a company name, depending on how it was
entered. For example, it may return one of the following:
WidgetWerks, Inc.
WidgetWerks, Inc.
Widget Holdings Corporation
WidgetWerks Co
WidgetWerks Ltd.
WidgetWerks Canada
WidgetWerks

For grouping and simplification, I would like to add another row to my
query results called "Simplified Company", where it would just return
"WidgetWerks" if "Company" is any of those above values.

In other databases, I might use CASE for this. Any tips on how I could
do it in Access?

Thanks,
N. Lee
0
N
3/11/2008 6:37:11 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
459 Views

Similar Articles

[PageSpeed] 33

You can use a translation table.


Aliases  ' table name
CityName    AliasOf    ' field names
--------------    ------------
Constantinople    Istanbul
Byzance              Istanbul
Bytown                Ottawa
New-York            New York
                                ... data sample




----------------------------------
SELECT a.cityName, NZ(b.aliasOf, a.cityName) AS translated
FROM myTable AS a LEFT JOIN aliases AS b
        ON a.cityName = b.cityName
----------------------------------



will display the 'translation', if one is known, in the second column. Sure, 
the table Aliases has to be filled with all the known alias you will be 
using.






Hoping it may help,
Vanderghast, Access MVP





"N L" <cheesekeeper@gmail.com> wrote in message 
news:34048250-69e9-4425-a3c8-9f421379f345@h11g2000prf.googlegroups.com...
> Greetings,
>
> I have a database table that has a row called "Company", which may
> return different variations on a company name, depending on how it was
> entered. For example, it may return one of the following:
> WidgetWerks, Inc.
> WidgetWerks, Inc.
> Widget Holdings Corporation
> WidgetWerks Co
> WidgetWerks Ltd.
> WidgetWerks Canada
> WidgetWerks
>
> For grouping and simplification, I would like to add another row to my
> query results called "Simplified Company", where it would just return
> "WidgetWerks" if "Company" is any of those above values.
>
> In other databases, I might use CASE for this. Any tips on how I could
> do it in Access?
>
> Thanks,
> N. Lee 


0
Michel
3/11/2008 8:43:04 PM
On Mar 11, 4:43=A0pm, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:
> You can use a translation table.
>
> Aliases =A0' table name
> CityName =A0 =A0AliasOf =A0 =A0' field names
> -------------- =A0 =A0------------
> Constantinople =A0 =A0Istanbul
> Byzance =A0 =A0 =A0 =A0 =A0 =A0 =A0Istanbul
> Bytown =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Ottawa
> New-York =A0 =A0 =A0 =A0 =A0 =A0New York
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ... data s=
ample
>
> ----------------------------------
> SELECT a.cityName, NZ(b.aliasOf, a.cityName) AS translated
> FROM myTable AS a LEFT JOIN aliases AS b
> =A0 =A0 =A0 =A0 ON a.cityName =3D b.cityName
> ----------------------------------
>
> will display the 'translation', if one is known, in the second column. Sur=
e,
> the table Aliases has to be filled with all the known alias you will be
> using.
>
> Hoping it may help,
> Vanderghast, Access MVP
>
> "N L" <cheesekee...@gmail.com> wrote in message
>
> news:34048250-69e9-4425-a3c8-9f421379f345@h11g2000prf.googlegroups.com...
>
>
>
> > Greetings,
>
> > I have a database table that has a row called "Company", which may
> > return different variations on a company name, depending on how it was
> > entered. For example, it may return one of the following:
> > WidgetWerks, Inc.
> > WidgetWerks, Inc.
> > Widget Holdings Corporation
> > WidgetWerks Co
> > WidgetWerks Ltd.
> > WidgetWerks Canada
> > WidgetWerks
>
> > For grouping and simplification, I would like to add another row to my
> > query results called "Simplified Company", where it would just return
> > "WidgetWerks" if "Company" is any of those above values.
>
> > In other databases, I might use CASE for this. Any tips on how I could
> > do it in Access?
>
> > Thanks,
> > N. Lee- Hide quoted text -
>
> - Show quoted text -

That's terrific. Why didn't I think of that?

Thanks.
0
N
3/12/2008 1:29:10 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Eliminating Duplicates from a Query Access 2007
I've run a query that captures the data set I want. Now I want to eliminate Duplicate names and retain only the unique names. These are text fields, can I do this in the Criteria in Design View? Many thanks. (B^>)-]=[ hi, On 01.04.2010 16:46, WSR wrote: > I've run a query that captures the data set I want. Now I want to eliminate > Duplicate names and retain only the unique names. > > These are text fields, can I do this in the Criteria in Design View? Take a look at the Unique Values property of your query. mfG --> stefan <-- Und...

Zero-value Budget Column in FRx SP11
Hello all, We have just resolved an interesting problem that one of our customers was encountering after upgrading from FRx SP9 to SP11, that I'd like to share with you all, along with our resolution. Bear in mind, I am not recommending this resolution to you, so much as providing it to assist you with your own enquiries. The symptoms of the problem are as follows: A report contains a two columns, a budget column and an actuals column. Running the report on FRx SP9 produces the correct figures. Running the report on FRx SP11 produces correct actuals, but the budget figu...

Chart Values and X axis values from VBA
Hi, I'm creating a series of charts based on data in many adjacent columns based on Name defined Ranges. However, I have to hardcode the Workbook and Sheet names that are used for the source names. My piece of code is: With ChtObj.Chart.SeriesCollection(jbl) .Values = "=Book3.xls!Sheet1_COL_" & ColAry(z) .XValues = "=Book3.xls!Sheet1_Date" End With I want the code to handle the workbook & sheet names more dynamically to accept a variable for wbk name or sheet name (or number). I can't get any syntax to work. Experts Help! Thank you. Kohai ...

Knowledge Base Article
Hello, I would like the Knowledge base article (if one has been selected) to automatically fill a tab that I have added specifically for it. At the moment, it does not auto show and is only about 4 or 5 lines in height even though the rest of the tab is empty. Any ideas? Thanks Steve ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Merge/consolidate text cells based on unique keys ?
Hi all. I hope someone can help me out. I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'. Every time the accounting dept. generates an invoice, a new row is added in this worksheet. Obviously this is chronological not per Client #. But for the sake of simplicity, let's assume the worksheet is already sorted by Client #, like so: A B Client # Invoice # 231 5929 231 4358 231 2185 231 6234 464 1166 464 1264 464 3432 464 1720 464 9747 791 1133 791 4930 791 5496 791 6291 989 8681 989 3023 989 7935 989 8809 989 8873 My goal is to achieve...

.NET equivalent to XSLT value-of select
This seems like it should be really easy, but I cannot seem to make it work. I am trying to retrieve the text value of an element named "child2Element" from an XML file in a .NET (v 1.1) with an XPath expression. In an XSLT document I would use <xsl:value-of select="rootElement/child1Element[@childId='110']/child2Element"/> (and this does work fine in a transform). But I cannot seem to find the right method or object in .NET. There seem to be a number of classes that can use XPath, but I can't figure out which one I have to use to get just the te...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

two quick queries :-)
I have a few vb scripts that I'd like to store centrally, so that I can use them in multiple workbooks by multiple users on a network. Can someone tell me how I can store them so that they may be called. Secondly a number of my scripts need to use the ame variable. For example I store a filing location in a cell and three different scripts access it. Can I store this as a global variable that can be used by any script in the workbook? Thanks you're pretty much screwed.. if i were you i would put your efforts into Access where it is a lot easier to centralize logic like that ...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Formatting query adding , & " "
Using Excel 2002. I have a column list of 3000 items. They are of varying lengths the maximum being 11 characters. I would like to take this list & be able to change it from vertical to horizontal. I would like to add a comma in between each entry with no extra spaces. I would like to add " " around each entry, but the quotes must all be in the same fixed space. ex: I have an entry that is 11 characters long. it should have quotes. I also have an entry that is 8 characters long, the quotes on this shorter entry must be in the same place as if it were an 11 character entry. ...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Deleting a note keeps the value in the NOTEINDX field
I just discovered that if you delete a note from a document (in my case, a cash receipts document), the system will delete the actual note record from the SY03900 table but will leave the original NOTEINDX value on the document record intact. Is this normal behavior? I would think the system should clear out the value in the document record since the note no longer exists. Referential integrity! -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 Yes, this is normal for GP. The record is created the first time the user clicks the note icon and "bookmarks...

Reference cell values from other sheets in a function
I am trying to do something that seems like it would be quite simple, and yet I cannot get it to work. I have some data in separate work sheets (one sheet for each month), and a yearly summary sheet. I have a DAVERAGE function in each month. I am trying to take that DAVERAGE on each monthly sheet and make an AVERAGE function in the year summary page referencing each DAVERAGE cell on the monthly sheets. I enter =AVERAGE( into the cell and click over to the other sheets where I need to pull the info from. I click on the DAVERAGE cell that I need to reference in the yearly AVERAGE fu...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem, and haven't had a response. I have a form which uses some data from table1 to create an entry in table2 (i know duplication is bad, but as this is a live database, and i am working on a new section, this makes this complicated task easier), which works fine. however, i have a third table that has the unique ID from table1 and table2 in order to show when a table1 row of data is needed in table2 via a checkbox. This third table3 is created when elsewhere. The problem lies with bringing in the unique ID value...

List of values summing to a known value
Is it possible to run a query that would return a list of values that sum to a known quantity. A simplified example would be as follows: Return any items from the list that sum to 10 1 3 4 7 9 The returned values would be 1,9 and 3,7. Thanks. Do you mean something like this -- SELECT [TableA_1].[Auto]+[TableA].[Auto] AS Expr1, TableA.Auto, TableA_1.Auto FROM TableA, TableA AS TableA_1 WHERE ((([TableA_1].[Auto]+[TableA].[Auto])=10)); -- Build a little, test a little. "lmattern" wrote: > Is it possible to run a query that would return a list...

Query on "Yes" Brings Back both
Hi, I have created a table where the value is Yes/No - text with default as No. I have a query in which I am trying to bring back only the "Yes", but both yes and no are being shown. Any ideas? Thanks It keeps saying "Data Type mismatch" but how can that be when I created the query from scratch? SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all 05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all 05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer for 2007], [all 05].[Claim Salutation], [all 05].[Cla...

Returning Multiple Values in a Single Cell
Hello, I posted this same question a couple of weeks ago but forgot to mention a key point. My question was as follows: I have read a few posts regarding this subject but am still somewhat lost. I have a two sheet workbook (Excel 2000). On the first sheet is a list of projects that my department will complete during the year. A description of each project is given, a start date, an end date, as well as which crew will be completing the work (Crew 1 or Crew 2). What I am trying to do on the second sheet is to be able to type in any date, and have the description of the project on that da...

Returns immediate function
Hi all, I know that the function CeRapiInitEx returns immediately. No wait for processing. I want to implement such a function. Returns immediate. no wait. How do I implement such a function? Thanks Ko You might create a UI thread that do the work, launch it in the "non-wait" function and return immediatly to the caller. When the thread done it's works, you can post an application message to return the results.... Greetings, Gaetano Sferra "PPC DEV" <ppc_dev@yahoo.com> ha scritto nel messaggio news:033101c3b388$a7098910$a301280a@phx.gbl... > Hi all...

Multiple Tables in Query not Listing Data.
I cannot seem to get this correct. I am trying to run a query on two databases one of which is linked to the other. I am trying to get the query to display a 0 for the total defects counted from one table based on the date and time from another table. So far when I run the query I get only the counts from the values that are not null. Here is the SQL Statement: SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift, Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1, Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL, IIf(IsNull([Counto...

Lookup Values, return multiple.
I need help please. The formula I require has to in Worksheet A :- [B1] lookup DATA in Worksheet A [A1] SP001 in worksheet B [A] [B] [C] SP001 PL001 16 SP001 PL002 5 SP001 DR001 10 CR001 PL001 2 Search for all Text String starting with "PL" in Worksheet B [B] only for SP001 in [A] and return with values from Worksheet B [C] 16 and 5 I need the values to be seperated and not summed. Tx. Appreciate assistance. Try this... In the formulas: Rng1 refers to worksheet B $A$2:$A$5 Rng2 refers ...