Queries :: Checking That All Matching Fields Are True
Mar 21, 2013
For my query I have 3 tables.
TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
View Replies
ADVERTISEMENT
Jun 26, 2013
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
View 7 Replies
View Related
Aug 14, 2013
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
View 5 Replies
View Related
Jun 16, 2013
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
View 8 Replies
View Related
Sep 27, 2005
Hi all,
I had a little problem with delete query. I would like to delete the records by checking two fields if both fields are empty. I mean if a records had both these fields are empty only. If one of the field has data , the record should stay there.. Is there any way I can do this?
Thanks in advance.
Thanks
View 4 Replies
View Related
Oct 19, 2004
If I have about 9 Text fields, How would I go about checking to see if all of the text fields are empty, then hide all the fields, but if any of them have anything in it, show them all. Here is my code, but it doesn't work:
Code:If SpecAgent = "" And SpecArea = "" And SpecBenefit = "" And SpecCompany = "" And SpecCSR = "" And SpecDoctor = "" And SpecHospital = "" And SpecPlan = "" And SpecRx = "" ThentglNo = TruetglYes = FalselblSpecAgent.Visible = FalseSpecAgent.Visible = FalselblSpecArea.Visible = FalseSpecArea.Visible = FalselblSpecBenefit.Visible = FalseSpecBenefit.Visible = FalselblSpecCompany.Visible = FalseSpecCompany.Visible = FalselblSpecCSR.Visible = FalseSpecCSR.Visible = FalselblSpecDoctor.Visible = FalseSpecDoctor.Visible = FalselblSpecHospital.Visible = FalseSpecHospital.Visible = FalselblSpecPlan.Visible = FalseSpecPlan.Visible = FalselblSpecRx.Visible = FalseSpecRx.Visible = FalseElsetglNo = FalsetglYes = TruelblSpecAgent.Visible = TrueSpecAgent.Visible = TruelblSpecArea.Visible = TrueSpecArea.Visible = TruelblSpecBenefit.Visible = TrueSpecBenefit.Visible = TruelblSpecCompany.Visible = TrueSpecCompany.Visible = TruelblSpecCSR.Visible = TrueSpecCSR.Visible = TruelblSpecDoctor.Visible = TrueSpecDoctor.Visible = TruelblSpecHospital.Visible = TrueSpecHospital.Visible = TruelblSpecPlan.Visible = TrueSpecPlan.Visible = TruelblSpecRx.Visible = TrueSpecRx.Visible = TrueEnd IfEnd Sub
View 2 Replies
View Related
Aug 16, 2013
I have a form with mandatory fields highlighted a different colour (yellow or blue). On the form are 3 tab pages with subforms which also have these fields.
I have added a checkbox named incomplete to each tab page and to the form. My intention is to try to write some code to look at each field on the page and if the non-white (ie mandatory) fields all have a value, then the incomplete on that page is changed to no. The code then checks the incomplete value for all 3 pages - if these are all no and the form's mandatory fields are also filled in, then this also becomes no.
When creating reports, I can then find out which records have not been completed and notify the relevant staff. Also if the data is incomplete, they don't want those records appearing in reports - so I can use the incomplete value from the form.
View 4 Replies
View Related
Jun 19, 2007
Hi All,
Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...
I then create another table called "importtable" with field "a" (imported in from Excel)...
I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...
Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..
Any tips :confused:
View 1 Replies
View Related
Aug 17, 2005
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y1...007/notnull.jpg
Code:Option Compare Database''This module displays field name and type in a massage boxFunction ShowFields(pTable As String) As StringDim db As DatabaseDim rs As RecordsetDim I As Integer''Dim j As IntegerDim n As Integer''Dim NL As StringDim strHold As String, ST As String''Dim x As Variant''NL = Chr(13) & Chr(10) ' Define newline.Set db = CurrentDbSet rs = db.OpenRecordset(pTable)n = rs.Fields.CountReDim x(n - 1, 1) As String'''ST = "Create Table " & pTable & vbCrLf''adding Create table and table name to statementST = "Create Table " & pTable & vbCrLf & "("For I = 0 To (n - 1)For Each fld In tbl.Fields ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf If fld.Required = True Then ST = ST & " NOT NULL" & " " Else ST = ST End IfNext Irs.Closedb.CloseSet db = NothingShowFields = ST '' returns the fields name to main functionEnd Function
on click even code
Code: For Each T In db.TableDefs '''Skip the system tables If Left(T.Name, 4) <> "MSys" Then ''' this line determines the primary key of the table ''' by calling GetPk function from module pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf End If Next T ''' This line of code post the generated table statment to a massage box MsgBox cont
View 1 Replies
View Related
Aug 25, 2014
I have a transaction form and there is at least two fields I need to make sure have been entered before the record is saved.
I have no problem with text or numeric fields but I can't seem to be able to check the contents of a drop down field.
What is the best method to use to make sure a drop down has been selected by the user and contains a value.
I have temporarily used a default value in the drop down but that's not really what I want.
View 3 Replies
View Related
Nov 13, 2006
In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I just cannot figure out how to do this.
Any ideas are greatly appreciated
/twallstr
View 2 Replies
View Related
May 18, 2005
Hello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
View 1 Replies
View Related
Oct 20, 2014
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub
View 8 Replies
View Related
Dec 29, 2011
I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
View 10 Replies
View Related
Aug 30, 2013
The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.
I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,
View 3 Replies
View Related
Mar 25, 2014
I have two databases (files). Each file has a format like the one below.
File 1:
chr start end
chr1 1000 2000
chr2 3000 4000
File 2:
chr start end
chr1 1500 3000
chr2 3750 4000
I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
View 7 Replies
View Related
Apr 30, 2012
I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
View 3 Replies
View Related
Dec 3, 2014
Can I generate an IIf statement with two-true values? I have these two statements but don't know how to combine them:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””)
IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)
I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.
I have tried:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)
But it returns y in fields that should be blank.
View 7 Replies
View Related
Jun 4, 2015
I'm trying to make it a little further with my new call answering database at work.
Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.
I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.
I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.
The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows
Switchboard:-
Code:
Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI
Private Sub cmd_onnet_Click()
DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub
[Code] .....
View 8 Replies
View Related
Aug 25, 2011
I have a subform in which I want to put in a Command Button to open another form.
I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.
I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?
View 3 Replies
View Related
Feb 2, 2015
I have two queries:
1. a sum of payments by individuals
2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
View 5 Replies
View Related
Jul 17, 2014
I have a query that uses a checkbox from a form as a criteria. in the table the values are stored as 0,-1. if i run the query with the checkbox false, the query works and filters correctly, same if the box is null. But when I check the box true, no records load. It worked in an older version of my database so i am not sure why when i copied it over it doesn't work. I even tried a combo box with values of 0, -1 and got the same results.....
View 6 Replies
View Related
Mar 21, 2014
I have 3 tables:
- tbl_Positions {containing information on a specific job role}
- tbl_Office {containing information on an office}
- tbl_StaffDetails {containing information of all staff in the company, including a relationship with tbl_Office to say which office they are working out of}
I want to do a simple query saying what type of staff are working out of each office:
e.g.
If in Office 1 I might have a managing director, CEO and 2xOperations managers etc.
If in Office 2 I might have 5xAdmin, 3xHR, 6xIT Support etc.
What sort of query can I run to return just a list of job titles for those present in the office I choose.
View 2 Replies
View Related
Mar 12, 2015
I want my to detect special characters in my descriptions that are not found on keyboard and display as error.
Tried using the following
Description check: IIf([Common Description] Is Null Or [Common Description] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*" And [Description Local] Is Null Or [Description Local] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*","<<Error Desc>>","OK")
but when i tested it using some data, it shows all as <<Error Desc>>
some of the special characters i want to check for are βuΩ etc.
So if my description contains characters that are not: a-z OR 0-9 or any of the following ~!@#$%^&*()_+=-`][';/.,<>?:"{}|~
it will return <<Error Desc>>
View 4 Replies
View Related
May 28, 2013
I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.
View 10 Replies
View Related
Dec 10, 2014
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
View 1 Replies
View Related
