Queries :: How To Return Value Based On Row And Column Location
Aug 9, 2013
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:
KeyEnroll JanFebMarAprMay
abc1/2013103521
def3/201300789
ghi5/201300008
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.
View Replies
ADVERTISEMENT
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
Jul 10, 2015
Basically in the query I need a Collumn to only output the greatest value that is given within the sub query. If that makes sense?
Eg. Tenancy start date must be the highest date against the date the property was added back to the system - for post development properties.
At present the it's returning previous tenancies from pre development.
View 3 Replies
View Related
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
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
Dec 6, 2013
I have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
View 4 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
May 14, 2013
Is Access SQL able to do this:I want the value from a particular column, whose name is derived from the value in the field of another column?
Eg.
Col1 Col2 Col3 Col4 Col5
1 ABC 234 Col2 ABC
2 BCD 345 Col3 345
3 CDE 456 Col2 CDE
4 DEF 567 Col2 DEF
5 EFG 678 Col3 678
6 FGH 789 Col2 FGH
As you can see, I want the value in column 5 to be derived from the value in Column 4.Is this possible to be done in SQL.
View 7 Replies
View Related
Jan 23, 2006
i have a url file location to a pdf file on the 3rd column of a listbox.
is there anyway of opening the file from a button, refering to the the highlighted record row 3rd column pdf file location
i know that me.lstBox.column(2) gets me to the column but not the code to open the pdf file for the highlighted record
any help would be appreciated
View 1 Replies
View Related
Nov 8, 2013
Is there a way of using a text field value as the heading for query column?
ie change...
Column header: FieldInATable
to
[form1].[field2]: FieldInATable?
View 1 Replies
View Related
Jul 11, 2013
I have a field called density which needs to be updated to show either 10, 20, 30, 40, 50,60, 70 or 80 depending on a number of variables, for example: If market location is 'hot' and unit type is 'house' and discounted is 'no' then show '10' in the density box.
or perhaps:
If market location is 'cold' and unit type is 'apartment' and discounted is 'no' then show '20' in the density box.
I have tried all sorts of expressions and queries but have really reached the limit of my know how and can't solve it. Is it even possible to do this in Access?
View 2 Replies
View Related
Mar 15, 2013
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
View 2 Replies
View Related
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
May 1, 2013
I have two tables that are related: BUCKETS & LOCATION
BUCKETS has these fields:ID (autonum)
Type (type of bucket)
Creation Date (when it was created)
Operational (if it is operational or not (Yes/No).
Location (Location of the bucket)
Notes (notes)
LOCATIONS has these fields:LocationAutoID (Autonumber)
LocationCity
LocationID
BucketsOnSite (can have multiple entries)
What I'd like to have done is for LOCATIONS table to be aware of how many different cities there are. So for instance, if there is multiple location entries for the cities of Vancouver and Toronto in there, I want the LocationID to first know how many Torontos there are and in LocationID to combine the LocationCity and a count (starting from 1) to that field.
So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.
At first I had the expression of [LocationCity] & "-" & [LocationAutoID] in the Expression field in the Field Properties but I quickly realized that it wouldn't count the amount of cities but just add the autonumber and the city (which kind of works, but not ideally).
View 8 Replies
View Related
Mar 5, 2007
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
View 1 Replies
View Related
Nov 30, 2014
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
View 4 Replies
View Related
Jul 4, 2006
Hi,
I need to create an sql query for an ms access db that will return the data type of a given column.
Can anyone help me? I quite new to sql etc.
thanks in advance!
View 2 Replies
View Related
Feb 15, 2015
When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.
So basically I want to go something like this:
.tblDirctoryLocation
txtDirectory
.tblProduct
txtProductName
txtProductNumber
txtProductCADNo
These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.
Somehow I want the db to assemble file location like this:
“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf
Is there any way this could be done?
View 1 Replies
View Related
Mar 3, 2015
I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.
The response I got on another forum is as follows:
-The impression I'm getting is that you've got a database of information over here.
-And it lists the file name, but not location.
-And then you have a physical folder structure with the fax files in it.
-And there are folders for done, awaiting approval, or processing.
-So what you're looking for is something to read those folders and update the status column based on which folder it is in?
-So at the start of the day we have:
New
-Fax1.tiff
-Fax2.tiff
-Fax3.tiff
-Fax4.tiff
Finished
-
In progress
-
And at the end of the day the folders look like this:
New
-Fax1.tiff
Finished
-Fax3.tiff
-Fax4.tiff
In progress
-Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status. Create two separate functions, one for in progress, one for completed and call them one after the other. There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.
View 3 Replies
View Related
Jun 29, 2005
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?
Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX
There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.
In SQL I would write it like this:
Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;
Thanks in advance,
Ken
View 2 Replies
View Related
Apr 13, 2006
How do I return the Column Header Text on Right Click of listbox?
Thanks
View 5 Replies
View Related
Jun 25, 2014
I have a checkbox that when checked returns all of the yes values for the column but I want it so that when it isn't checked, it doesnt search for this criteria at all, is there a way of doing this? I tried option buttons too but I wasn't sure of how to go about it.
View 13 Replies
View Related
Nov 10, 2004
Dear All:
Thank you for all you help with the above title. Your suggestions were all implemented.
Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
Please help,
Dion
View 5 Replies
View Related
Jun 15, 2015
Is there anyway to choice where the dialog box from my queries will pop up on my forms?
View 5 Replies
View Related
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
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
