Queries :: Separating Values From Single Column Into Two Columns And Counting Them?

Oct 4, 2013

I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:

Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;

Crosstab query did the trick..

View Replies


ADVERTISEMENT

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

Counting Distinct Values Of A Column Group By Another Column

Dec 4, 2011

I have this table ("people") and an example of possible rows:

id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco

I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).

I have tryed in this way:

SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;

But Access says me there is a syntax error.

I'm working with MS Access 2002.

View 2 Replies View Related

Copying Access Table Columns To A Single Column In Excel?

Feb 1, 2005

HELP :confused:

Hi,

I need to copy 5 columns in an Access table into a single column in Excel. How can I do this?

Pictures To Help explain below:

http://uploads.savefile.com/users/uploads/1_154.jpg 261kb
http://uploads.savefile.com/users/uploads/2_154.jpg 192kb

View 4 Replies View Related

Possible To Remove Duplicates From A Query Based On Values In Single Column?

May 14, 2015

I have a query that displays this

Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleC TravelerD TravelerC TravelerE
TripTItleC TravelerE TravelerC TravelerD
TripTitleD TravelerF N/A N/A

I want to filter Column1 to have no duplicates so it looks like this

Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleD TravelerF N/A N/A

Is this possible?

View 1 Replies View Related

Queries :: Counting Multiple Dates In A Single Query Field

Feb 8, 2015

I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.

View 1 Replies View Related

Forms :: Single Column Combobox To List Values From Multiple Fields

Apr 8, 2013

I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.

All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.

View 1 Replies View Related

Tables :: Can Split A Column Values Into 2 Columns

Jul 3, 2014

I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

View 14 Replies View Related

Separating Numbers & Text Into 2 Columns

Dec 29, 2005

Hi!!

I have a quick question. I have a field that has information that looks like the below. I need it to be in 2 separate columns, but there is no space between the numbers and text otherwise I would know how to do this in excel. Anyone know how to do this is excel or access?

11000031377A & A AMERICAN DETECTIVE BUREAU


It should be separated as....

11000031377 A & A AMERICAN DETECTIVE BUREAU

Any and all help would be appreciated!!! Thanks!

View 2 Replies View Related

Modules & VBA :: Get Number Of Unique Values Without Separating Values That Belong To Same Block?

Dec 5, 2014

I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?

So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.

If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.

For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.

So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.

There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.

A few notes.

1.Getting as close as possible to the requested number is a requirement.

2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).

my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134

[code]....

A few sample outputs

one solution is taking rows 1-6 and 26.

OtherID
11098554
56200936
25138850
56148974
56530104
56148975

Another solution is taking rows 1-4 and 10-14.

OtherID
11098554
56200936
25138850
56024315
56115247
56148974

This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.

View 12 Replies View Related

Counting Results Of Multiple Queries And Displaying The The Reults On A Single Form

Apr 17, 2007

Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?

Thank you

Dan

View 7 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

Adding Values In Separate Columns Dependent On Text In Another Column?

May 16, 2013

I'm trying to make a database to track inventory or several items. Basically, I have four tables:

1) RawMaterialList - includes a list of all raw materials.
2) PartList - includes a list of all finished product using said raw materials.
3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials.
4) ShipmentRecord - contains details of daily shipments.

Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.

Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:

1) Item - a list of of raw materials from table RawMaterialList
2) Description - also dependent on info entered into table RawMaterialList
3) Quantity

But, I have 12 of these instances.

Item1, Item2...Item12;
Description1, Description2...Description12;
Quantity1, Quantity2...Quantity12.

My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.

Example:

1st Entry:
May 13
Item1 = PartA Description1 = PartA's description Quantity1 = 15
Item2 = PartB Description2 = PartB's description Quantity2 = 20

2nd Entry:
May 14
Item1 = PartB Description1 = PartB's description Quantity1 = 30

How do I get it to add up Part B to get 50pcs?

View 10 Replies View Related

Queries :: Counting Occurrences Of P - Summary Column In Crosstab Query

Feb 18, 2014

I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:

PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");

This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.

View 3 Replies View Related

Queries :: How To Distribute Different Values Of One Column In New Column Fields

Jan 30, 2014

In my table for duplicate "line no" I have different "contractor" like below.

LINE NO CONTRACTOR

L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000

Now I would like to make a query for transposing values like below:

LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3

L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000

how I have to make this query?

View 1 Replies View Related

Queries :: 2 Queries Into A Single 2 Column Query?

Jun 6, 2013

I would like to be able to take 1 column from 2 different queries and put them into 2 columns in a 3rd query.

View 6 Replies View Related

Queries :: Single Row Data Required As Column Header

Nov 4, 2013

Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.

(Access 2007 query)

View 1 Replies View Related

Queries :: One Column That Is Sum Of 3 Other Columns

Feb 13, 2014

I have an append query. I want to get a column that is the sum of 3 other columns:

Oil_T_C_(bpd) Gas_T_C_(MMcfd) LNG_T_C_(Tonnes_per_Annum)

1000 2000 3000

I want to get a column called Total_Throughput

What is the exact syntax that I have to put into the query design form?

so: Total_Throughput: than what?

View 14 Replies View Related

Queries :: Two Tables To Result In Single Column With Field Name Identifier For Each Record

Apr 27, 2015

How to get this one to display in a single column.

I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.

Customer Table with PK Customer_ID.

There are two tables with FK Customer_ID.

1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.

2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:

The Type shows up in a single column.

Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challenge:

Lease1 table has 3 fields that need to be transformed into a single column.

Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

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 :: Counting Text Values Based On Their Status

Jan 25, 2015

I have a question regarding counting of text values base on their status and using that result to a calculation.

Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.

What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.

The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.

I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.

View 2 Replies View Related

Queries :: Counting Values And Creating A Table For Each Defined Count

Apr 14, 2014

I am using an existing database which allows my company to track claims information. One report my supervisor has asked for is a table which shows the counts of each kind of event occurring at a district level by department. For example, the classifications are injury classes like FSA, so I need to design a report which says facility a has 3 fsa's in the Wireline department in a table format. I have been looking into union queries as suggested by other sites but nothing seems to work. My labels are District, Analysis Code and Department.

View 3 Replies View Related

Queries :: How To Show Row Values As Column Values

Mar 8, 2015

I have a table with following two columns

TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010

How can i show this data as components eqch version for a techname to be one component order need not follow

TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL

View 4 Replies View Related

Forms :: List Boxes - Single Column Versus Multi Column

Apr 3, 2013

How do I select the first column of a multi-column list box (called "List1") for a query.

A single column list box works fine.

Code:

SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;

View 6 Replies View Related

Queries :: Combining Columns And Replacing Certain Values

Sep 29, 2014

I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.

I have an example of what I'm working with (access file) and what result I want (excel file).

View 2 Replies View Related

Queries :: Sum Of Values With Same ID In Column

Aug 14, 2013

I am having some trouble with a program that contains a query that is suppose to sum a bunch of market values that have the same ID and is from the same portfolio.

The problem I had at first was that some of these IDs and portfolios that should have been summed together had one called counterparty that had different values.

So the problem that occured was the following:

ID Portfolio CounterParty SumOfMarketValue
5224 SPAC Counterparty1 -55
5224 SPAC Counterparty2 -45

What I wanted

ID Portfolio SumOfMarketValue
5224 SPAC -100

So what I did was that I deleted that column, but I am guessing that the table somehow might save that they have this difference. How I can make them appear in the same sum?

View 3 Replies View Related







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