Trouble with union qry

Below is a union query which gathers up shipment and returns to give data 
representing quality (total returned/total shipped).  The query works fine 
without the WHERE statement for all customers.  But I want to filter it out 
for a specific Account Manager's customer's only.

I tried the WHERE statement below, but it doesn't work.

WHERE (((qryQualityRGA.AcctmgrID)=getmyvariable()));

Interesting to me is using that last statement brings up a parameter box 
asking for the value of qryQualityRGA.AcctmgrID????


SELECT qryQualityRGA.plant, qryQualityRGA.AcctmgrID, 
qryQualityRGA.TransactionDate, qryQualityRGA.QuantityReturned, 
qryQualityRGA.QuantityShipped FROM qryQualityRGA UNION ALL SELECT 
qryQualityShipped.plant, qryQualityShipped.AcctmgrID, 
qryQualityShipped.TransactionDate, qryQualityShipped.QuantityReturned, 
qryQualityShipped.QuantityShipped  FROM qryQualityShipped
WHERE (((qryQualityShipped.AcctmgrID)=getmyvariable()));
-- 

-- 
TIA
0
Utf
1/21/2008 6:58:00 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
611 Views

Similar Articles

[PageSpeed] 23

tsison7 wrote:

>Below is a union query which gathers up shipment and returns to give data 
>representing quality (total returned/total shipped).  The query works fine 
>without the WHERE statement for all customers.  But I want to filter it out 
>for a specific Account Manager's customer's only.
>
>I tried the WHERE statement below, but it doesn't work.
>
>WHERE (((qryQualityRGA.AcctmgrID)=getmyvariable()));
>
>Interesting to me is using that last statement brings up a parameter box 
>asking for the value of qryQualityRGA.AcctmgrID????
>
>
>SELECT qryQualityRGA.plant, qryQualityRGA.AcctmgrID, 
>qryQualityRGA.TransactionDate, qryQualityRGA.QuantityReturned, 
>qryQualityRGA.QuantityShipped FROM qryQualityRGA UNION ALL SELECT 
>qryQualityShipped.plant, qryQualityShipped.AcctmgrID, 
>qryQualityShipped.TransactionDate, qryQualityShipped.QuantityReturned, 
>qryQualityShipped.QuantityShipped  FROM qryQualityShipped
>WHERE (((qryQualityShipped.AcctmgrID)=getmyvariable()));


Each SELECT statemet has a separate WHERE clause, not one
WHERE clause for all SELECT statements in the UNION.

In this case, I think you need to move it up before UNION

-- 
Marsh
MVP [MS Access]
0
Marshall
1/21/2008 4:40:44 PM
Reply:

Similar Artilces:

Trouble with session identifier in production but not development
Hi! When using ReportExecution2005.ToggleItem I have a pretty much working page displaying reports with drilldowns in my development environment. In production though, I keep getting "The session identifier is missing". I'm setting the execution id returned by Render in each request to the server, and have been fiddling with setting or not setting parameters each time. Will provide more info if needed, but hoping that someone have a good clue based on this information. The versions are the same in development and production, both 64 bit servers. -- Lars-Erik...

define managed unions in vc++.net #2
Hi, I want to define the managed union in VC++.net. I have defined the union at the file scope level in the header as follows: [Serializable] public union xyz { Stud^ Student; String^ Rank; }; But I get a compile error as follows: error C4959: cannot define unmanaged union 'xyz' in /clr:safe because accessing its members yields unverifiable code The Stud is the managed structure at file scope level. Unions are generally a bad idea; the core principle usually deals with minimizing storage allocation, which is in cases like you show below is pretty pointless, since you are savin...

Trouble w/ Excel Window
When accessing a particular excel file, I cannot view the minimize/restore/maximize and close buttons that are normally at the top right of the window. The only happens w/ this particular file; all other files are as normal. ...

Trouble with the Favorites function
My favorites function worked till yesterday. When I click the Favorites in this bar: File Edit Favorites Tools Help, I still get a list of all my Favorite folders, but when I put the mouse pointer over a folder, all I get is a blank mini-window. For example, when I place my mouse pointer over the Medical folder, I used to get the half dozen sites I placed in this folder. Now I get only a blank space. The Favorites function works sometimes though - about 10% of the time. Then it goes out and I get that blank mini-window after I click on the folder. There's probably an ...

Trouble getting Bill Pay to work with Money 2005
I upgraded from Money 2004 Deluxe to Money 2005 Premium this evening. I had Bill Pay working in the 2004 version. I went to enable the "Access your Money data on the Web" option in Settings. It said that I had to disable Bill Pay first before it would continue and that it would enable Bill Pay again once the process was complete. Unfortunately, now I can't get Bill Pay enabled again. I can go to the Bills screen and see my scheduled bills, but I can't do ePay through Bill Pay in Money anymore. When I go to Manage Online Services, I see my bank accounts that are ...

