Queries :: SQL Crosstab Query With Multiple Grouping Levels - Transform Statement?

Mar 24, 2015

I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.

The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)

The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.

This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!

Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

View Replies


ADVERTISEMENT

Queries :: Transform Table With Multiple Rows To One Row?

Dec 22, 2014

I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:

Member_IDDiagnosis
10000Hypothermia
10000Frost Bite
10001Fatigue
10001Dehydration
10001Exhaustion
99999Exhaustion
99999Hypothermia

And I'd like to convert it to this:
Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4
10000 HypothermiaFrost Bite
10001 Fatigue DehydrationExhaustion
99999 Exhaustion Hypothermia

The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.

View 3 Replies View Related

How To Transform Data To Crosstab-like Format?

Apr 1, 2008

May i reach following function by Grid Query design or by SQL


Original:
==============================================
Factory 1-----------Product A
Factory 1-----------Product B
Factory 1-----------Product C
Factory 1-----------Product D


Factory 2-----------Product D
Factory 2-----------Product E
Factory 2-----------Product F

Factory 3-----------Product D
Factory 3-----------Product E
Factory 3-----------Product F


Result:
==============================================
Factory 1-----------Product A,Product B,Product C,Product D
Factory 2-----------Product D,Product E,Product F
Factory 3-----------Product D,Product E,Product F

View 7 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Queries :: Append Query In Different Levels Of Tables

Dec 17, 2013

I receive the data from engineering team lets name it as MAster database( in DB2 database - Read only ) and then i want to assign the data to each member and staus will be updated by them.So my Idea is upload the data from Engineering team (MAster database) to my (Local database), i think of using Append query if there is any changes in engineeiring data after i append the data then i can use the Update query to modify the data.

But once i assign the job to every member from my Localdatabase, i want it to stored in a data base with their name because it is a huge volume data and it will go for years.The bottleneck is If i use the Append query here, the changes from Masterdatabase to Locadatabase will not reflect in Team members database? is any solution for this or i have to use the Update query in Localdatabase and also in Team memers database?

View 1 Replies View Related

Queries :: Update Query For Stock Levels

May 12, 2015

I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....

I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.

View 6 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Queries :: Transform Number Of Characters In A Field

Mar 28, 2014

I have a table with acct numbers of various lengths, ie:

246
18
36895
352119
472

I need to run a query so that all of the acct numbers are 8 characters long with zeros as placeholders in front of the actual acct numbers, ie:

00000246
00000018
00036895
00352119
00000472

I tried using the expression NewAcctNo: 00000000+[AcctNo], but that did not fix the problem.

View 2 Replies View Related

Multiple Levels Of Data

Jan 14, 2008

I'm fairly new to access, having done a lot of what would have been easier with access in excel. I've decided to make the switch but need one particular thing that I can't seem to work out.

I have a series of "Courses" which contain a series of "modules" and each module has various amounts of data that needs to be accessed.

Is it possible to have module tables embedded in the courses table. Or, do I have to just have one massive table that contains ALL the modules with a field that says what course they belong to. Then make queries that display only the modules of that course? Or is there another way to do it?

I also haven't gotten to learning about queries yet, so if this is the way to do, can anyone explain how or better yet, point to a tutorial that can explain how this is done?

View 6 Replies View Related

How To Handle Multiple User Levels

Aug 23, 2011

I've got this database that needs some users to be read only and some users to have full control. I applied the appropriate ntfs permissions but there is a problem. If a read only users open the database, and then a full control users opens the database its stuck in read-only.

The database is a 2007 accdb.

View 2 Replies View Related

How Do You Make A Multiple Crosstab Query?

Dec 18, 2005

Hi there thanks for all your help on the last problem got it working now. What I need now is 2 crosstab queries to become one, example below on how I want it to look:

Created By-----Total Of Date-----Total Of Date Taken-----Policy Name Type (eg Breakdown,Caravan going across counting Date and Date qouted in separate columns)

Here is the SQL for each crosstab query:

TRANSFORM Count([Date]) AS [The Value]
SELECT [Created By], Count([Date]) AS [Total Of Date]
FROM qryPresonalReport
GROUP BY [Created By]
PIVOT [Policy Name Type];

TRANSFORM Count(qryPresonalReport.[Date Taken]) AS [The Value]
SELECT qryPresonalReport.[Created By], Count(qryPresonalReport.[Date Taken]) AS [Total Of Date Taken]
FROM qryPresonalReport
GROUP BY qryPresonalReport.[Created By]
PIVOT qryPresonalReport.[Policy Name Type];

Cheers

Headintheclouds

View 1 Replies View Related

Multiple Queries, One Statement?

Aug 15, 2005

Hi all,

I thought the whole point of the ; sign at the end of a sql statement in access was so that you can
run more than one query at a time?
if this is the case, could you tell me why this doesn't work?
Code: sqlStr = "UPDATE tabControlFeeType SET FeeAmount = " & txtFirst.Text & " WHERE KeyFeeType = 1;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtSecond.Text & " WHERE KeyFeeType = 2;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtThird.Text & " WHERE KeyFeeType = 3;" Dim comm As New OleDbCommand(sqlStr, conn) comm.ExecuteNonQuery()
the query doesn't work if I put it straight into access either?

