Queries :: How To Retrieve Column Name
Oct 31, 2013I have table in which i want to update reason column as "def missing" on 1st record and "abc missing" on second record.
View Replies
ADVERTISEMENT
Queries :: Retrieve Column Name When Data Is Null
Oct 31, 2013I have a table in which i want a column name as output when the data in it is null.
View 2 Replies View RelatedQueries :: How To Retrieve Column Data Using SQL Query
Apr 14, 2014I'm trying to retrieve data based on the contents of one column.
Sample table
Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111
gfdsffsdf 1006 W5464111
gfd 1005 N7765111
gfdsffsdf 1006 A4000011
gfdsffsdf 1006 W5464111
ssdad 1001 2378797
gfd 1002 1164478
ssdad 1001 965000
gfd 1002 780000
yjgk 4456 540000
kjhkh 2009 150000
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not
IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
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
Code:
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(Basecode,1)) AND Left(Basecode,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'));
Queries :: How To Retrieve Column Data Other Than Specified In 2 Sql Queries
Apr 14, 2014I 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'));
How To Retrieve Column Name That Contain Maximum Number
Apr 9, 2013I have an Access database with a table that contains 11 fields. The first is a unique ID and the other 10 fields are various numbers. I need to know which of the 10 columns contains the highest number for each row and return the 2 rightmost characters of that column name. I started with the "maximum" function that I found on-line and it works to give me the maximum number in each row, but I need it modified to return the 2 rightmost characters of the column name that contains that number.
Code:
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
[Code] ....
Forms :: Retrieve Info From Another Query - Higher Column Numbers?
Aug 22, 2014When I attempt to use the me.field = me.combobox.column() method to retrieve info from another query,it only works if I use column 0 or 1... it doesn't work with higher columns number.
- field name: cbofi
- row source: listado-unico-fi <-- it is a query with unique values and 7 fields in total
- event procedure, change:
Private Sub cbofi_Change()
Me.txtDOCTPT = Me.cboFI.Column(1)
Me.campovia = Me.cboFI.Column(5)
End Sub
- txtDOCTPT is the field that indeed works
- campovia is the one that does not work
I tried using column(0) and column(1) and it worked fine... but higher column numbers do not work. Just mentioning because the field names are all right.
Queries :: Need A Way To Retrieve First Highest Value
Oct 3, 2013using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.
View 2 Replies View RelatedQueries :: Retrieve Records From Specific Month
Mar 10, 2014I have a query which I'd like to create which would be to retrieve records from a specific month of a specific year, in this format "01/14" "02/13" etc..
Obviously it would need a prompt box for the query for the user to input the month and year.
Queries :: Retrieve Data From A Table From SQL Query
Feb 21, 2014How to retrieve data from a table (via query) ? I created the below query, but I'm not sure what else is needed to retrieve the value from my SQL query. My query code is below. I'm not getting any errors.
Dim strClient As String
strClient = "Jerry Davis"
strSQL = " SELECT [Progress Tracking].[Client Name], [Progress Tracking].[Client Start Date],
[Progress Tracking].[Start Body Weight], [Progress Tracking].[Tracking Date]
FROM [Progress Tracking]
WHERE [Progress Tracking].[Client Name])= ' " & strClient & " ' "
MsgBox "Weight Box " & " " & strSQL
Queries :: Tracking Database - Retrieve Top 100 Records
Jul 24, 2013I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).
I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.
My Top 20 query is below
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;
I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.
Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column
Sep 24, 2013I 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 RelatedQueries :: Change In Column Based On Base Query Column
Mar 24, 2014I 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...
Queries :: Find Unmatched Contents From The Column By Matching With Another Column?
Nov 16, 2013i 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 RelatedQueries :: Can Retrieve Value Of User-defined Type In A Query
Aug 19, 2013Is there a way to retrieve the value of a user-defined type in a query?
Here's the type:
Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type
I have a function with the following excerpt:
Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function
In my query I expected to be able to put the following:
Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount
However, Access didn't like the period in .Rank or .ClassCount.
Should I just write two different function to get 'rank' and 'classcount'?
Queries :: Substring Extraction - Retrieve Information From Field
Aug 7, 2014How I can retrieve the following information from a field
[CONTENTS]
1 x 17.7ml
2 x 17ml
1 x 181.1ml
I need
17.7
17
181.1
I was using a combination of CAST(LEFT(RIGHT...)) AS Float
but that obviously won't work due to too many variations...
Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column
May 21, 2015I 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 RelatedQueries :: How To Distribute Different Values Of One Column In New Column Fields
Jan 30, 2014In 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?
Queries :: Retrieve Number Of Loans Taken / Repayments - No Duplicate Entries
Jun 6, 2013I have groups and members tables in which each group is formed from no. of members, each member can take loan and will return this loan in a no. of installments so here my problem is when i run the query to retrieve no. of loans she has taken and no. of repayments she has given, this query retrieves same loan amount duplicate ... How to group by this query so that when i run this query it should only retrieves that no. of loans (no duplicate) she has taken and no. of repayments (no duplicate) she has given...
For ur more information I attached the db here ...
Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field
Oct 1, 2013Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?
Queries :: Retrieve Criteria From Form / When Form Is Closed
Mar 28, 2013I have a query that retrieves one of its criteria from a form. I have referenced correctly the value on the form from the query, and it works, but what I wanted to do is a bit more complicated: when the form is closed, I want to launch the query with a "default value". I tried to do it in 2 different ways:
a) Defining an "IIf" at the query criteria: I would need a function that checks if the form from which I retrieve the values is open.
b) Defining public variables with a default value, which would be changed from the form: I don't know where/when to initialize the value of the variable.
Query gets criteria from form when it's open. If form is closed, query uses default value.
Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count
Aug 16, 2015I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
Queries :: Combine Two Queries Result Of Second Query In Another Column
Mar 15, 2014i 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"));
Queries :: 2 Queries Into A Single 2 Column Query?
Jun 6, 2013I 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 RelatedQueries :: How To Get Sum Of Multiple Queries In Each Column
Oct 1, 2014How 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 RelatedQueries :: Shifting A Column Up?
Dec 5, 2013I 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
Queries :: Divide Each Value By Sum Of Column
Aug 9, 2015I am trying to divide each unique value in a column by the total column; so sales by total sales.
View 4 Replies View Related