SQL strings with quotation marks or apostrophes trouble!
I'm trying to write an SQL statement where one or more field entries include strings with quotations mark. for instance: 20" Tube The quotation mark translates to "inches", and needs to be stored in the field. or in another case Support "T"-Beam Here's short (very abbreviated) example of what I'm trying to do in VBA (which doesn't work, by the way).... Sub MySub() Dim strField1 as string Dim strField2 as String Dim QUOTE as String QUOTE=3D""" =20 ' comment triple quotations to improve code readability. s...

Trouble maintaining "date modified" information
How can I maintain the "date modified" information? It seems to updating this anytime I access or simply print the document. It is doing this even when I say "no" to saving changes. Since the details have "date accessed" and "date modified" should it not follow that definition. Does anyone know how I can keep modified the date modified to change only WHEN I truly modify it? Submitted via EggHeadCafe - Software Developer Portal of Choice HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE http://www.eggheadcafe.com/tutorials/aspnet/af45b9e6-4b0e-4c...

Trouble with Tab key on windows developed with Developers Toolkit
I am developing an integrating application using the Developers Toolkit. The windows I've developed using VS2005 and the Developers Toolkit are not accepting the tab key for movement between fields. When one of my windows is the active window, even Alt-Tab doesn't work. When I change my project type to be a windows application, the windows work fine. Has anyone experienced this problem or have a hunch what might be causing the problem? Thanks in advance for any assistance! "Phil Glantz" wrote: > I am developing an integrating application using the Developer...

Trouble exporting Report to Excel
How do you increase rows to output size? When exporting a 2000+ page report to Excel (97-2002 *xls), stops on page 412 with message stating: “There are too many rows to output, based on limitation specified by the output format or by Microsoft Access”. Using Access 2000 with Windows 2000 OS Excel has a hard-and-fast limit of 65536. When you export to Excel from a report, you usually get extraneous blank lines, so it's conceivable that you're hitting that limit. There's no way around it, other than to simply export the data (assuming less than 65536 rows) rather than the rep...

trouble with excel #5
Hello, Iam trying very hard to find a fourmula in order to calculate holiday for my employees. The problem is as follow... If they are in the company for less than 2 years they are entitle to 20 days between the 1st of January and the 31st of december If they are in the company between 2 and 5 years they are entitle to 22 days from their anniversary date. If they are in the company for more than 5 years they are entitle to 25 days from their anniversary date. My problem is to take in consideration their anniversary date (ex starting date the 15 of march 2000 how many holidays does this perso...

trouble with sending messages
I have just tried sending the messege using outlook express but it keeps on saying message on the outbox. what can i do ?? Apparent dbx file corruption. Move most of your messages out of the Inbox and then create new Outbox and Sent Items folders after you move any messages you wish to save to a local folder you create. Tools | Options | Maintenance | Store Folder will reveal the location of your Outlook Express files. Write the location down and navigate to it in Windows Explorer or, copy and paste it into Start | Run. In WinXP, Win2K & Win2K3, the OE user files...

trouble openning file
I am drawing a blank again regarding the correct way to read files. Here is the pertinent section of code I am troubled by: CString JobFile="C:\\temp\\1.dat"; CFile in; CByteArray buffer; if(!in.Open(JobFile, CFile::modeRead)){ AfxMessageBox("Error opening file"); return; } buffer.SetSize(in.GetFileSize()); if(in.Read(buffer.GetData(), buffer.GetSize()) != buffer.GetSize()){ AfxMessageBox("Error reading file"); return; } in.Close(); When I compile I get the following error: error C2039: 'GetFileSize' : is not a member ...

Stanford Credit Union (SFCU) and Money
Hi there, We've been using online banking with SFCU until the recent upgrade. Now, when I tried to re-setup online services SFCU is no longer listed. What's going on? Thanks! -- Vlada Breiburg In microsoft.public.money, Vlada Breiburg wrote: >Hi there, >We've been using online banking with SFCU until the recent upgrade. Now, >when I tried to re-setup online services SFCU is no longer listed. What's >going on? Stanford Federal Credit Union access was provided via Cash Edge, and Microsoft is providing such access via Yodlee. That means that only web acces...

Polishing off a new template-trouble with text placement
I am creating a new template in PowerPoint 2003 for my company using XP Prof. The only problem I am having is the text boxes I created on the title slide aren't working when an old presentation is copied into the new one. The text takes placement on the title slide where the old template had them. Is there a way to get the text from the old presentation to conform to the new template. All of the text slides seem to work fine. Any help will be appreciated! Do these slides use placeholder ("click to add text") or manual "ad hoc" textboxes? You may nee...

trouble receiving mail
hi oh computer smart ones!! am hoping anyone can help with my problem - i have 53 unread messages because when i hit 'send and receive' i get the first 13 then it stops completely and wont give me any more messages....i get this error msg: Message number 13 could not be retrieved. Account: 'mail.three.com.au', Server: 'mail.three.com.au', Protocol: POP3, Server Response: 'No virus found in this incoming message.', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800420CD i have tried turning off firewalls, uninstalling...

money 03 troubles downloading from bank
recently I have been unable to download from my bank. I go to the bank sight and everything appears to work fine...but there is no downlad in money. No notice of items downloaded. Then the bank says that there is no info for download because it is already done. This happened spontaneously with no system changes. win xp home microscoft money 03 standard thanks mark Go to Internet Options and clear out your Temporary Internet Files. It can't hurt and it works for some. This very topic is discussed a lot. In addition to the 'clear temporary internet files', try thes...

Why does Platform SDK define union LARGE_INTEGER in such a way?
Why does Platform SDK define union LARGE_INTEGER in such a way? ntdef.h defines the union LARGE_INTEGER as follows: typedef union _LARGE_INTEGER { struct { ULONG LowPart; LONG HighPart; }; struct { ULONG LowPart; LONG HighPart; } u; LONGLONG QuadPart; } LARGE_INTEGER; Though I fully understand what union is, I cannot think out the reason why it adds struct u into the union. In fact, the struct u is equivalent to the unnamed struct, so we can alway use the unnamed struct instead of struct u. To my thinking, the union should be des...

Passing Qry as part of Rpt DoCmd.OpenReport
If the selected manufacturer = "_All" first value in list, then my query sQry = "Year = " & ddlYear.Value else: sQry = "Year = & ddlYear.Value & " and manufacturer = " & ddlManu.Value .... also tried: "...=" & "'" & ddlManu.Value & "'" DoCmd.OpenReport sReportName, acViewPreview, , sQry Checking the value of sQry shows "Year = 2007 and manufactuer = 'TRMCO'" But I still get the same data set as "Year = 2007" When I query by pasting the same where clase into my dat...

Newbie: trouble validating xml file
IDE: Visual Studio 2003 .NET (7.1.3088) OS: XP Pro ..NET Framework 1.1 (1.1.4322) I'm experimenting with xml in .net, and having trouble to get a xml file validated without any errors/warnings in VS . So I searched the web and found this example at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307379&ID=kb;en-us;Q307379&SD=MSDN section "Use Namespaces in the XSD Schema" ProductWithXSD.xml: <?xml version="1.0"?> <Product ProductID="123" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="ur...

Trouble modifying/deleting relationships
We just rolled to CRM 4, rollup 7 and ran into a problem. Relationships that were in place before rollup 7 now can NOT be modified or deleted without the system throwing an 'Invalid Argument' error dialog. I believe this is a known bug, does anyone know of any patches or hotfixes available or in the works? -- Thanks and have a great day! Mike On Jan 27, 7:04=A0am, Mike <M...@discussions.microsoft.com> wrote: > We just rolled to CRM 4, rollup 7 and ran into a problem. =A0Relationship= s that > were in place before rollup 7 now can NOT be modified or deleted without ...

Trouble with concatenation.
Hi, I'm trying to concatenate a number of cells together using the concatenate function and am having some trouble. Basically the formula is showing up in the cell without the actual concatenated value. I have removed any leading spaces and the "=" sign is in place but the only thing I see is the formula. What would cause this? Your cell is formatted as Text. Change it to General. In article <F987FB24-30E5-4018-84AE-3FB06597167C@microsoft.com>, Jim Moberg <JimMoberg@discussions.microsoft.com> wrote: > Hi, > > I'm trying to concatenate a numbe...

new publisher format troubles
Hello All, I currently have our church bulletin in Publisher 2000. It is run on 8.5 x 11 with 4 pages and book fold, so there are two pages on each side of the paper (using duplex printing with lazer printer). I want to keep this exact format, but I want to switch to 8.5 x 14, and use the extra three inches for 2 separate pages (one front and one back). So, in essence, everything would stay the same except for the new few inches that are gained. The document is setup to print in landscape form with book fold. The pages are obviously 8.5 inches tall, and 5.5 inches wide. I can change ...

trouble sending emails
In the last few weeks, I started using Outlook (Express and Outlook 2003). Both will often not allow me to send an email. Other times, they let me. What is going on? Outlook 2003 says: 500 Unauthorized relay msg rejected. Here is the message Outlook Express gives me: The message could not be sent because one of the recipients was rejected by the server. The rejected e- mail address was 'macrealestate@denver.net'. Subject 'Form posted from Microsoft Internet Explorer.', Account: 'pop.thelotusway.org', Server: 'smtp.thelotusway.org', Protocol: SMTP, S...

Question abt delete qry!
Hello All, I have 2 tables .DEL4(4 records) and Order Details( 10 records) I want to delete those records from Order Details where they match with the records from DEL4 on 2 fields which are "OrderID" and "ProductID"'I tried a Delete Query but that did not work! PS the fields "OrderID" and "ProductID" exist in both tables ofcourse! Thanks in advance for the help! hi Earl, On 18.12.2009 17:46, Earl.AKA J.Alladien in access forum!! wrote: > I have 2 tables .DEL4(4 records) and Order Details( 10 records) What ki...

Trouble formatting Date data
I am using a Line chart to plot specific milestones over a series of dates. Each line in the chart represents the series of data and its progression over time. I have managed to create each series of data with my dates showing up in the X axis as a Time-Scale. However, I need my categories (the names of the milestones) to appear in the Y axis and I can't figure this out. I also want to experiment with having the dates appear in the Y axis, and categories in the X, although this does not appear to be possible. I believe this would represent my data more meaningfully. Your help i...