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 Replies
ADVERTISEMENT
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 9 Replies
View Related
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
Apr 26, 2007
I have data that I would like to use within another system. In order for this information to work as desired I will have to "de-normalize" my tables into a single record set.
Here is the issue, I have 40k+ records that have the majority of the data I need in a single table. I have directory path links to the photos related back to the primary key.
I need to merge these into a single line of information.
Example:(this is simply an example and not the true layout)
Tbl1
PKEY De1 De2 De3
123 North 15 Blue
124 North 28 Green
Tbl2
PKEY Path PhotoName
123 M:Photo 123(a).jpg
123 M:Photo 123(b).jpg
123 M:Photo 123(c).jpg
124 M:Photo 124(a).jpg
124 M:Photo 124(b).jpg
124 M:Photo 124(c).jpg
124 M:Photo 124(d).jpg
The desired end result would look like
PKEY De1 De2 De3 Photo1 Photo2
123 North 15 Blue M:Photo123(a).jpg M:123(b).jpg
...and so on until all photos for the record have been listed out.
Any suggestions? If a search topic similar to my issue could be recommended I would appreciate it greatly.
View 1 Replies
View Related
Jun 20, 2013
I'm trying to build a query that will normalize some graphical data along the x-axis. To do this I need all the peaks to be at the same point. I'll just talk in generic terms of FieldX and FieldY for the X and Y variables (resp)
In my mind the steps require me to
1) Determine FieldX at Max(FieldY)
2) Determine difference between FieldX and arbitrary normalization point (probably 100k)
3) Shift FieldX at all points to put Max(FieldY) over 100k (its a log scale so I'll be dividing)
Steps 2 and 3 are easy, but step 1 is giving me grief.
Ok, so I know how to find Max(FieldY), easy enough. Now how do I find FieldX? My first thought is a complex series of subqueries. At some point in the past, where I was smarter than I am today, I created a query that would do numerical integration. Looking back at the query I can't understand it, but I think the principle will be similar.
View 1 Replies
View Related
Nov 21, 2014
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
View 6 Replies
View Related
Dec 6, 2013
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.
View 4 Replies
View Related
Oct 25, 2007
I have been tasked with producing a Service Catalogue - essentially, a list of services offered by IT to our business customers.
The Catalogue is a repository of information related to the service - such as, Service Info, Business Info, Server names and location, DB info, Batch and Online times, Network Info (and the list goes on).
Obviously, these are high-level categories. Within each of these (for example. Business Info) there is more granular info such as, Business Owner, Supported Business Area (there can be more than one) and Business Criticality.
Given the data and the fact that some category items may have one or many values for any given field (DB names for example), is this something worthy of a DB?
I have tossed this around in my head and I can't get away from the notion that I will require multiple tables with untold fields (yikes - scary thought!)
I have attached a rough DB but before I put any more time and effort into it, I want to know if I am on the right track.
View 3 Replies
View Related
Aug 22, 2005
i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.
i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.
i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.
i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.
All Help will be appreciated.
I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.
thanks,
sam
View 7 Replies
View Related
Jun 15, 2006
I am using a table a user created which is like:
Member ID (key field)
Visit Date
Dept 1
Expense Code1 (combo box E through I)
Dept 2
Expense Code2
This goes on through Department 20.
Now they want to know how many E's for one month. I am stumped on how to normalize this or if it is even possible! I thought maybe there is a way to search the table as if it were a spreadsheet doing a "countif" function??
Any replies much appreciated!
View 1 Replies
View Related
Feb 27, 2008
Does anyone know if there an access equivalent to the SQL server 'unpivot' command relating to crosstab queries or tables?
View 3 Replies
View Related
Oct 11, 2005
I have a performance evaluation db in MSA 2003 I am about done with. I had asked a question and got a helpful response. However, the response mentioned normalizing so I hit up google for more info. My db is not normalized, but after reading several sites, I don't see why I need to normalize it. The sites also said there are not a lot od reason not to normalize. I can't believe I just stumbled onto one. I have fields in my table that need to be scored on a 1-5 scale. I can see making a separate table for scores. However, the list of questions never grows or shrinks. It's a standard format that asks the same 20 or so question every time. Is there a reason to split the table into multiple tables? If so, would it be the scores vs. employee info tables? TIA for your assistance!
View 6 Replies
View Related
Nov 21, 2006
Hello I have a question.
I work for a company that had someone set up an Access database that generates work orders.
That person created a single LARGE table. 107 fields in the table. over 3000 records created so far.
Should I bother to try and normalize it?
Can it be Normalized?
Is it a pain?
Or should I just start over and create a new database. I thought about doing this but don't know if I can copy the old information into the new one.
Thanks for any advice you can offer.
View 3 Replies
View Related
Apr 15, 2005
I have a table -
TableID
PartNum
PartName
InstallTime_Shop
InstallTime_Site
InstallTime_Cadd
InstallTime_Testing
InstallTime Is a set amount of estimated time per part. This Value would be used to estimate time needed to install a part.
Should InstallTime_ be in its own table?
If it is in it own table would it one to one?
View 10 Replies
View Related
Sep 1, 2006
This has been a nightmare all around. Because I can't get 40 licenses for Access I'm stuck doing transferspreadsheets every day, which has worked out mostly, but I'm reaching the absolute limits of my knowledge. I can't even think straight and need help with a solution.
Here is the table data I'm bringing in (in a simple way)
Editor Client ResearchHits HitsSent AltEditor Pruned Edited
J. Doe Coca Cola 1000 500
J. Doe Coca Cola J. Schmoe 200 100
This data is brought in from two imports of data (there is actually a date column too that I didn't include). Now, what I need to do is have it subtract the "Pruned" from the "ResearchHis" and the "Edited" from the "HitsSent" while only showing Coca Cola and J. Doe once... is this possible?
Thanks.
View 3 Replies
View Related
Nov 6, 2007
If someone can help me with this I will send over $30 bucks for your time on paypal. I'm stuck bigtime and need some help.
Feel free to reply here, or e-mail me spork1138@gmail.com
Consider the sample data on patients, providers and visits given in the table below:
VisitNoVisitDatePatNoPatAgePatCityPatZipProvNoProvSpecialtyDiagnosis
V100201/13/2000P135DENVER80217D1INTERNISTEAR INFECTION
V100201/13/2000P135DENVER80217D2NURSE PRACTIONERINFLUENZA
V930301/20/2000P317ENGLEWOOD80113D2OBGYNPREGNANCY
V821101/18/2000P260BOULDER85932D3CARDIOLOGISTMURMUR
V342101/18/2000P465ENGLEWOOD80113D3CARDIOLOGISTIRREGULAR BEAT
Create a set of normalized tables, upto BCNF.
View 14 Replies
View Related
Dec 17, 2013
I have a large spreadsheet I need as the basis for an Access database.
The spreadsheet contains... Company Number, Name, Address, etc...
The Company number is consistant and always the same..
However the Name and address is different... eg LTD v Limited, ABC House v 34 High Street etc....
how I can prepare the data and get into access?
View 3 Replies
View Related
Dec 18, 2013
I have some data in an array that I need to normalize, remove duplicates, and import.
Original Table
Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.
Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____
New Data Structure
I want to group the data by HouseHold; which Address will serve to define for this import.
tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset
Dim rsHH As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim rsAddress As DAO.Recordset
Dim rsPerson As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
[Code] .....
View 2 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Apr 19, 2013
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
View 2 Replies
View Related
Feb 3, 2015
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
View 5 Replies
View Related
Jan 22, 2015
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
View 2 Replies
View Related
Nov 18, 2014
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
View 2 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Sep 20, 2014
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
View 3 Replies
View Related
