473,854 Members | 1,677 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Compex SQL Query containing UNION, Needs Filtering

1 New Member
Hi there,

I am trying to do a UNION with slightly different queries, it appears to work but it now bring in another problem. The second part of the UNION need to have a an extra field value ("NULL") added thus to match the number of fields between the two select queries. The problem comes that I some have duplicate records and want to condense them down so that "Table_Form_Ele ment_ID" has a single record against it but I want the record to be the one where (NOT (Form_Element_S erver_Process_C ode IS NULL)) AND (NOT (Form_Element_S ite_Id IS NULL)) and disregard the other bogus records. However say this if (NOT (Form_Element_S erver_Process_C ode IS NULL)) AND (NOT (Form_Element_S ite_Id IS NULL)) do not exist then display the records (NOT (Form_Element_S erver_Process_C ode IS NULL)) OR
(NOT (Form_Element_S ite_Id IS NULL)).

The current query is producing the following:-



Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Form_Storage_Process_Order, Form_Element_Server_Process_Code, Table_Form_Element_ID, Table_Form_Table_Name, Table_Form_Element_Key, Table_Form_Element_Name, Table_Form_Element_Type, Table_Form_Element_Length, Form_Element_Site_Id, Form_Id
  3. FROM (SELECT cTbl.Form_Storage_Process_Order AS Form_Storage_Process_Order, cTbl.Form_Element_Server_Process_Code AS Form_Element_Server_Process_Code, dTbl.Table_Form_Element_ID AS Table_Form_Element_ID, dTbl.Table_Form_Table_Name AS Table_Form_Table_Name, dTbl.Table_Form_Element_Key AS Table_Form_Element_Key, dTbl.Table_Form_Element_Name AS Table_Form_Element_Name, dTbl.Table_Form_Element_Type AS Table_Form_Element_Type, dTbl.Table_Form_Element_Length AS Table_Form_Element_Length, bTbl.Form_Element_Site_Id AS Form_Element_Site_Id, eTbl.Form_Id AS Form_Id
  4.      FROM WWWFORM_SITE_ELEMENT_LAYOUT aTbl INNER JOIN
  5.         WWWFORM_SITE_ELEMENT bTbl ON aTbl.Form_Element_Site_Id = bTbl.Form_Element_Site_Id INNER JOIN
  6.         WWWDATABASE_STORAGE_PROSESS_GROUP cTbl ON aTbl.Form_Element_Layout_Id = cTbl.Form_Element_Layout_Id INNER JOIN
  7.         WWWDATABASE_STORAGE_PROSESS_STRUCTURE dTbl ON cTbl.Table_Form_Element_ID = dTbl.Table_Form_Element_ID INNER JOIN
  8.         WWWFORM eTbl ON eTbl.Form_Storage_Process_Type_Id = cTbl.Form_Storage_Process_Type_Id
  9.      UNION
  10.      SELECT cTbl.Form_Storage_Process_Order AS Form_Storage_Process_Order, 
  11.        cTbl.Form_Element_Server_Process_Code AS Form_Element_Server_Process_Code, 
  12.        dTbl.Table_Form_Element_ID AS Table_Form_Element_ID, dTbl.Table_Form_Table_Name AS Table_Form_Table_Name, 
  13.        dTbl.Table_Form_Element_Key AS Table_Form_Element_Key, dTbl.Table_Form_Element_Name AS Table_Form_Element_Name, 
  14.        dTbl.Table_Form_Element_Type AS Table_Form_Element_Type, dTbl.Table_Form_Element_Length AS Table_Form_Element_Length, NULL, 
  15.        eTbl.Form_Id AS Form_Id
  16.      FROM WWWDATABASE_STORAGE_PROSESS_GROUP cTbl INNER JOIN
  17.          WWWDATABASE_STORAGE_PROSESS_STRUCTURE dTbl ON cTbl.Table_Form_Element_ID = dTbl.Table_Form_Element_ID INNER JOIN
  18.          WWWFORM eTbl ON eTbl.Form_Storage_Process_Type_Id = cTbl.Form_Storage_Process_Type_Id) DERIVEDTBL
  19. WHERE (NOT (Form_Element_Server_Process_Code IS NULL)) OR (NOT (Form_Element_Site_Id IS NULL)) AND (Form_Id = '1')
  20.  
If anyone knows how I can get around this problem I would be very grateful. Its giving me a headache. I am not familiar with If Else Clauses in SQL but I feel that it must be possible.

Thanks
Dec 15 '07 #1
0 1146

Sign in to post your reply or Sign up for a free account.

Similar topics

2
1448
by: Gerry Abbott | last post by:
Hi All, I have one table with two tables linked to two index fields, tblMain, MainId, CashId, CreditId tblCash Cashid, CashValue tblCredit, CreditId, CreditValue Each record is either a credit or a cash record, and so either the CashId or the CreditId will be completed. Is there a way I can query this info (without changing the underlying data) to give me a table containing two
7
3663
by: RLN | last post by:
Re: Access 2000 I have three history tables. Each table contains 3 years worth of data. All three tables have a date field in them (and autonum field). Each table has the potential to contain over 100K rows each. A common request the user receives is a request to pull all data written in a given month. I need to write some sort of union query across all three tables that would pull data for, say, only the month of December of all nine...
1
3095
by: Nicolae Fieraru | last post by:
Hi All, I want to find if there is a different way than the way I am working now. Lets say I have a table, tblCustomers containing address details. I want a report with all the customers from a specific state. In order to generate that, I create a form, I put a combobox with the states and a Command Button which opens a report. The report is based on a query, qryCustomersByState which has a parameter (State) taken from the Form.
0
2025
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary form by field named TestID. The subform is used just for displaying site address data, data which is stored in another table named Total_Site_Address. In the Total_Site_Address table there are numerous fields that form the site addresses...
2
4038
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter by form" feature. When the user tries to use Filter by form a messagebox is displayed: "There are too many controls in this form to perform a filter by form", after that,when the user exit the Filter by Form mode, Access crash, displaying a...
15
16778
by: laurenq uantrell | last post by:
Wondering if there is a physical or realistic limitation to the number of UNION statements I can create in a query? I have a client with approx 250 tables - the data needs to be kept in seperate tables, but I need to be filtering them to create single results sets. Each table holds between 35,000 - 150,000 rows. Should I shoot myself now? lq
2
1661
by: AJ | last post by:
Hi all, I have this monster query (at least i think it is). SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT JOIN Package AS p ON s.Package_ID = p.ID
1
2691
by: bgreenspan | last post by:
Hi Everyone, I'm back for some more expert help. Here's what I am doing and what I tried. My database has entries with Contract Names and Expiry Dates, among other fields. I have a form designed to show the expiring contracts. To do this I use a straight forward query in my form's ON LOAD code strwhere = " BETWEEN #" & Now() & "# AND #" & DateAdd("m", 6, Now()) & "#" Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery",...
2
1450
by: billelev | last post by:
Hi There, I am performing a UNION query on a Table containing % formatted values with a Query, also containing % formatted values. The resulting union query displays the table % values as decimal but the query % values as percents. e.g. Table, value1 = 3.5% Query, value1 = 4.5%
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10364
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9510
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7909
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7077
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4150
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.