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:
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'));
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: 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 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
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.
When 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.
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?
I am trying to create a Database that will type our orders. I have a table with our customer list that includes both billing and shipping information. The problem is that sometimes one customer will request a "drop shipment" to another customer. Is it possible to retrieve different data from two different customers? Billing info for customer "A" and shipping info for customer "B" without creating seperate tables?
I 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.
Current situation is that I have this form with a chart.
Row Source :
Code: SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP] FROM PolyWrongRegInsCount;
PolyWrongRegInsCount; <- This table is one of the queries that I created.However, the data is manually added. I want to get the data from excel spreadsheet. And load into my queries
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...
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).
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.
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
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.
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"));
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"
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.
I create the table1 in SQL Server and then link the table to MS Access using ODBC. But, when I write do this statement Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("Select * from table1")
for i = 1 to rs.recordcount msgbox rs.field(0) next i
It only retrieve 1 record only, but the table have 10 records. What's wrong with it? I check rs.recordcount, it only loop one time.
Additionally, it doesn't work this function in the ODBC link table rs.addnew rs.field(0) = "hello" rs.update
And, when we use ODBC link tables, we cannot delete data in the table. currentdb.execute "delete * from table1"
it will give out error message, the table is read-only.
i want to retrieve some data from a table i have pieced together a bit of code but get an error to few parameters.
Code:
Dim db As dao.Database Dim Lrs As dao.Recordset Dim LSQL As String Dim Lname As String 'Open connection to current Access database Set db = CurrentDb()
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.
