I want to count data from my table ( Table ) and to display result like a dashboard (Desired result ). To count this do i need to write query for every column and then link to final column? i have totally 300 columns to count I am attaching Table & desired result images;
View RepliesI have a problem to count a multi columns into one field. For example, I have a table consist of these fields (simplified version):
- id
- block_name
- handle_type (lookup: big and small)
- kernel_type (lookup: big and small)
- fruit_set (lookup: 1, 2, 3 and 4)
I want to create a query to count a combination of handle_type and kernel_type columns as one column. So the query field would shows these fields:
- block_name
- count_of_big_handle_big_kernel
- count_of_big_handle_small_kernel
- count_of_small_handle_big_kernel
- count_of_small_handle_small_kernel
- count_of_fruit_set_1
- count_of_fruit_set_2
- count_of_fruit_set_3
- count_of_fruit_set_4
I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".
What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.
I'm pretty new at Access queries so I don't know how to use a count statement to pull from all those queries AND get the corresponding date to be in range.
I am working with other data that has been created by someone else.
There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.
So far I have tried using this SQL:
SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T
[Code] .....
But it is not working - Is it to do with the field names or am I entering something incorrectly?
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.
View 13 Replies View RelatedI wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies View RelatedI have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
See the attached file ....
I have a table of Cabinets. Each cabinet has 20 fields for Hardware (HW1, HW2 etc)and 20 for Hardware Quantity (HW1Qty, HW2Qty etc). Not all fields are populated.
Hardware is entered as an ID number
eg "Hinge" = 620 and "Handle" = 750
So say there are 3 Cabinets in a Job Order
Cab1: [HW1] = 620, [HW1Qty] = 2
Cab2: [HW4] = 620, [HW4Qty] = 4
Cab3: [HW1] =750, [HW1Qty] = 2 AND [HW3]=750, [HW3Qty]=1
Each Job Order (list of cabs) is different.
I need to total all the different hardware items, no matter which column (HW1, HW2 etc) they might be in.
So I would end up with totals like :
620 (Hinges) = 7
750 (Handles) = 1
for each Job Order
I have a database that I just imported a table and I am trying to get the top 5 by desc order of my member count. The problem is some of the counts are duplicated as 1 but the other columns are not a duplicate. I am attaching an example of the Excel I am importing, but this is just an example. I have multiple product across all states and products and then there is WLP, AGP, MMP under Legacy.
Then I have the top 10 top1dx and the ranking is actually 1 to 10. The layout was pulled asking for the top10 diagnosis or dx1 and then give me all the dx2's that fall under that dx1. So I might have 100 dx2's but only need the top 5 but as I said with the count of 1 on many, if I do my query, it will give me the top 5 and chop off any 1's but then I don't get each ranking 1 through 10. I will post an example also of what I need for the output and just having a difficult time with the right query. Here is the query I used:
Code:
SELECT OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT, OP.ADMITQTY, OP.STATE, OP.PRODUCT
FROM OP
WHERE ((([OP].[TOPRANK]) In (SELECT TOP 5 TR.TOPRANK
FROM OP as TR
where OP.TOPRANK = TR.TOPRANK
ORDER BY TR.MEMBERCOUNT DESC)) AND (([IP].[MEMBERCOUNT])>1))
GROUP BY OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT, OP.ADMITQTY, OP.STATE, OP.PRODUCT
ORDER BY OP.MEMBERCOUNT DESC;
Even if the top 5 ends up showing only 3 in the mix, that is fine. As long as I have in the end every legacy, product, state, the dx1, dx2, toprank accounted for by memebecnt desc.
I have a table that looks something like this in structure:
P_date (date)
Location_No (integer)
Invoice_type_A (integer)
Invoice_type_B (integer)
Invoice_type_C (integer)
Invoice_type_D (integer)
I want to create a query which shows the sum of the number of all four invoice types for a specific date regardless of the Location_No. By putting something like "Invoices_total: Invoice_type_A + Invoice_type_B + Invoice_type_C + Invoice_type_D" in the top field I am able to get the query to show me the total number of invoices per date AND location, but I would like the total number of invoices for a given date regardless of location number. How can this be done?
I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.
For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.
To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.
It was simple enough to do this one column at a time...
UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"
WHERE ((([Capture history intermediate].[1971]) Is Null))
But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.
So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately?
Student ID
Command over subject
Teaching Subject
Explaining things
Pedagogy Methods
Solicited_Participation_Class
1
Good
Average
Bad
Good
Bad
2
Bad
Average
Good
Bad
Good
3
[code]......
I have a table given above, table name is student. Which has following sample data.
I want to count the no. of students who say Good, Average ,Bad for every indicator Output from query:
Good
Average
Bad
Command over subject
2
3
1
Teaching Subject
1
5
0
[code]......
How this can be achived from query in MS Access 2010
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
How do you return the most recent date of multiple columns.
I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.
The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.
We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?
Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.
I've managed to do it in SQL using GREATEST() but that isn't an option in Access.
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
I have a linked table. The table looks like:
Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3
I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:
Course name | Course Grade
Student 1
Student 1
Student 2
Student 2
What is the best way to do this?
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
how to split this , I am new Access DB, I worked in Sql Server but access new to me
I have the below data
Appid Names
100 John,Bob,Kondya, Lima
200 Shor,James,Kim
300 Cinem
I want to convert to
Appid Names Name1 Name2 Name3 Name4
100 John Bob Kondya Lima NULL
200 Shor James Kim NULL NULL
300 Cinem NULL NULL NULL Null
I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.
Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] ,
[Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] ,
[Code] ......
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...
Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training 1/1/2010 5/1/2010
1 John Smith Python Training 1/30/2011 4/1/2011
1 John Smith Leadership Development 6/27/2013 9/1/2013
1 John Smith Sensitivity Training 9/5/2010 -
2 Hank Joel MS Office Training 8/1/2010 10/1/2010
2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010
2 Hank Joel WHMIS Training 11/15/2010 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012
To turn like this:
Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, -
2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012
I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:
Quote:
SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];
However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:
Quote:
WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")
I placed the following in VBA module:
Code:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
[code]....
I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.
The following works for one column:
SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";
Are there any suggestions?
Thanks
I know how to have multiple columns fill a lookup in a combobox both from a table and a query. But I need to have the other fields that aren't saved by the combobox saved in the neighboring columns. So, my primary table is a master list of chemicals to be analyzed along with their respective registry numbers. I know what most of the programmers say about repetitive data being bad form etc. These names and registry numbers will NEVER change, so I'm not worried about a change causing problems later. I'm trying to build a separate tables that will have specific chemicals and the methods that they are analyzed under that effectively copy from the master list, but add their own quality control criteria. Further more, not all of my clients need all of the chemicals that are available for every method. It should be noted that not all chemicals are analyzed by the same methods and that some methods will have some of the same chemicals as others. I need the registry numbers because this is what the analysis software uses to uniquely identify each chemical and I need the name because names are easier for me. Long story short, I need both of these pieces together. It was suggested to have a macro copy the remaining columns from the dropbox in the table to the other columns in my table, but I'm not sure how to do this (I'm still very new to Access and my VB is very rusty). I understand how to do this for a form, but when client reporting lists become involved later on, this will make my database very bloated to have a form to populate each respective table.
View 1 Replies View Related