Queries :: Unpivot Table With Union Query

Feb 2, 2014

I am using below Union Query to unpivot attached table. Basically this is a table with some clothes and their sizes (in yellow). Now my problem is that below query works well only when all sizes are in the same row but as per attachment I may have them potentially over three different rows.

I can determine which row is for which item based on size range column (in red) but I still do not know how to modify my query to look at different rows depending on value of size range column, especially that as you can see there are multiple values pointing out to the same size range i.e. 0-6 and 1-4 point out to Size Range 1.

Unfortunately I cannot change the layout of the table to have all sizes in one row

Code:
Select Field1, Field2, "0" as Size,Field3 as Qty from orderform where Field3 > 0;
Union All Select Field1, Field2, "1" as Size, Field4 as Qty from orderform where Field4 > 0;
Union All Select Field1, Field2, "2" as Size, Field5 as Qty from orderform where Field5 > 0;
Union All Select Field1, Field2, "3" as Size, Field6 as Qty from orderform where Field6 > 0;
Union All Select Field1, Field2, "4" as Size, Field7 as Qty from orderform where Field7 > 0;
Union All Select Field1, Field2, "5" as Size, Field8 as Qty from orderform where Field8 > 0;
Union All Select Field1, Field2, "6" as Size, Field9 as Qty from orderform where Field9 > 0;

View Replies


ADVERTISEMENT

Queries :: Unpivot / Normalize Table

Nov 15, 2013

Just wondering if I'm missing an SQL trick for normalizing this data I inherited from an Excel spreadsheet.

Current data:

Code:
Case | Insp1 | Insp2 | Insp3 | Insp4 | Insp5 | Rnsp1 | Rnsp2 | Rnsp3 | Rnsp4 | Rnsp5
---------------------------------------------------------------------------------
1234 WEEDS TRASH CAR <null> <null> WEEDS CAR <null> <null> <null>
1235 TRASH <null> <null> <null> <null> TRASH <null> <null> <null> <null>

Normalized table:

Code:
Case | Violation | InspectionType
-----------------------------------
1234 WEEDS Initial
1234 TRASH Initial
1234 CAR Initial
1234 WEEDS Reinspect
1234 CAR Reinspect
1235 TRASH Initial
1235 TRASH Reinspect

Right now I'm having to do it with ten different queries (each with its own query behind it to massage the original data), one for each Excel field, and checking for blank entries before appending. And there's going to be about 50 Excel tabs to gather up... anything I'm missing besides the basic slog?

View 2 Replies View Related

Queries :: UNION / UNION ALL Query Crashed Access

Oct 24, 2013

I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.

Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000

[code]...

View 1 Replies View Related

Queries :: Update Table With The Value Of Union Query

Sep 25, 2013

I have a table with a field I want to update with the value of a Union Query

The field I want to update is Yes/No format and I can update using 0 or -1 OK.

However, when I link in the union query (and check that I only have 1 update result which is either 0 or -1) I get an error message saying that Operation must use an updatable query.

I guess this is referring to the Union Query (although I am trying to update the table and not the query.

In short - I want to update a table based on the value of a union query.

Code:
UPDATE tbl_StaMe_NGR_Subscription INNER JOIN qry_QDF_QRYDEF1 ON (tbl_StaMe_NGR_Subscription.EmailType = qry_QDF_QRYDEF1.Type) AND (tbl_StaMe_NGR_Subscription.AgentEmail = qry_QDF_QRYDEF1.Email)

Code:
SET tbl_StaMe_NGR_Subscription.Subscribed = [Subscription];

View 1 Replies View Related

General :: How To Unpivot Table

Dec 31, 2013

I need now somehow 'unpivot' this table and in order to do it I need to concatenate columns and rows names and then match correct values so based on the above example, I would need to receive table with the following two columns:

Item X Size 1 | 0
Item X Size 2 | 4
Item X Size 3 | 6
Item Y Size 1 | 0

I have been trying various solutions found via Google but nothing seems to be working.

View 3 Replies View Related

Pivot Table Won't Update From Union Queries

Jan 3, 2008

I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...

When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".

I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?

Perhaps I should insist that our IT department upgrade. :p

Thanks in advance for any assistance.

- Matt

View 5 Replies View Related

Can You INSERT INTO Table With SELECT.. UNION Queries

Mar 29, 2005

I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?



The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:

INSERT INTO TableCompleteForMailing

SELECT [Address List].[FamilySurname] AS Surname,

[Address List].[DEARFirstnames] AS FirstName,

[Address List].[Mailing] AS Mailing,

[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Address List]

UNION

SELECT [Names].[LastName] AS Surname,

[Names].[FirstName] AS FirstName,

[Names].[MailingList] AS Mailing,

[Names].[Selected] AS OtherMailing,

[Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Names],[Address List]

WHERE [Names].[AddressListID]=[Address List].[AddressListID]

ORDER BY Surname, FirstName;

View 3 Replies View Related

General :: Combining Make Table With Union Queries

Aug 13, 2013

Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.

I am using Access 2010.

View 2 Replies View Related

Queries :: Checkboxes Becoming -1 And 0 In Union Query?

Oct 11, 2013

After putting in fields there were checkboxes into a Union Query, they now show up as -1s and 0s. Is a -1 a blank, and a 0 checked?

View 1 Replies View Related

Queries :: Max UNION ALL In Single Query

Sep 12, 2013

I'm having a small problem where Access returns a "Query too complex" error when I try to run a Union query of the form:

Code:
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "Two (2) zero visibility dives?" AS [Question], CountZeroVisibility AS [NegativeResponses]
FROM qryScore
UNION ALL
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "One (1) night dive?" AS [Question], CountNightDive AS [NegativeResponses]
FROM qryScore
UNION ALL
...etc

With more than 13 unions.

View 13 Replies View Related

Queries :: Make A Union Query

Dec 18, 2014

I have two basic lists of employee names, ID's, phone numbers etc, with one field containing an attachment with a picture of their ID. I don't want to permanently combine these two lists of employees, as they work in different departments, but for certain events I need to be able to print a report with a list of all their names, ID numbers, and corresponding ID photos.

I tried to make a UNION query, but can't do it with the attachment field. If I leave that field out, it's no problem. To simplify, I have been doing a test run as follows, with just the name field and photo field (field 1).

SELECT [Starting Gate employees].[Employee name], [Starting Gate employees].Field2
FROM [Starting Gate employees]
UNION
SELECT [Farrier employees].[Employee name], [Farrier employees].Field2
FROM [Farrier employees];

View 2 Replies View Related

Queries :: Union Query - Group By Then Sum

Aug 5, 2013

I have a Union Query which the result was like this:

Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00

Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.

View 5 Replies View Related

SQL - Union Query To Combine 3 Queries

Jun 3, 2015

I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?

SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByRole
UNION ALL
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByTeam
UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByAdHocPersonnel;

The where statement of potential use:

Code:
WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));

