Combining two fields into one in a query for a report

The database I am working on is to keep track of parts that are either in 
their storage area (on a shelf) or on an assembly line.  I have two fields, 
one called Shelf and every part has a shelf assigned to it.  The other field 
is Line and if a part in on an assembly line, there is a number indicating 
that line, if the part is on it's shelf, the word STOCK appears in the line 
field.  (Has to be this way for the rest of the database to work, so this is 
not up for question)  Now, I need to creat a report to show where all parts 
are.  If they are on a line the result needs to show the line number, but if 
they are on a shelf, it needs to show the shelf letter.  I can easily get the 
query to show all shelf assignments, or all locations be it in STOCK or on a 
line number, but I need one column to combine these two fields.

Thanks 
Sue
0
Utf
1/7/2008 9:11:00 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1098 Views

Similar Articles

[PageSpeed] 0

"Design by Sue" <DesignbySue@discussions.microsoft.com> wrote in message 
news:2AD910F4-D4C0-45A3-B9ED-A3E8D800CC8C@microsoft.com...
> The database I am working on is to keep track of parts that are either in
> their storage area (on a shelf) or on an assembly line.  I have two 
> fields,
> one called Shelf and every part has a shelf assigned to it.  The other 
> field
> is Line and if a part in on an assembly line, there is a number indicating
> that line, if the part is on it's shelf, the word STOCK appears in the 
> line
> field.  (Has to be this way for the rest of the database to work, so this 
> is
> not up for question)

I sincerely doubt that, but...ok.  We'll assume that's true for the sake of 
argument ;-)

> Now, I need to creat a report to show where all parts
> are.  If they are on a line the result needs to show the line number, but 
> if
> they are on a shelf, it needs to show the shelf letter.  I can easily get 
> the
> query to show all shelf assignments, or all locations be it in STOCK or on 
> a
> line number, but I need one column to combine these two fields.

How bout

Select IIF(Line = "STOCK", [Shelf], [Line]) AS Location FROM YourTable;

HTH;

Amy 


0
Amy
1/7/2008 9:32:28 PM
Field: WhereIsIt: IIF(Line = "STOCK",Shelf,Line)

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Design by Sue" <DesignbySue@discussions.microsoft.com> wrote in message 
news:2AD910F4-D4C0-45A3-B9ED-A3E8D800CC8C@microsoft.com...
> The database I am working on is to keep track of parts that are either in
> their storage area (on a shelf) or on an assembly line.  I have two 
> fields,
> one called Shelf and every part has a shelf assigned to it.  The other 
> field
> is Line and if a part in on an assembly line, there is a number indicating
> that line, if the part is on it's shelf, the word STOCK appears in the 
> line
> field.  (Has to be this way for the rest of the database to work, so this 
> is
> not up for question)  Now, I need to creat a report to show where all 
> parts
> are.  If they are on a line the result needs to show the line number, but 
> if
> they are on a shelf, it needs to show the shelf letter.  I can easily get 
> the
> query to show all shelf assignments, or all locations be it in STOCK or on 
> a
> line number, but I need one column to combine these two fields.
>
> Thanks
> Sue 


0
John
1/7/2008 9:35:32 PM
Thanks for your reply, but I am not sure where to put the code.

Thanks
Sue

"Amy Blankenship" wrote:

> 
> "Design by Sue" <DesignbySue@discussions.microsoft.com> wrote in message 
> news:2AD910F4-D4C0-45A3-B9ED-A3E8D800CC8C@microsoft.com...
> > The database I am working on is to keep track of parts that are either in
> > their storage area (on a shelf) or on an assembly line.  I have two 
> > fields,
> > one called Shelf and every part has a shelf assigned to it.  The other 
> > field
> > is Line and if a part in on an assembly line, there is a number indicating
> > that line, if the part is on it's shelf, the word STOCK appears in the 
> > line
> > field.  (Has to be this way for the rest of the database to work, so this 
> > is
> > not up for question)
> 
> I sincerely doubt that, but...ok.  We'll assume that's true for the sake of 
> argument ;-)
> 
> > Now, I need to creat a report to show where all parts
> > are.  If they are on a line the result needs to show the line number, but 
> > if
> > they are on a shelf, it needs to show the shelf letter.  I can easily get 
> > the
> > query to show all shelf assignments, or all locations be it in STOCK or on 
> > a
> > line number, but I need one column to combine these two fields.
> 
> How bout
> 
> Select IIF(Line = "STOCK", [Shelf], [Line]) AS Location FROM YourTable;
> 
> HTH;
> 
> Amy 
> 
> 
> 
0
Utf
1/7/2008 9:43:04 PM
You are wonderful!!!!! Thanks a million.

Sue

"John Spencer" wrote:

> 
> Field: WhereIsIt: IIF(Line = "STOCK",Shelf,Line)
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Design by Sue" <DesignbySue@discussions.microsoft.com> wrote in message 
> news:2AD910F4-D4C0-45A3-B9ED-A3E8D800CC8C@microsoft.com...
> > The database I am working on is to keep track of parts that are either in
> > their storage area (on a shelf) or on an assembly line.  I have two 
> > fields,
> > one called Shelf and every part has a shelf assigned to it.  The other 
> > field
> > is Line and if a part in on an assembly line, there is a number indicating
> > that line, if the part is on it's shelf, the word STOCK appears in the 
> > line
> > field.  (Has to be this way for the rest of the database to work, so this 
> > is
> > not up for question)  Now, I need to creat a report to show where all 
> > parts
> > are.  If they are on a line the result needs to show the line number, but 
> > if
> > they are on a shelf, it needs to show the shelf letter.  I can easily get 
> > the
> > query to show all shelf assignments, or all locations be it in STOCK or on 
> > a
> > line number, but I need one column to combine these two fields.
> >
> > Thanks
> > Sue 
> 
> 
> 
0
Utf
1/7/2008 10:55:02 PM
Reply:

Similar Artilces:

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

SUMIF with two criteria??
I would like to sum values for all rows where the value in column B 'F' AND the value in column C = 'X' I don't seem able to use SUMIF whe I have 2 criteria that need to be met for summing as opposed to jus one. Any advice would be greatly appreciated. Chas -- Potatosalad ----------------------------------------------------------------------- Potatosalad2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2083 View this thread: http://www.excelforum.com/showthread.php?threadid=47921 Use the SUMPRODUCT function: =SUMPRODUCT(--(B1:B100="...

One use licence
I have purchased a one use student/teacher copy of Microsoft office 2007 from my colleges IT department. My laptop has recently developed a serious fault and will need to be replaced. I still have the disks and I was wondering, if i remove the program from my old laptop will i be able to install it on my new machine. If so what would I need to do? On 2/28/2010 8:05 AM, Mr Random101 wrote: > I have purchased a one use student/teacher copy of Microsoft office 2007 from > my colleges IT department. My laptop has recently developed a serious fault > and will need to be...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

two fields automatically need to come up!
Access 2003 - After creating a table, I can type in the first field and if I have already used that student name, it automatically comes up and I can just press enter. I would like the second field to come up automatically as soon as the first field name is complete so I can press enter also - the first field is the student name and the second field is the student ID #. In other words, I would like the two fields to come up together automatically and I just have to press enter. How do I do this? Goldie What you are describing sounds suspiciously like what happens in Excel.. Access ...

Running Large Reports
Hello partners, I have an RMS install that's been for about a year now database size of about 2gigs with SQL Server Installed. My customer has a problem with running reports with large date ranges (6 months, 1 year etc). The loading and generating reports shows up however, "computing totals" does not load eventually the computer becomes unresponsive. Does anyone know how I can generate reports with sucha large intensive dataset? Regards ...

Access 2003 query help please
Hello, I am in need of some advice for a table create query. I have to work with a database that is imported using XML from an online database. Unfortunately the online database is a single (flat) table of membership data. I need to generate a mailing list from that data. The problem is that each record may contain one of two addresses for the mailing to a member. One is the organization address they work at the other is an optional mailing address. If the optional mailing address is present those fields must be used, otherwise the fields containing the organization address should be us...

Sorting Created Customized Fields in My Tasks view
This question may/may not have been answered ad-nauseum already, but I did not find it immediately when I did a search so I apologize in advance. If we have created customized fields for our My Task view, is there any way to make those fields sortable at all? I did a few mock tests of adding a new customized field and customized view, but did not see where I could make the desired customized field sortable like they are for the default/existing enterprise fields out of the box. Thanks....any help on this is greatly appreciated. ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

One element correspond with two or more types?
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta content="text/html;charset=UTF-8" http-equiv="Content-Type"> </head> <body bgcolor="#ffffff" text="#000000"> <tt>Is it posible that one element correspond with two or more types? The example below could be implemented?<br> <br> &lt;xsd:element name="MyElement" type=<b><font color="#ff0000">"type1 or type2" </font></b>/&gt;<br> <br> &a...

how to publish access parameter based query report
I have an access database report build up complete with parameter query and some charting and graphing. What is the best way to present the time based report thru the websites complete with all the input parameter. Please advise. Access reports cannot be rendered as web pages. The best you could do would be to present it as a PDF file, using ASP to capture the parameters, automate Access to generate the report and save it as a PDF file (which might require the use of a third-party product depending on Access version), and return the PDF file to the browser. "Parameter Query report...

