Multiple Match Conditions In A Unrelated Table
Jan 31, 2007
Hi All,
My database has two tables:
1. Complete Address Data
2. Address conditions.
Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.
How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?
The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure
View Replies
ADVERTISEMENT
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 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
Apr 10, 2013
Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......
(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');
View 1 Replies
View Related
Apr 30, 2006
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
Can anyone help me?
Cheers
View 6 Replies
View Related
Feb 29, 2008
Hi,
Is it possible to create a lookup in a query against a table which has not relationship but has a match?
E.g. iif(spec(in query)=Spec (match table) then bring back cost in (match table)
Hope this makes sense?
Thanks
View 1 Replies
View Related
Apr 11, 2006
G'Day:
I have a form that I created that allows the user to input an "ExhibitNo" and then a "PlacingNo" to allow each entry to receive a set monetary amount (based on values in a table).
The "ExhibitNo" has two references linked to it. They are "ClassNo" and "ExhibitorID".
My supervisors want me to make it so that when the "ExhibitNo" is entered on the form that it will not allow a monetary amount.
This is where I started, but it does not work. Does anyone have a better idea?
Private Sub Combo14_AfterUpdate()
Me.Refresh
If [ClassNo] >= 0 >= 5000 Then
If [ExhibitorNo] <= 0 >= 499 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] <= 500 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If
If [ClassNo] >= 5000 Then
If [ExhibitorNo] >= 500 Then
[Premium] = 0
End If
End If
End Sub
Thanks
View 1 Replies
View Related
Nov 6, 2013
how to do multiple conditions, Nested....I have 6 conditions that i like to check,
Code:
if (condition-1) And
(condition-2) And
(condition-3) And
'[code]....
View 8 Replies
View Related
Sep 10, 2014
I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.
For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'
I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.
View 6 Replies
View Related
Apr 3, 2013
I am trying to calculate [current status] based on multiple conditions. for example
Current status = A , if (w>0 and x=0 and y=0 and z=0)
Current Status =B , if(w>0 and x>0 , y=0 and z=0)
Current Status =C , if(w>0 and x>0 and y>0 , z=0)
Current status =D , if(w>0 and x>0 and y>0 and z>0)
Where A,B,C,D are text values and w,x,y,z are dates
View 1 Replies
View Related
Dec 26, 2014
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
View 7 Replies
View Related
Jan 11, 2005
For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked
Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...
I can think it through logically but cannot get my head around the query.
If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless
If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;
Then i also need to do the reverse for the Date Loan Booked, i.e.
If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;
Phew! That probably makes no sense but i had to try.
Once again, i appreciate any help so that i can keep the remainder of my hair.
Switters
View 1 Replies
View Related
Oct 11, 2013
I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.
All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.
What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.
For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.
Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.
Here is some code that I have on the Report button:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.
If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.
I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.
Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
or
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
View 3 Replies
View Related
Aug 25, 2005
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
View 2 Replies
View Related
Mar 12, 2014
I want to open a filter form with mulitple conditions.one of them is a date condition.
I'm using access 2013 and the code below:
Code:
DoCmd.OpenForm "Edit_Mission", acNormal, , "[Report_Date]= " & Me.Date & " And [Supporter_Name]='" & Me.Supporter & "'"
it's opens the form but with no data. I also tried the # and it's still didn't work.
View 1 Replies
View Related
Jan 9, 2013
is there any way to put into the calculated field (in expression builder) conditions? What I need is something like
Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)
I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).
View 13 Replies
View Related
Apr 14, 2014
I have a main form which has couple of subforms bound to one main table. One of the subfomrs is about employment info. It has 3 TextBoxes: (Job Start Date), (Employer Name), and (Wage).
It has also 3 ComboBoxes: (Job Type [Full Time or Part Time]), (Hire Status [Permanent or Temporary]), (Quarter [1st, 2nd, or 3rd]).
I want to force users to fill all these 6 fields if they put any value in any one of them. I tried to set a Validation Rule in the TextBox/ComboBox’s property including IIF statement condition, but I did not succeed, and these rules did not work.I put the following code in the subform’s (After Update), (Before Update), and (On Current) events which works very well when I keep the (Wage) value 0 and move to a new record or to another subform:
Private Sub Form_AfterUpdate()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "You did not put how much is the wage."
Cancel = True
End If
End Sub
[code]....
how to make a similar validation rule for the other TextBoxes and ComboBoxes.
View 14 Replies
View Related
Jul 24, 2013
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
View 3 Replies
View Related
Sep 8, 2014
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[code]...
View 1 Replies
View Related
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
Apr 2, 2014
I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds
Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result:
PersList T1Component
Andrew Med 1
Andrew Med 2
Brett Med 1
Brett Med 3
Brett Med 4
Charles Med 2
Duane Med 1
Duane Med 4
Quy 2 Result
Regimen T2Component
Goody1 Med 1
Goody1 Med 3
Goody1 Med 4
Goody2 Med 1
Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return
Person Regimen
Andrew Goody2 (he has med 1, 3, and 4)
Brett Goody1 (he has med 1 and 2)
"Bad" would return
Person
Charles
Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
View 4 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
Sep 22, 2014
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
Patents.PatentNumber | AP.ID | Patents.Reference | AP.Title | AP.Year | AP.Volume | AP.PageStart
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
View 10 Replies
View Related
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 1 Replies
View Related
Mar 19, 2014
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
View 2 Replies
View Related
Feb 22, 2013
I have 3 tables :
table 1 empno Employee Nomber
empname Employee name
salary basic Salary
bonus
deduction
iddate month nomber
table 2 is bonus table id
empno
bonus amount
iddate
table 3 is deduction tableid
empno
deduction amount
iddate
Now i need is to transfer the data from bonus table and from deduction table to the table 1 if the empno and iddate are equal on the both tables
What I mean for ex if i create query and when i enter the employee number directly it will give me his bonus if he has and his deduction also if he has, so how can i do it?
View 1 Replies
View Related
