Queries :: Crosstab Query That Keeps Each Contract On One Line And Separate Fields For Each Contact
Jun 4, 2014
I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:
Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally
I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.
I want to be able to take two separate queries to create a 2 line graph. Also to be able to spice the graphs up some. The ones I have done, single line, even seems dull.
I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?
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
I have a sign up form where people select their name for the time slot they want, there are 2 spots open per time slot, so I have Interviewer A and Interviewer B. Records can then either just be a name in A or a name in A and B.
I would like to run a report that shows how many times all the interviewers have signed up in a date range.
I can do this individually by looking up one name at a time with this query:
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted FROM Interview_Schedule WHERE (((Interview_Schedule.Interviewer_B)=[Forms]![Main]![NavigationSubform]![cboSup])) OR (((Interview_Schedule.Interviewer_A)=[Forms]![Main]![NavigationSubform]![cboSup]) AND ((Interview_Schedule.Interview_Date) Between [Forms]![Main]![NavigationSubform]![txtStartDate] And [Forms]![Main]![NavigationSubform]![txtEndDate]));
My question is how do I get a report to show all agents at once with their own interview count?
I can make 2 separate queries to look up each field, for interviewer A and Interviewer B, then run a join query, but this results in 2 counts for each agent and I can't sum the two totals together....
SELECT Sum(Interview_Schedule.ID) AS SumOfID, Sum(Interview_Schedule.Completed)*-1 AS CountofYes, Count(Interview_Schedule.Completed) AS CountOfCompleted, Interview_Schedule.Interviewer_A FROM Interview_Schedule
I then have a crosstab query that counts the different "AggType" like this. (This data is output to a report as both data and a chart)
Verbal, Physical, Threat 2, 1, 1
This all works great IF the date range is large enough that all 3 AggType fields are included. BUT, I my date range was from 01/05/2006 to 31/05/2006, I would only have 1 record (23, Agressive, 14/05/2006, Threat ) so the crosstab would result in ONE field only. (Threat)
The problem is that my report is looking for 3 fields (Verbal, physical and Threat)
How can I stop the report from giving an error if fields are missing from the crosstab result?
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE;
----------- qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
I have multiple queries that I need to have filered by the same parameter (Client). There is a list of clients that receive this report (the report has a pre-made template with multiple tabs, hence the multiple queries).
Currently the user of the database has to filter the exported excel file for each client re-save and email basied on the client. The above is done when the user pushes the specified button on a form (which just says run report a on it).
I am trying to figure out how the write a code so that a parameter (or table if that would work better) pulls the first record of the Client query to filter the other queries being run. Then to loop that process with the second record and so on through the Client query until all the clients have had the report run for them.
Attached are the current VBA being used and the current SQL for the first query that needs the parameter value added.
the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?
Code: Set db = CurrentDb Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab") indexx = 0
I have 3 forms that all use the same sub form. The 3 main forms show 3 different project types, where the sub form shows what other projects that a company is engaged with. Rather than create 3 different queries for my sub form, i would like to just filter it using criteria that looks at the CompanyID field on my 3 main forms. Currently my criteria is like this:
[Forms]![frmProjects_Detail_IND]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_CSS]![txtcompanyid] Or Like [Forms]![frmProjects_Detail_TAP]![txtcompanyid]
Is there a way to ignore the parameters that are null?
I have an database that uses a couple of different date ranges, so I created a table that shows the different date ranges that may be required (xReport Dates) so I didn't have to keep manually editing queries or entering dates every time.
I have one query that appends data from one table into another based on a date range that you need to manually enter when prompted; I can't seem to get it to refer to my xReport Dates table for the range.
I created a Contact database where I have the date and time as two separate fields in one of the tables. I have them defined as such, but would prefer that the fields wait until I enter data into them and not auto fill with the current information. Is there a property of these fields I can set so that these fields do not get auto filled? Again this database is too large to send, so I hope that you can help without it.
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:
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");
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
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.
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef" Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
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.
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
I want to run a query that will show a single line per employee, and different columns for each of the cost centers the employee was in. If the employee was in 3 different cost centers, the query would have 4 columns (1 for the employee, 1 for the first cost center, 1 for the second cost center, 3 for third cost center).
I saw another thread to linked to a concatenate example by Allen Browne, but that places multiple values in a single cell. I want different columns per cost center.
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
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.
