Query to format to columns

[bigroo]

Hello,

I am looking for assistance with a query that will combine my two tables of data, tblSiteScoreCard and tblVolumeMetrics for each month and transform into column format.



My tables at present have two months of data, but I will be adding data monthly to both tables. I have parameters within the query for Start Date and End Date querying the data, but I want to display 12 months of
data at a time on the report and I want each location displayed on one page from the tblLocations table.

Please review the end result picture for final output I want to see in the report.

Thanks

[June7]

Why are there multiple similar fields - Missed, Missed1, Missed2, etc? This indicates non-normalized structure.

Your data has to first be transposed so the field headers become row headers. Then a CROSSTAB query can transpose the date values to column headers.

A UNION query can manipulate the data into a normalized structure.

SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Device Count" AS DataType, [Device Count] AS Data FROM tablename
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Incidents", [Incidents] FROM tablename
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Requests", [Requests] FROM tablename;

A UNION can have 50 lines.

Building a report based on a CROSSTAB that will run perpetually is not easy.

Bing: dynamic crosstab

Review:
http://allenbrowne.com/ser-67.html
http://support.microsoft.com/kb/328320

[bigroo]

Thanks June7 for your reply!

I was asked last week to help out with making this database easier to extract data from it. From what I have been told, this database has been in use for a number of years and now, and what was done, the tables (two of them) were copied month over month for the new data to be added. The query was enormous with all the tables counting about 40 of them.

The design of the tables now in the uploaded database is what I came up with to try and simplify it query. I data is imported each month from another data source and feeds into these two tables via queries.
The png file view is from the old database, this is what they have used for the past few years and from what I can gather, they wish to keep. So, this is what I am trying to accomplish.

From what I can see from your Union Query, it seems to be only feeding from the tblSitescorecard only, how can I have the tblvolumemetrics feed in as well?
Also, how do I setup the report to be the same as the attached picture?

[June7]

To produce report like the image build a CROSSTAB query that uses the UNION as its data source.

I am not sure how to include tblvolumetrics. I am not understanding the relationship. Seems it should be 1-to-1 but they don't have the same number of records and there is no common unique ID. The KeyID comes close but has different suffix in each table.

[bigroo]

Hi June7,

The relationship I came up with was like this.
I am using the tbllocations as the primary key. I took the first 3 to 4 letters of the location name for my locid. I then thought ok, one location to many months of volumes produced on their printers and many months of performance of the printers. I made up the key on the date, locid and table name to get a unique id.
With the tblsitescorecard, this shows the performances of the printers month over month, so I followed the same for creating the Key ID.

Is there another method to tie these tables together?

[June7]

Two options:

1. remove the suffix and use KeyID as a common unique ID

2. compound join on LocID and YearMonth fields

However, the tables don't have the same number of records which means the same unique keys are not in both tables so some records in each table will be lost when tables joined.

Need a 'master' dataset of all unique IDs that the two tables can be joined to. A UNION query can generate that dataset. All UNION queries must be typed into SQL View window.

SELECT Left([KeyID], InStrRev([KeyID],"-")-1) AS Key FROM tblSiteScoreCard
UNION SELECT Left([KeyID], InStrRev([KeyID],"-")-1) FROM tblVolumeMetrics;

Then join the two tables to AllKeys query. The result should be 218 records.

SELECT AllKeys.Key, tblVolumeMetrics.*, tblSiteScoreCard.*
FROM tblVolumeMetrics RIGHT JOIN (tblSiteScoreCard RIGHT JOIN AllKeys ON Left(tblSiteScoreCard.KeyID,InStrRev(tblSiteScoreC ard.KeyID,"-")-1) = AllKeys.Key) ON Left(tblVolumeMetrics.KeyID, InStrRev(tblVolumeMetrics.KeyID,"-")-1) = AllKeys.Key;