View 6 Replies View Related

Queries :: Combining Two Queries Without Union Query

May 7, 2014

Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.

Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.

If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.

View 10 Replies View Related

Queries :: How To Do Combine Two Different Queries In UNION Query

Oct 28, 2013

Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.

They Quays details:

1.Customer Credit Transaction1
2.Customer Credit Transaction2

Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.

The both query details:

Customer Credit Transaction Final
SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID
WHERE (((BillBook1.BillMode)="Credit"));
UNION ALL

[code]...

Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.

View 6 Replies View Related

Queries :: Union Query For Mail Merge?

Jun 3, 2015

it's possible to use a union query as a mail merge? I haven't found anything that says I can't do it, but I'm not getting my merge to complete, and when I switch to a plain query (and not changing anything else) my merge is successful, so I'm thinking there might be a limitation.

View 14 Replies View Related

Queries :: Does Rnd Function Work With A Union Query

Aug 29, 2013

I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);

But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA

View 9 Replies View Related

Queries :: UNION Query Will Not Execute Properly

Apr 1, 2015

UNION Query will not execute properly. Remove "WHERE" statement query runs.The WHERE statement is used to locate all duplicate records, based on serial number, in a table. Also, it excludes certain data contained in two of the four tables. There are four separate queries for four separate tables. They all work until incorporated into a UNION QUERY.

Question, is there another way to structure the "WHERE" statement for this UNION Query and achieve the same results?

View 6 Replies View Related

Queries :: Union Query Not Separating Results

Feb 18, 2014

I have a simple union query that looks like this:

Code:
Select DPTag from tbleqDP UNION ALL select ESGTag from tbleqESG

It is returning all the tags from both tables but putting them all under "DPTag." What I want is two columns in a report. One would have the header "DPTag" and all the values under it would be the tags from tbleqDP and the other would have the header "ESGTag" and all the values under it would be from the table tbleqESG.

What am I doing wrong such that it is returning all the values under the header DPTag?

View 10 Replies View Related

Queries :: Hide Field In Union Query?

Dec 11, 2013

Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.

View 3 Replies View Related

Queries :: Date Format In SQL Union Query

Nov 11, 2014

I have unified three queries , each query has a field "date",

format is dd/mm/yyyy.

The Union select query however, displays this "date" with different format , dd/mmm/yy

How can I fetch same format in the union query ??

View 3 Replies View Related

Queries :: Combining Like Records In Union Query

Nov 25, 2013

How can I add the resulting records from a union query. The results of the union are something like this:

Quantity Item

2 Cats
3 Cats
1 Dog
4 Mice

What I need to display is

Quantity Item

5 Cats
1 Dog
4 Mice

How can I get the query to "do the math"?

View 3 Replies View Related

Queries :: Union Query Ruins Numbered Order?

Jan 19, 2015

I have a union query with 2 fields: Order and Row_Heading_Full.

Code:

SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;

The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.

I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.

When I remove the union aspect it is ordered properly:

Code:

SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;

But that defeats the purpose because I am not adding a final entry of "25" and "Totals".

View 14 Replies View Related

Queries :: Union Query - Multiple Records On One Report

May 7, 2014

I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.

I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.

SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician

[Code] ....

I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.

I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...

I have created a report from the above union query and it works perfectly with only one serial number entered.

View 14 Replies View Related

Queries :: How To Sort A Union Query SQL Statement For A Report

Oct 1, 2013

I have a Union Query (that works perfectly fine) with the following code:

Code:
SELECT * FROM sbqryUseBattery
UNION
SELECT * FROM sbqryUseBeltsDeck;
UNION
SELECT * FROM sbqryUseBeltsHydro;
UNION
SELECT * FROM sbqryUseBeltsPTO;
UNION
SELECT * FROM sbqryUseFiltersAir
UNION
SELECT * FROM sbqryUseFiltersFuel
UNION SELECT * FROM sbqryUseFiltersOil;

I am using this information on a Report.

The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report?

View 14 Replies View Related

Queries :: Access Export Union Query Error

Jul 7, 2014

I have a database that is used to create a data file for a customer. The database has two linked tables that are linked to tables that are both in another database (but within the same database as each other). Their structure is identical.I have a union query set up to combine both linked tables.

I am using a Macro to export that query but after running for a short while I get the error "The query cannot be completed. Either the size of the query set....."Does Access have a limitation on union query size? Combined, the tables are a lot of data but I'm confused as both the tables I'm combining are in the same database.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved