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 Replies


ADVERTISEMENT

Queries :: Retrieve Column Name When Data Is Null

Oct 31, 2013

I have a table in which i want a column name as output when the data in it is null.

View 2 Replies View Related

Queries :: How To Retrieve Column Data Using SQL Query

Apr 14, 2014

I'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'));

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

Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field

Oct 1, 2013

Background 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?

View 3 Replies View Related

Queries :: Retrieve Data From A Table From SQL Query

Feb 21, 2014

How 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

View 12 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 :: Data From Rows To Column

Apr 6, 2014

I need to change rows data into column.

I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master

Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc

The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as

Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc

How to change this from rows to one column.

View 1 Replies View Related

Queries :: Copy Data From One Column Into Another Of Different Table?

Jun 9, 2013

I have two tables pertaining to the same database.

Table [Purchase_Order_Details] amongst many fields have Columns - (Purchase Order #) which is the Primary Key and (Work Order #)

My second table [Order Details] also has a column (Work Order #) . Now I have added another column to this table i.e. (PurchaseOrderNo) - this column is blank as of now.

However I would like to run a query to fill the (PurchaseOrderNo) column depending upon the (Work Order #) which is present both the tables.

View 14 Replies View Related

Queries :: Get SQL From Data On Multi Column Listbox

Oct 22, 2013

I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now

View 10 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 :: Select Statement To Return Specific Data From Another Column?

Nov 3, 2014

I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.

I am currently developing a database from scratch for work (with very little Access experience).

The current query I am trying to run, if linked to a number of tables with different information.

What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".

In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)

The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....

When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"

What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.

If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"

View 10 Replies View Related

Queries :: How To Reduce Down Data Set To Get A List Of All Unique Combinations Of Column

Jul 8, 2014

I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.

Column A Column B Column C Column D
NJ Red Monday 10
NJ Red Tuesday 20
NJ Yellow Wednesday 30
NY Red Monday 35
NY Green Tuesday 40
NY Green Wednesday 60

I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.

View 4 Replies View Related

Queries :: Need A Way To Retrieve First Highest Value

Oct 3, 2013

using 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 Related

Queries :: IIF Statement Expression - Create Alias Column Based On Data From Two Fields

Jun 20, 2013

I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.

Here's the logic:

True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"

False: "GS"

View 2 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 :: Retrieve Records From Specific Month

Mar 10, 2014

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

View 6 Replies View Related

Queries :: Tracking Database - Retrieve Top 100 Records

Jul 24, 2013

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

View 10 Replies View Related

Queries :: Can Retrieve Value Of User-defined Type In A Query

Aug 19, 2013

Is 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'?

View 2 Replies View Related

Queries :: Substring Extraction - Retrieve Information From Field

Aug 7, 2014

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

View 4 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 :: Retrieve Number Of Loans Taken / Repayments - No Duplicate Entries

Jun 6, 2013

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

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







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