Add A Column In A Query

Feb 26, 2007

Hello,

I'd like to add a new column in access query (see attached) that within the same batch if there is a "f" in the "passOrFail" column, then in a new column put a "f" for the whole batch, otherwise put a "p" for the whole batch.

Any help would be greatly appreciated.

View Replies


ADVERTISEMENT

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Queries :: Change In Column Based On Base Query Column

Mar 24, 2014

I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...

Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".

The error is because the second layer of query does not identifies Q1 2014.

How do i make access change the column automatically when the Q1 changes to Q2...

View 1 Replies View Related

Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column

May 21, 2015

I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).

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

Populating A Column In A Table Based On Values In An Existing Column

Mar 5, 2007

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc


I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.

So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc

How do I do this?

Kind Regards,

Matthew

View 1 Replies View Related

Modules & VBA :: Input String In Column Based On Data In Another Column?

Nov 30, 2014

I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.

Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.

These are the codes that I am using to achieve this, however nothing is being input into the EventType column.

Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")

[Code] ....

I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.

View 4 Replies View Related

Queries :: Find Unmatched Contents From The Column By Matching With Another Column?

Nov 16, 2013

i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.

View 1 Replies View Related

Can I Use A Column As Query?

Feb 26, 2007

Is it possible to use a column as query?

Normally, with a multiselectquery you type in several criteria(postal code) like;
3311 aa
1245 bb
1234 bc

which is all very well, but what if you have 100 postal codes (dutch postal codes and without a logical order). In this case it would be nice if you could use a table with one column (the postal codes) to use as criteria.

View 12 Replies View Related

Column(1) In Query

Nov 8, 2007

Hi all,

From another post that I have been trying to help with I have a question.

Is it possible to create a querie that has, in a criteria

Like Forms![Form1]![Lookup].Column(1)

Lookup is a combo box with 2 columns

It throw up an error message "Undefined function........."

Any help would be appreciated.

Garry

View 10 Replies View Related

Referencing Dropdown Column Diffferent To Boun Column

Dec 14, 2006

hi,

im wanting to reference a value of a dropdown which is already serving a function - the dopdown already has it's bound set to 3 but i want this new function to refercne the second column-

im tyring:

"Tutor = '" & Me.ComboStaff.Value(2) & "'"

which isn't working- how do i reference the second column of a dropdown if its bound to its third?

cheers

dubs

View 2 Replies View Related

Moving And SORTING Multiple Column Data Into One Column

Feb 25, 2008

Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:

Original data layout:

Column Headings: Sample Event, Depth 1, Depth 2, Depth 3,
1st Row Data: 1, 6, 9, 12, 9
2nd Row Data: 2, 7, 9, 8, 3

Desired data layout:

Column Headings: Sample Event, Depths
1, 6
1, 9
1,12
1, 9
2, 7
2, 9
2, 8
2, 3

So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?

I appreciate any help anyone may have to offer. Thank you.

SELECT Depth1 AS Depths
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth2
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth3
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth4
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth5
FROM Depth_Velocity_Substrate_Correct

View 5 Replies View Related

Modules & VBA :: Convert Number Column Into String Column

Feb 28, 2014

I want to convert a number column into a text column.

Number Column is called Customer.

Below I can't match to the other table containing the text column Customer No.

How can I convert columns in vba?

Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"

Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)

DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"

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

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

How To Highlight One Column And Change All Data Inside Of That Column To Same

Aug 18, 2011

I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?

View 1 Replies View Related

Add A Column To The Table With A Query

Jul 30, 2007

Hi there,

I have a big table bringing Access up to almost 2G limit.
I need to add a column to that table using a query.
I can’t make another table because of the volume.

I have 3 columns like

Company Division Branch
W X P

I need to add the column having

Company Division Branch BranchCode
W X P WXP

Thank you.

View 12 Replies View Related

COlumn In Query With Table Name

May 31, 2005

How can I create a column in a query that reflects automatically the table name? Example: Table_ABC. has fields: ID, Material, Qty. I want to create automatically a column/field in the Qy_ABC with "ID", "Material" and "QTY", and in additon into it, next to "Qty" a filed/column is called "Name" and filled with "ABC". Thanks.