Same account in two groups
I've got the same account in two different groups. I've moved all transactions to one account, but the Portfolio view still has a second account under another group with zero transactions. How do I get rid of the second view without deleting the whole account? GB In microsoft.public.money, Gabriel Behr wrote: >I've got the same account in two different groups. I've >moved all transactions to one account, but the Portfolio >view still has a second account under another group with >zero transactions. > >How do I get rid of the second view withou...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Needing a access report to captilize mutiple fields
here's the problem, we have a report that is in colums that has the same duplicate fields listed. In the first data field of Rank (this is a military file) we can use the > symbol to force the report to captilize the data in that field, but the other fields that a use the same data field will not print upper case. any ideals thanks danny danny wrote: >here's the problem, we have a report that is in colums that has the same >duplicate fields listed. In the first data field of Rank (this is a military >file) we can use the > symbol to force the report to captil...

After two years using it... still cannot get used to it
Please allow users to bring the old menus back. The "genius" who spearheaded the change in the menus in Office 2007, i.e. the ribbon interface, should be fired. It is EXTREMELY frustrating having to go through all these clicks and visual menus to find simple options that were more intuitively placed in previous versions of Office. Do you think that younger people are unable to read so you have to put pictures for the newer generations? It is so frustrating that I have decided to leave this message, and let you know that as of today, after using Office for... how many ...

Make Table Query
How do you add (in run-time) primary key and indexing to a table (during creation or after creation) when it has been created through a make-table query? Either from a designed query or VBA SQL statement. ...

one local profile
Hi, I want to create only one local profile on a PC and and I want prevent the domain accounts to create a local profile when they will login on this PC. Is it a way to configure it? Thanks, Haydar "Haydar" <Haydar@discussions.microsoft.com> wrote in message news:A2AA41AC-5BF9-4A49-B660-CEC711D60A58@microsoft.com... > Hi, > > I want to create only one local profile on a PC and and I want prevent the > domain accounts to create a local profile when they will login on this PC. > > Is it a way to configure it? > > Thanks, > ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Troubleshoot A Query
I am using these 2 queries: CrossTab1 SELECT UnderlyingSymbol, Date, Sum(TradeVolume) AS [Total Volume] FROM BTA_Trade_20100326143051 GROUP BY UnderlyingSymbol, Date; TRANSFORM Sum([TradeVolume]/[Total Volume]) AS Expr1 SELECT BTA_Trade_20100326143051.FirmId, BTA_Trade_20100326143051.UnderlyingSymbol FROM BTA_Trade_20100326143051 INNER JOIN CrossTab1 ON BTA_Trade_20100326143051.UnderlyingSymbol = CrossTab1.UnderlyingSymbol GROUP BY BTA_Trade_20100326143051.FirmId, BTA_Trade_20100326143051.UnderlyingSymbol PIVOT BTA_Trade_20100326143051.Date; I am trying to get the resul...

SO: HELP: using Sales Rep field in Customer record
I am setting up my SO database with importing using EMS. One snag: I want to use the Sales Representative info / table basically ot duplicate some of the customer field data in order to tag Customer referrals. This was UI can see which customer had referred another customer to us. I need to use all other customer record fields for other info. I can't get EMS to import the ID data from my external database (db) into the Sales Rep Id number field. Can anyone help? ...

Fields Read Only
How Do I Make Fields Read only after I saved it Thx There is no built-in support for this in the app. However, you could add javascript to the aspx page to disable the field if you want. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Mon, 15 Aug 2005 14:53:42 +0200, "Need Some Help" <Need@news.microsoft.com> wrote: How Do I Make Fields Read only after I saved it Thx ...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

Install Office:mac 2004 Professional on two laptops?
I just purchased a new copy of Office mac 2004 Professional and wish to install it on both of my laptops. The larger laptop, a 17" Powerbook never leaves my home and serves as my desktop computer. The smaller Macbook always travels with me. Is this installation on both of my laptops permitted? Or will the second installation be denied at registration, or worse, sometime later when I am away from home and trying to use the second computer? I have been told by a friend that two installations are allowed if only one of them is a laptop. Tom Hello Tom, My end-user licence agreement says ...

how to delete and insert in a two cells merge using Access 2003?
I want to merge a column of names to a column of 10 digit mobile numbers; but in doing so I need to delete "0" (the first digit) before adding "44" to the front ot the remaining 9. I know of the "=A1& " "&B1" formula, but it does not do the necessary deletion and insertion I need to do. Any help? Hi, Try this in cell C1 =A1&SUBSTITUTE(B1,0,99,1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dubois2U" <Dubois2U@discussions.microsoft.com> wrote in message news:F954F2E7-44DD-49F0...