General :: Find Records That Match Criteria Of Multiple Fields
Mar 12, 2013
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
View Replies
ADVERTISEMENT
Nov 2, 2005
Hi, I have a personell DB an I vant do be able to select only the persons from a specific "city" with a specific " genre" and a specific "skill" and also i want the query to "ignore" one of the criteria if I press Enter or input all.
I tried with "query design " and I don't seem to make it work for more than one condition.
Thanks
View 2 Replies
View Related
Jan 23, 2012
I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
View 5 Replies
View Related
May 2, 2013
I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.
So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.
example table:
LevelID DoorID
1 1
1 2
1 5
1 6
etc..
All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..
What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.
View 14 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Jan 25, 2006
I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.
For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.
Any help would be appreciated.
View 1 Replies
View Related
Sep 22, 2013
I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:
Private Sub PrepCustcmd_Click()
'Turn warnings off
DoCmd.SetWarnings False
DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit
[code]....
View 2 Replies
View Related
May 20, 2015
I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?
View 5 Replies
View Related
Dec 26, 2012
I would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
Code : Me.Lastname.width = Len(Me.Lastname]
View 1 Replies
View Related
Nov 10, 2005
My database has a street address field that I would like find and replace a handful of certain strings with other strings. For example I would like to see:
Sunset Point = Sunset Pt
Main = Main
Prospect = Prospect
Caminito Paseo = Camto Paseo
Camino Litoral = Cam Litoral
I would like to perform something exactly like Find and Replace but to multiple strings in ONE function or query to update.
Is this possible? I looked at fReplace() but not sure where to put this.
Thank you.
STARS14
View 6 Replies
View Related
Jan 19, 2006
I'm building a database with supplier information. Some users prefer to use the supplier names, but it is sometimes hard know how foreign suppliers are spelled, therefore some users prefer to use supplier numbers.
I want my form to show all supplier data based on either the supplier number or the supplier name, which can both be chosen from 2 comboboxes.
In addition, and here's the catch, I would also like the two comboboxes to update eachother. So if I choose supplier 2 in the first box, the other box should automatically show me the name of vendor 2 and vice versa.
I think the example form shows my problem best ..
View 4 Replies
View Related
May 30, 2015
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code
Code:
Private Sub SearchBtn_Click()
PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
End Sub
This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code
Code:
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &
I get an Compile error saying "expected expression".
View 3 Replies
View Related
Jul 6, 2012
I need want to use the vlookup function in Access 2010 when creating a query. I am doing this as an expression in design view of my query.
I want the field in which I am creating this question, to first, ask what state I am looking for (I have put this in the criteria box of the field "[State]"), then search another table (I have it created already, it is a list of the states) for that state and plug the cell with the name of that state into this new query cell.
View 6 Replies
View Related
Nov 10, 2014
i need to find sum of three or more fields in a table.
View 6 Replies
View Related
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
May 8, 2013
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies
View Related
Jul 15, 2012
I need to write a query to find and match all numbers recorded against a list of numbers (1 - 200) and to identify those missing or not matching.
View 1 Replies
View Related
Jul 30, 2013
I have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?
View 3 Replies
View Related
Jun 5, 2007
Hello all,
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
Thank you!
View 1 Replies
View Related
Feb 16, 2014
I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.
What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.
So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.
View 5 Replies
View Related
Feb 14, 2007
I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.
While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?
View 2 Replies
View Related
Aug 29, 2013
I want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
View 6 Replies
View Related
Jun 26, 2015
I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.
I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".
When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.
My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.
View 3 Replies
View Related
Jun 29, 2015
I have a table Billing_Temp that I need one field updated if I find a match in another table Random_Temp. I runt the query and it prompts for "Enter parameter value: Random_Temp.peopleID... what could be going on? Both tables have a field called peopleID and always Billing_temp has many more records than Random_temp:
UPDATE Billing_Temp SET Billing_Temp.audited = -1
WHERE (([Billing_Temp].[peopleID]=[Random_Temp].[peopleID]));
View 1 Replies
View Related
Oct 12, 2013
How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.
View 4 Replies
View Related
Nov 29, 2012
I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?
View 2 Replies
View Related