View 2 Replies View Related

Only First Column Of Listbox For Query

May 19, 2006

I'm still a little new to this, so please bear with me. I've searched to no avail for what I am looking for, so if there is an existing thread and you would rather point me in it's direction as opposed to answering my question, that would be fine.

I want one of the parameters of my delete query to be only the first column of my "ProdList" listbox, but don't know how to specify this. This is what my query looks like so far... Can someone tell me how do i need to change it?

DELETE ProductionLog.Username, ProductionLog.Date, ProductionLog.OrderNumber
FROM ProductionLog
WHERE (((ProductionLog.Username)=fOSUserName()) AND ((ProductionLog.Date)=[Forms]![Production]![TheDate]) AND ((ProductionLog.OrderNumber)=[Forms]![Production]![ProdList]));

View 2 Replies View Related

Can I Add Totals To A Query Column?

Jul 10, 2007

I have a rather simple query that shows the number of layoffs in a given period and would like to know if I can have the query sum the numbers.


Company City Date Incident Laid-Off
CompanyABC CityABC 5/3/2007 Mass Layoff 207
CompanyKLM CityKLM 5/5/2007 Mass Layoff 54
CompanyQRS CityQRS 5/7/2007 Plant Closing 436
CompanyXYZ CityXYZ 5/9/2007 Plant Closing 75


Is it possible to somehow display the total (772) underneath the "Laid-Off" column? Or create a separate column that only displays the "Laid-Off" total?

Or would it be easier to just build a report from the query and display laid off totals on the report?

(I apologize for the "table." I tried to import the query results as an image but couldn't get it to work.)

Thanks in advance for your help,
Kerry

View 3 Replies View Related

Crosstab Query Contains <> Column

Nov 29, 2007

I have constructed a crosstab query in order to populate a stacked column chart.

Everything is fine except the crosstab query seems to contain an extra blank column with the header <>. This is therefore appearing in the chart when I don't want it to.

As the crosstab query is itself based on a select query I think the <> column is a reference to a blank row in the select query.

The blank row in the select query is itself the "new record" row from the underlying table.

Whether significant or not one of the data types is an autonumber.

To solve my problem I think I need to find a criteria to use in the select query that would exclude the new record row. I've tried to use the autonumber field but various "is null" "is not null" <>"" statements don't seem to work.

I basically want to include all populated rows but exclude the blank "new record" row.

View 1 Replies View Related

Rank Column In Query

Feb 20, 2008

Hi,

I am trying to add a column into a query to rank items in that query.

For example, I want this table:

Name Score
Lee 10
Simon 8
Steve 8
Mike 6

to read:

Name Score Rank
Lee 10 1
Simon 8 2
Steve 8 2
Mike 6 4

I have read the Microsoft help but everytime I try and change the alias name of the query within a query, Access automatically says it is closing??

Any ideas

Many thanks in advance

Lee

View 1 Replies View Related

Summing Column In Query

May 2, 2005

I have a query and one of the fields is numbers. When I run the query I would like for that column to total the numbers. How can I do this and the have the total appear in the Query?

View 1 Replies View Related

How To Combine Two Query Which Have The Same Column Name To Be Another

Aug 8, 2005

Hi friend
I need your help.
I have made 2 query (named "QryReceivesum" & "QryWithdrawsum").
But query have the same common column called "ProductId".
I want to combine 2 query to be just one.

Currently I have mae one query named "QryStock".
but the data from this qry is not correct.

As you can see, it doesn't show the number of product from productID3.


can anyone help me??

View 8 Replies View Related

Autonumber Column In A Query

Sep 23, 2004

Is there a way to add a column in a query with an autonamber
function ? What I need to achieve, is to assign a unique number to
identify each row of the query.

View 1 Replies View Related

Format Column In Query

Mar 16, 2005

how do I format the column returned in a query. Like the average ..I want to format the values returned upto 1 decimal place...
Code: "SELECT AVG(PMRating.H1) AS DIV_AVG, Employee.Division FROM Employee LEFT JOIN PMRating ON Employee.TokenID = PMRating.TokenID GROUP BY Employee.Division"

View 2 Replies View Related







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