do I just have this wrong?

Thanx

View 2 Replies View Related

Queries :: PROPER Function - Transform Names In Upper Case In Capitalized Word

Dec 16, 2013

I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that

Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName

Which as you can see is quite a complex statement, how can I simplify it?

Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca

View 14 Replies View Related

Queries :: Grouping Number Field Into Intervals In A Query

Jul 2, 2014

I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:

0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet

This is the query I'm coming up with so far

JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600

I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.

View 5 Replies View Related

Reports :: Access Report Chart Across Multiple Group Levels

Jan 22, 2015

I know how to create a chart in an access report, put it in the group header or footer and link it such that the the grouping filters the chart.

My VP wants me to create a chart that compares each invididual providers episiotomy rate (something that is done to pregnant women during delivery) to the overall divisional rate on a monthly basis.

I know how to do this with 2 charts, ie 2 different group levels. One chart goes in the date grouping level (the overall rate) and another goes in the provider grouping level (the individual provide rate).

is there a way to have this in one graph in an access report?

View 5 Replies View Related

Queries :: IIF Statement That Would Change Value To Multiple Fields

Oct 15, 2013

I have a problem that I can't seem to solve in SQL for my access 2010 query.

Let's say I have a the price of and for every year.

In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)

I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.

View 2 Replies View Related

Modules & VBA :: Changing Stock Levels Of Multiple Product Components By One Command

Feb 11, 2014

I have the following form and subform combination:

The subform is linked to the main form by way of the "ProductID" control. (It's hidden in the subform).

Basically, each product (the finished article) is made up of many components.

What I would like to do is this:

1. Increase the stock of a "Product".
2. The Stock level control on the main form will then increment as much as selected
3. Contrary to that, I would like each component to decrement the relevant amount based on how much of the component is used up to make the product.

E.g. If I have Product A and it contains 2 components of which component A is used once per product but component B is used twice, then when I increase stock of product by 1, then component A goes down by 1 and component B goes down by 2. But If I put two of a product in to stock, I would obviously need component A to go down by 2 but component B to go down by 4.

I am thinking I need some way to loop through each record in the sub form for the current form, such as "Do While Not", or a "For Each". And then stick an Update query within the loop, but to be honest I can't think of how I need to address it!

View 10 Replies View Related

Queries :: Setting Zero Value In Crosstab Query

Jun 19, 2014

I have a crosstab query to summarise the counted string values from another query: E.g.;

TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;

As there are some null values returned (ie blank cell in the pivot table)

I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;

So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:

Viable Not viable
14 12
displays as 1412

So how do I add the values in the rows???

View 1 Replies View Related

Queries :: Data On 1 Row Crosstab Query

Sep 17, 2014

Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?

Code:
PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime;
TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM
SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM]
FROM Query1
WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null))
GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp]
PIVOT Format([Date Stamp],"Short Date");

View 4 Replies View Related

Queries :: Crosstab Query With No Aggregation

Apr 16, 2013

I have the table "tblProductionForecast" with the following fields:

ProductionForecastProductId
ProductionForecastMonth
ProductionForecastQuantity

I want to show this in a report, similar to an spreadsheet, with:

ProductionForecastProductId = Rows
ProductionForecastMonth = Columns
ProductionForecastQuantity = Data

I thought of using a crosstab query like this:

Code:
TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity
SELECT tblProductionForecast.[ProductionForecastProductId]
FROM tblProductionForecast
GROUP BY tblProductionForecast.[ProductionForecastProductId]
PIVOT tblProductionForecast.[ProductionForecastMonth];

but I don't want the average of ProductionForecastQuantity. I want the actual value!

Is a crosstab query the wrong method to use?

If no, how do I show the actual value of ProductionForecastQuantity

If yes, how should I create the report?

View 5 Replies View Related

Queries :: Sum Data Across Crosstab Query?

Nov 12, 2013

I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.

View 1 Replies View Related

Queries :: Query Error When Data Changes In Crosstab Source Query

Aug 12, 2014

I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.

I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .

My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.

View 9 Replies View Related

Queries :: Sort Row Headings For Crosstab Query

Feb 26, 2015

I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:

10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc

But I need to show it as:

3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.

The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.

View 4 Replies View Related

Queries :: Crosstab Query With Fixed Row Headings

Apr 23, 2014

I've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).

View 3 Replies View Related

Queries :: Crosstab Query Using Dates For Next 12 Months

Jun 23, 2014

I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.

I could represent an activity using a colour i.e. yellow for delivery, green for design etc.

Also do I need to create a table with all the dates? Is there a quick way of doing this?

View 7 Replies View Related

Queries :: CountIf Between Dates In Crosstab Query

Nov 4, 2013

I have generated a table using a crosstab query and it gives the information as below

12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5

So basically part, qty used, year/month used - it works fine up to this point.

Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months

Or

Create another query to do this.

But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).

The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.

I use the design view to generate queries.

View 1 Replies View Related







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