Union queries and crosstab...

I created a union query that combined three tables.  Three tables that store 
three types of deposits (ID, Coupon, Misc).  The union query has a criteria 
of a date range.  The fields include SalesAmt for all of the deposit types.

If one of the tables do not have data for that date range (let's say Misc), 
the SalesAmt column for the Misc table does not show up in the query.  Is 
there a way for me to have a filler column so it can be displayed in a 
report?  Currently, if a table has no data, the report errors out saying the 
column does not exist.

Thanks.
0
Utf
12/26/2007 7:35:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
480 Views

Similar Articles

[PageSpeed] 32

Use a NZ function on that field so that there will not be a null.
  AliasFieldName: Nz([YourFieldName], 0)  if you want a zero.

  AliasFieldName: Nz([YourFieldName], "")  if you want a blank.

-- 
KARL DEWEY
Build a little - Test a little


"ngan adams" wrote:

> I created a union query that combined three tables.  Three tables that store 
> three types of deposits (ID, Coupon, Misc).  The union query has a criteria 
> of a date range.  The fields include SalesAmt for all of the deposit types.
> 
> If one of the tables do not have data for that date range (let's say Misc), 
> the SalesAmt column for the Misc table does not show up in the query.  Is 
> there a way for me to have a filler column so it can be displayed in a 
> report?  Currently, if a table has no data, the report errors out saying the 
> column does not exist.
> 
> Thanks.
0
Utf
12/26/2007 8:29:00 PM
Reply:

Similar Artilces:

Union Query Problem
I am trying to use a union query to join 4 table together I have done the code but when it runs i get the following error ODBC - Call Failed [MySQL][ODBC3.51 Driver][mysql-4.1.22-standard] You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near Union( SELECT 'tblcodesWS' , 'tblProductsWS' , 'ProductName' , ' tblPr' at line 1 (#1064) Can any one help me If i if i split the Union query into 2 seperate ones they both work fine, just have problems when i put them together What...

International Money 2005 queries
Firstly - Apologies for raising International version queries in this forum. There does not appear to be a specific Australian NG. Ran 2005 Trial version O.K for the 60 days then brought Money Standard 'New for 2005' version a few weeks ago. Help|About shows it is Vrs 14.0.120.1105 A couple of queries. 1. There appears to be no built-in Help - selecting Help brings up a Help side panel with "A problem occurred, Please Try again link". Clicking on the link brings up "Get help with an MSN product" and a list of topics such as 'Billing, MSN Client, MSN Hotma...

Query Analyzer Question #2
We are running GP 9.0. One of our customers was bought out and all of thier stores have changed names. I am trying to find an easy was to go into our GP SQL database and change the Customer Name, The ShortName and StatementName to reflect the new name of the company. Does anyone know the query syntax I would use to make these changes? Am I posting in the correct group? TIA TRD Why not use Integration Mgr, its easier but since query analyzer is your choice then here's the basic sql statement but maybe somebody here can improve it: Update RM00101 set STMTNAME = ' ' where...

Query to delete characters after first hypen
I'm looking to return all the text before the first hypen IBDT-1209-USD-B INGBFE-434142-EUR-B F/AGD-4079-NOK-B ADE-434129-EUR-B I would like to see: IBDT INGBFE F/AGD ADE Thanks for the assistance. U�ytkownik "Rachel" <Rachel@discussions.microsoft.com> napisa� w wiadomo�ci news:39E1E728-B534-4C11-B02F-0A9BA833E7E7@microsoft.com... > I'm looking to return all the text before the first hypen > > IBDT-1209-USD-B > INGBFE-434142-EUR-B > F/AGD-4079-NOK-B > ADE-434129-EUR-B > > I would like to see: > > IBDT > INGBFE > F/AGD > A...

Building a Query by Form Interface
Hello, I'm trying to create a method where a user selects some criteria in a form and a query then generates the results. I have been able to pass numbers successfully in the query but not text. If anyone has an easy way of developing this let me know. The code below works for numbers but not text Help me please. Function BuildSQLString(strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String strSELECT = "s.*" strFROM = "UFRRecords s " If Check2 Then strWHERE = strWHERE & " s.[FlagField1] = " & Combo0 End I...

Make Table query, but remove character from field
I already have a rroutine set up and part of that is a make table query. One of my fields has some values as G35.X and some as G35X. I want to make this field in my make table query read G35X for all. I do not want to run update queries or find/replace and want in my make table definition some code that when creating this field to remove the "." where it exists. Is this possible? Thanks Sorry, I should have made clear I can't just set the field to "G35X" as there are other values with the "." ! "Andy" wrote: > I already have ...

Internationalisation queries
1. When entering numbers in Excel using scientific notation in the English version of Excel, the following convention is used "[number]E[number]", for example 12E3 (i.e. 12,000). Can someone tell me whether the "E" character is the same for all languages? 2. Error codes such in Excel such as "#N/A" and "#VALUE", can someone tell me whether these are the same for all languages? And if not, is there an easy way to determine what the error strings are in the language locale used by Excel? Many thanks, John www.johnmote.com ...

Analytical Accounting Queries for multiple years
Would like the ability to select multiple years within the queries in Analytical Accounting. ---------------- 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.mspx?mid=e284dc78-f0fb-4f68-80eb-ee9026f...

CTE and union error on varchar field
I have a CTE that I am reading with a UNION. For some reason, it thinks the 1st field is a bit. But LastName from both tables are varchars - one is varchar(50) and the other is varchar(36). The Error I am geting is: Conversion failed when converting the varchar value 'Clartia, MD (ALCM-FT)' to data type bit. The first row does happen to be a 1 but the type of r.LastName is varchar(36) and u.LastName is varchar(50). Also, if I run the query without the UNION and only use one SELECT it works fine with both SELECTs. The one with the error is: WITH Distribution...

how to calculate total time in query
Anyone know how to calculate total time in query if the total time exceed 24 hours? I wish the total time to be display in number of hours and minutes. See: Calculating elapsed time at: http://allenbrowne.com/casu-13.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <xiaodan86@hotmail.com> wrote in message news:1176090276.654203.320320@y80g2000hsf.googlegroups.com... > Anyone know how to calculate total time in query if the total time > exceed...

CRM Mobile querying recipientAddr,recipientSrvcPort etc
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

Remove Certain String from Query Field
Hello Experts, I have a query with mulitple fields. one of them contains the names of the projects our depatrment is working on. the name starts with a 4 digit number, then an underscore, then a 4 or 5 digit number then another underscore then the Name of the proejct itself. so the field looks like this xxxx_xxxx_Project Name. I want to create an experssion that only shows the Project Name part without the numbers or the underscores. the Problem i'm having tis that the number of these digits vary from project to project. the total number of characters can very between 10 1...

Query Access
I am using VB.net 2008. I have a access database with 2 tables in it tblTeacher and tblStudent. I create a recordset (rsTeacher) one of the fields (TeacherName) in tblTeacher using distinct. Then I loop through the rsTeacher and then create another recordset (rsSchedule) and pull the Period and StudentID then I want to use that StudentID to query the tblStudent with the StudentID to get the StudentLastName, StudentFirstName and etc. then order by period, studentlastname, studentfirst. I usually cheat and use the query function in Access to do them but it doesn't seem like this...

Union or Union All
I am trying to combine two tables into one but when I try to combine the text fields it gives me null values Table A Customer SalesPerson TEXT TEXT2 A B ABCD EFGH B C BCDE FGHI A You hit the send key too early. You only posted data from one table and then no results of the union query. You did not post your query SQL. I can not see into to the other screens on your computer. -- KARL DEWEY Build a little - Test a little "FSUrules2883@aol.com" wrote: &...

Using Union All in a stored proc
I have the following Stored Proc that works...basically it provides a dummy record for me to work with. I want to ad another record to this. I thought I could do something like a UNION ALL to make two records but it doesn't work. What's the easiest way to do this? See original SP, then edited SP below that... Thanks! -- Begin Original SP -- USE [CODS] GO /****** Object: StoredProcedure [dbo].[AHIS_GET_PATIENT_INFORMATION] Script Date: 05/28/2010 13:35:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================...

Developing queries in two or more parts.
Hi All, Sometimes I find that I need to develop queries in two parts because trying to do everything in one produces all sorts of error messages. I don't really mind doing it this way but I don't really understand what is going on and thus am unable to predict how difficult a particular query may be to develop. I appreciate that I am probably using the 'develop word a little too liberally here, I am most certainly not a developer, but can anyone advise an online resource that might enlighten me, if only a bit!! Thanks. Bill. Yes, that sort of thing. Been some quite com...

Instead of UNION?
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01CB2F42.2460ECF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Rather than use a UNION statement to add the outputs of these two SELECT = statements, is there another way to combine the outputs via one SELECT = statement (I only want unique values of tblLSKTransactions.CounterID)? SELECT tblLSKTransactions.CounterID FROM tblLSKTransactions=20 INNER JOIN tblLSKMembers=20 ON tblLSKTransactions.MemberEmail =3D tblLSKMembers.MemberEmailAddress INNER J...

Query runs from data given on form, how do you query all?
I didn't really know how to explain my question briefly enough for the subject, so I hope people understand my subject enough to click this thread if they can help. I was able to remember the answer to my question from yesterday, but now there is something else I want to do, and I wondered if anybody knows how. Currently, I have several queries that will be run using the data the user gives in certain forms. Now, what I want to be able to do is to allow the users to either leave one of the fields in the form blank, or select some kind of "Select all" type of option, and t...

Simple calc query
I am new to Excel and have Excel 2007 if that makes any difference. I am just trying to write my own vehicle logbook for my own vehicle which is used personally and for work. To denote this I have done the following: value 1 value2 Notation work column Personal column The above are different cells. Value 1 is the start mileage. Value2 is the end mileage. Notation is either P or W. Work Column has the formula =IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or whatever) and of course it goes for rows from A downwards. The end result, so far, is that...

union query without "blank" recordsl
I have a union query between 2 related tables, and where they may be records in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages ]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, Fixt...

Tab control not working with queries
I have a form with a tab control on it. Within one of the tabs I have a subform. the problem I have is that I have a button on this subform that runs a report based on a selection within this subform. Bottom line is it appears that the query (that the report uses) doesn't recognize the criteria - [Forms] ![F_AFE_subform]![AFE_ID] - because it is not part of the main form that the tab control is on. Is there a way to accomplish this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 "szag via AccessMonster.com"...

Query to pull set criteria or all
I have the following query: SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 Countsheet].Task = [Area to Task Query].[Task ] WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682 Countsheet].Status)<>"Completed")); I would like the query to ask for an OPID as i have set o...

Web Query Import
I have some data on a web page and I want to use the Data/Import External Data/Web Query to pull the data into an excel spreadsheet. The problem is that the import tries to interpret the data and changes it. The first line below is what was on the web, the second line is what excel changed it to.....I've tried all of the options with no luck .... I just want it to pull it in as text and not mess with it. Any ideas? 083934 03830000 05 0.00 30735800000000000060 83934 3830000 5 0 3.07358E+19 --- Message posted from http://www.ExcelForum.com/ have you tried pre-formatti...

help for query require
Hi I have reqiured a query which extract data from the table which is based on max date & time and today date,please hel p me Tablename xyz S.no Symbol Date &time Opening rate Current rate 1 hbl 21-08-2010 9:15:36 101 105 2 hbl 21-08-2010 9:15:40 101 106 3 hbl 21-08-2010 9:15:55 101 104 4 ppl 21-08-2010 9:15:36 125 130 5 ppl 21-08-2010 9:15:40 125 131 6 ppl 21-08-2010 9:15:50 125 128 7 ppl 21-08-2010 9:15:55 125 126 Out put S.no symbol Date & time op...

query error message
UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID = ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name] WHERE (((Len([name]))>0)) When I try to run this I get the error message An Error occurred while executing query: Inccorrect syntax near the keyword ‘inner’ We used to have local support that set this up we were able to run it once when it was set up and was told we could re run it when ever we needed to update the infor. Also was told they would send me a new query that would update this info constantly. Never got the email they disap...