Also, the SELECT query joining to AllKeys must be typed into SQL view. It will not properly display in Design View because of the string manipulation of the KeyID fields. However, an AllKeys table using option 2 (compound field join) would allow the SELECT query to be built with Design View.

Now use that SELECT query as source for the UNION query described in earlier post.

Then do the CROSSTAB with that UNION query.

[bigroo]

Thanks June7 for your guidance and help and being patience with me, this is really an education for me into the world of Union Queries!

OK, I am down to this step:

Now use that SELECT query as source for the UNION query described in earlier post.

Then do the CROSSTAB with that UNION query.

SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Device Count" AS DataType, [Device Count] AS Data FROM tablename
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Incidents", [Incidents] FROM tablename
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Requests", [Requests] FROM tablename;

Not too sure what to do here, I am guessing I need to take the 2 Union rows and add to my select statement script but it tells me I don't have enough columns.

[June7]

No, do not 'add' the UNION rows to the SELECT query that joined tables to AllKeys query. Just follow the steps as described. The instruction was to use the query, not add UNION lines. The UNION query is another query that uses that query as its data source.

Where the UNION query refers to 'tablename' will be the query name that joined the two tables to AllKeys query.

Look at it this way

SELECT ... queryname
UNION
SELECT ... queryname
UNION
SELECT ... queryname;

Up to 50 SELECTs in a UNION.

[bigroo]

June7,

I don't understand what you are trying to tell me!
I have attached the database as I have it now, can you show me what you are saying?

[June7]

Query1: UNION query to build AllKeys master dataset

Query2: SELECT query that joins tables to AllKeys query, results in 218 records

Query3: UNION query to transpose field headers to data attribute and normalize data structure using Query2 as the data source

Query4: CROSSTAB query to transpose Year/Month data to column headers and do aggregate calcs using Query3 as the data source

[bigroo]

Sorry June7,

I cannot make any head or tail of this, I spent a few hours on this last night, with no joy!
I really need some assistance with this!

[bigroo]

I had some time today to pick this up again, I found this Transform query, June7, is this what you are refereeing too when you say:

Query3: UNION query to transpose field headers to data attribute and normalize data structure using Query2 as the data source

TRANSFORM Sum(([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Sales
SELECT Orders.ShipCountry FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE (((Orders.OrderDate)>=[StartDate]))
GROUP BY Orders.ShipCountry
PIVOT Year([OrderDate])*12+Format([OrderDate],"mm")-(Year([StartDate])*12+Format([StartDate],"mm"))+1

IN (1,2,3,4,5,6,7,8,9,10,11,12)

Also, when you say Normalize, what does that mean?

[June7]

TRANSFORM PIVOT is a CROSSTAB query.

To get the output you want, your data needs to be 'normalized'.

Bing: database normalization

Generally, in a normalized structure tables will be long and narrow instead of wide and short.

Your data needs to be restructured so that a CROSSTAB can pivot on the YearMonth values and do aggregate calcs on Data by DataType.

SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Device Count" AS DataType, [Device Count] AS Data FROM Query2
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Incidents", [Incidents] FROM Query2
UNION SELECT KeyID, LocID, YearMonth, [Key Service], [Total Tickets], [XOS User Services], "Requests", [Requests] FROM Query2;

KeyID	LocID	YearMonth	KeyService	TotalTickets	XOS	DataType	Data
Alb-2013-09	Alb	9/1/2013	Albany plant	39	0	Device Count	88
Alb-2013-09	Alb	9/1/2013	Albany plant	39	0	Incidents	16
Alb-2013-09	Alb	9/1/2013	Albany plant	39	0	Requests	21
Alb-2013-10	Alb	10/1/2013	Albany plant	32	0	Device Count	88
Alb-2013-10	Alb	10/1/2013	Albany plant	32	0	Incidents	12

Even this is not fully normalized structure because of the repetition of values in the first 6 fields but it gives a dataset that would be the result of a query that joined two normalized tables.