Queries :: Shifting A Column Up?

Dec 5, 2013

I was wondering if it was at all possible to duplicate a column, but shift it up by one record. So for instance I have a table that looks like:

Date-------------Duration
1/12/2013------0
2/12/2013------20
3/12/2013------15
4/12/2013------10

But I would Like it to read

Date-------------Duration---Shifted Duration
1/12/2013------0------------20
2/12/2013------20----------15
3/12/2013------15----------10
4/12/2013------10----------0

View Replies


ADVERTISEMENT

Shifting Cells Within A Table

Jun 8, 2006

Hi,

I currently have a table of contacts. For the most part, each record has a 'company name' field, an 'address' field, etc. Unfortunately, because I imported this data from a text file (I ran a VBA code which extracted each record from the text file and put them into the table) and because not all the contacts have the same number of fields, the fields don't all line up.

How can I traverse through the entire table and shift the cells so that everything lines up properly (i.e. - all the addresses are under one column and all the fax numbers are under another)? If I have to write some sort of procedure to do this, what approach should I take?

Thanks!

View 1 Replies View Related

Shifting Pop-Uo Form On The Screen

Aug 15, 2005

Hi All

I am looking for a method to shift a small pop-up window around on the screen. I want to avoid the overlap with the help function, which is an external program.
I already tried this code in the form:

Me.Left = Screen.Width / 2.5

But a form does not seem to have the property .Left or .Top.
Does anyone have the solution?
Hans van Vliet

View 4 Replies View Related

General :: Creating Unique Ordered Number Field With Auto Shifting Values

May 31, 2013

I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously.

Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries.

Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3.

View 4 Replies View Related

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

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

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 :: Combine Two Queries Result Of Second Query In Another Column

Mar 15, 2014

i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.

First Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;

Second Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));

View 2 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 :: How To Retrieve Column Data Other Than Specified In 2 Sql Queries

Apr 14, 2014

I have a table (tbl1) contains sample records in Basecode column like S2378797 , R1165778 , W1165778 , N1165778

Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111

[code]....

And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967

Third query not working

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

View 2 Replies View Related

Queries :: How To Get Sum Of Multiple Queries In Each Column

Oct 1, 2014

How to write the syntax to get the sum of a query in a query builder. I want to get the sum of multiple queries in each column. ie (Query: R1-R7)

View 3 Replies View Related

Queries :: Divide Each Value By Sum Of Column

Aug 9, 2015

I am trying to divide each unique value in a column by the total column; so sales by total sales.

View 4 Replies View Related

Queries :: MIN (00) Or MAX Value To Return In Same Column

Jun 10, 2015

I am having an issue with a select query as I want one column to show the MAX and "00" value of the same Column. How do I go about having this task completed.

For example, the column has 00,01,02,03,04,05,*1,*2,*3,*4,*5 so I want a query that will return values of "00" and "05" on this example
00
05

Further example, this is what I have now

SELECT qryPR34_UNION.PR34001, qryPR34_UNION.PR34002, qryPR34_UNION.PR34003, qryPR34_UNION.PR34005,
qryPR34_UNION.PR34006, qryPR34_UNION.PR34007, qryPR34_UNION.PR34013, qryPR34_UNION.PR34014,
qryPR34_UNION.PR34098, qryPR34_UNION.PR34099, qryPR34_UNION.PR34100, qryPR34_UNION.PR34101
FROM qryPR34_UNION
WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0")
AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));

On the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005

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

Queries :: Count On One Column

Jan 5, 2015

I have one table with one column that I am trying to extract data from.What I would like to do with the Column is split it into a few counts. The Column has numbers in it with KB usage. What i'm trying to do is count the amount of users that use say 0. Then have a column next to it that count say 500 >= 2000 and so on. This is completely baffling me as to why I can't seem to do this. I've even redesigned the database to see if I could fix this problem, but no luck I can build a query for this to be done sepearetly, but can't seem to find a way to put it into one query.

SELECT Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID, [test Usage].[Nov-14], [test Usage].[Nov-14], Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID1
FROM [test Usage]
GROUP BY [test Usage].[Nov-14], [test Usage].[Nov-14]
HAVING ((([test Usage].[Nov-14])=0) AND (([test Usage].[Nov-14])>=500));

View 5 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 :: How To Retrieve Column Name

Oct 31, 2013

I have table in which i want to update reason column as "def missing" on 1st record and "abc missing" on second record.

View 4 Replies View Related

Crosstab Queries?...Is There A Way To Create More Than One Column?

Nov 12, 2004

Hey..

If there was a way I could get the crosstab query in Access to allow more than one column I would solve a major problem. Is that possible?...help anyone?

View 1 Replies View Related

Queries :: Multiple Query In A Column

Apr 20, 2014

I have create few option buttons in a form and assigned values. when each option button selected it will store a value in tbl.attend.log

Exp - Opt 1 button = 1 = OnDuty
Opt 2 Button = 2 = DutyTravel
Opt 3 Button = 3 = SeekLeave

[code]....

View 2 Replies View Related

Queries :: Max Function In Query By Row Not Column

Apr 3, 2015

I have a table that holds the dates of various approvals for documents.

Approval DatesDocumentID
ApproverA_DateApproved
ApproverB_DateApproved
ApproverC_DateApproved
LastUpdated

In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.

Code:
Name: Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])

"The expression you entered has a function containing the wrong number of arguments"

How can I get the most recent date a document was approved by any approver?

BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.

View 3 Replies View Related

Queries :: Auto-fill One Column From Another

Jun 11, 2014

I have a situation where, in a query, I need to autofill the column called "Xferd Vendor No" from the column called "Vendor No", where if the data in the Vendor No column changes, then the autofill changes to match.The one called Autofill Raw Data.xlsx shows the expected query results. The one called autofill Final Output.xlsx shows the final results I need when the task is completed.

View 2 Replies View Related

Queries :: Company Two Tables On Same Column?

Aug 13, 2015

In query, I would like to compare two tables and get the result but I always get the round robin result.

Table1

OrderQty Company Order# 8 Oop BBS 12 abc AAA 3 abc AAA

table 2
OrderQty Company Order# 7 Oop BBS 12 abc AAA 3 abc AAA

Result I get which I don't want What I want is the last table
Order2.OrderQty Order2.Company Order# Order1.OrderQty Order1.Company 3 abc AAA 3 abc 3 abc AAA 12 abc 7 Oop BBS 8 Oop 12 abc AAA 3 abc 12 abc AAA 12 abc

The result I need. How to get this?

Order2#Company Order# Order1#OrderQty Order1#Company 3 abc AAA 3 7 Oop BBS 8 12 abc AAA 12

View 4 Replies View Related

Queries :: Sum All Alternatives From Column That Equal Specified Value

Jun 27, 2014

I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.

so for example: Specified value = 150

Field 1 Field 2
------- -------

Name1 10
Name2 50
Name3 25
Name4 25
Name5 100
Name6 40
Name7 125
Name8 50

I'd like the query to return all possibilities that equal 150 so it should look like:

result 1 - name5 + name2
result 2 - Name5 + Name 8

View 3 Replies View Related

Queries :: Lookup Column Query

Jun 16, 2013

What I am wanting to do is to have columns at the end of my imported data that reference another table to tell me based on the queue what service type and centre it relates to.

I have attached 2 pictures one with table 1 and the other with table 2. Table one shows the last column 'SERVICE_TYPE' which I need to lookup from the reference table (aka table 2 - which has been attached).

View 3 Replies View Related







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