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"
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
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
I have a complex IF Statement within VBA. When I step thru the code the if statement variables should return true but instead treats it as False. See '>>>> this happens on the 2nd Pass of a Do Loop Statement, the First Loop the if Statement Returns True
Variable Values:
Record 1 contractNo: 00001634 nfld: 33.40% nTier6: 30.00%
Record 2 contractNo: 00001634 nfld: 137.52% nTier6: 28.50%
so the 2nd if should be true but it acts as false an moves to next If statement.
Code:
If nfld = Format(0, "Percent") Then nOvrAmt = 0 BkOvrCalc = nOvrAmt GoTo cont: '>>> ElseIf nfld > nTier6 Then nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
I inherited a spreadsheet that uses an Index (Match formulate to return a value. I want to use Access to manage the data going forward. I am just starting to build my tables in Access so I have some flexibility and want to do it right. Here is a sample of the data:
So if Enroll date starts in Jan I want the 10 amt returned from the Jan column. If the Enroll date starts in March, for that client I want the March amy returned, etc.
results: for client "DEF" the amt returned should be "7" because their enrollment date is 3/2013.
how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
So there's a memo field in my database that has a copies of an entire email message. I was wondering if there was a way for me to search for, extract, and place specific information from the text in the email into a new field automatically for each of them.
I'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.
My SQL:
SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt FROM tblRecovery GROUP BY tblRecovery.CustID;
This returns : Syntax error (missing operator) in query expression 'IF(( etc.
After clicking "OK", access highlights AS in the statement. I'm not sure how to deal with the NULL value or fix the error?
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3 INTO [tblTemp] FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3, False AS Field4, False AS Field5 INTO [tblTemp] FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID .fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration ORDER BY t_asset_personnel.LAST_NAME;
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.
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
I have an access table which has a key using letters (A,B,C,D...etc). Against each letter there is multiple rows against each letter. However for testing purposes I only need to get a single row against each key and I was wondering if there is a function I could use which would return only the first record of data for each key?
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
