Queries :: Count Multiple Columns As One Field

Sep 22, 2013

I 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

View Replies


ADVERTISEMENT

Queries :: Count Multiple Columns From The Table?

Aug 26, 2014

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 5 Replies View Related

Queries :: Count Records Across Multiple Columns With Certain Criteria

Jun 10, 2013

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.

View 3 Replies View Related

Queries :: Getting A Text Field Count From Multiple Tables

May 16, 2013

I am working with 4 tables and I am trying to get a count of the email address field in each table grouped by Email Group Name. I tried the dcount function but returned an error because some of the email addresses have an apostrophe in the field. Is there a way to do this in 1 query? This is an example of the result I would like:

EmailGroup/TtlEmailSents/TtlEmailsOpened/TtlEmailsClicked/TtlFormSubmits
AAA 50 30 15 10
BBB 100 75 50 20
CCC 60 25 5 1

Tables:
Emails Sent
Emails Opened
Emails Clicked
Form Submits

Field Names in all tables:
Email Group Name
Email Address

Field I want to count is Email Address for each table, grouped by Email Group Name.

View 5 Replies View Related

Queries :: Count A Field Multiple Times (sub Totals And A Total)

Jul 18, 2013

I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.

I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.

I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.

View 4 Replies View Related

Queries :: Consolidate Multiple Columns Into Two Columns

May 14, 2014

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] ....

View 2 Replies View Related

Queries :: Expression Or SQL To Count Query Columns?

Jan 7, 2014

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 Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

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 Related

Queries :: Put Multiple Records In Multiple Columns

Jul 31, 2013

I 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 ....

View 2 Replies View Related

Queries :: Sum From Multiple Columns

Jul 22, 2013

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

View 9 Replies View Related

Queries :: Top 5 Per Multiple Columns

Mar 11, 2015

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.

View 1 Replies View Related

Queries :: Sum Multiple Columns And Group?

Jan 15, 2014

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?

View 3 Replies View Related

Queries :: How To Conditionally Update Multiple Columns

Mar 29, 2013

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?

View 3 Replies View Related

Queries :: Counting Multiple Columns In A Query?

Apr 6, 2015

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

View 4 Replies View Related

Queries :: Matching Fields From Multiple Columns

Jun 26, 2013

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.

View 7 Replies View Related

Queries :: Return Max Date Of Multiple Columns

Jul 28, 2014

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.

View 6 Replies View Related

Queries :: Multiple Columns From Table Into One Column

Oct 14, 2014

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?

View 8 Replies View Related

Queries :: Multiple Rows Into 1 And Creating New Columns?

Nov 26, 2014

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.

View 5 Replies View Related

Queries :: Summing Columns Into Multiple Rows?

May 13, 2013

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?

View 8 Replies View Related

General :: Multiple Excel Columns To One Field

Apr 14, 2014

I have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:

Code:
sID|Code1|Code2|Code3
011|1234A|1234B|
021| |2345B|3456C
031|3456A|3456D|
041| | |7890E

The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.

This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.

I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.

This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:

Code:
eID|CodeX|FirsN|SecoN
011|1234A|Teddy|Bears
021|1234B|Harry|Horse
031|3456A|Edwin|Eagle
041|3456A|David|Tyrex

I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.

Code:
sID|CodeX|
011|1234A|
011|1234B|
031|3456A|
041|7890E|

My question is, how do I get all the Codes from the three different fields into a single field and relate it to the relevant sID for each?

View 1 Replies View Related

Queries :: Date - Multiple Columns Into Single Column

Jun 6, 2013

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.

View 9 Replies View Related

Queries :: How To Split Colum Value (with Comma) To Multiple Columns

Mar 8, 2015

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

View 2 Replies View Related

Queries :: Summing Days Late Through Multiple Columns

Aug 29, 2014

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] ......

View 1 Replies View Related

Forms :: Displaying One Field In Combobox In Multiple Columns

Jul 17, 2013

I have a multiple select combobox which shows up in many forms- SubCategories. The user can pick from a list of 154 SubCategories.

Everything is working properly however it is difficult for the user to scroll through the entire list.

My question: Is there a way to represent this ONE field in multiple columns in the combobox?

I know how to add columns for multiple fields to my combobox, but this is one field.

Maybe there is a better way to represent the data... a subform would have the same issue.

View 6 Replies View Related

Creating Multiple / Sequential Crosstab Columns From One Field

Jan 3, 2013

Number of Operators per Permit

Permit
Operator Name
6
065-24088
EOG RESOURCES INC
6
065-24088
PDC MOUNTAINEER LLC

[code]....

I want to take this data and create six new columns (operator1; operator2; operator3; operator4; operator5; operator6) for each permit number so that each unique operator name will be housed in each of those six columns, with the rows being defined by the permit number. So, I want to produce something that looks like this...

Permit

operator1
operator2

[code]....

At present, there are as many as 6 operators per permit, but the number of operators per permit varies from 1 to 6. I included an example that has 5 operators, to show that I want the sixth column to be null in this instance. So if a permit had only one operator, only the operator1 field would take on a non-null value in the new table/query.I know how to do this with 2 operators per permit by using min/max or first/last functions in a query, but I don't know how to deal with more than two operators per permit.

View 4 Replies View Related

Queries :: Filter Query Based On Some Criteria In Multiple Columns

Jul 25, 2013

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

View 5 Replies View Related







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