Query To Display Data That Does Not Match
Jun 12, 2006
Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.
I hope that makes sense?
Any help would be fantastic!
Cheers
Phill
View Replies
ADVERTISEMENT
Oct 28, 2013
I have a db which has a query. This query is rather simple where it filters fields based on criteria. Here is the SQL VIEW:
SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (((DSResult.STATUS)="TESTED") AND ((DSResult.TESTCODE)="41015" Or (DSResult.TESTCODE)="41016") AND ((DSResult.BATCHNO)<>"1") AND ((DSResult.TESTDATE)>=#1/18/2010#))
ORDER BY DSResult.TESTCODE, DSResult.TESTDATE, DSResult.CODE;
I need to alter this query so that where records that have the same "DSResult.CODE" and counted are greater than 20 occurrences and then display only these records sorted in DSRESULT.Code order.
View 6 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
Apr 14, 2008
Hi,
I need to match the data in all the fields between 2 tables.
eg.
Field 1 (Table 1) = Field 2 (Table 2)
Field 2 (Table 1) = Field 2 (Table 2)
so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
View 2 Replies
View Related
Jun 15, 2014
There are 2 tables:
Table 1 has National Insurance Number, first name, last name, phone no, address.
Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
View 10 Replies
View Related
Dec 19, 2006
I have a database which contains details of groups of students. Groups are assigned tutor(s) using a link table between the Groups table and a Tutors Table.
I want to create a query which shows each group with their tutor. If I add the three tables to the query I can correctly generate a list of groups with their tutors, but any group which hasn't got a tutor (i.e. where the Tutor column would be blank) doesn't feature in the results.
I want all groups to be listed, even if they haven't got a tutor. Is this possible? How?!
Thanks in advance,
Gary
View 2 Replies
View Related
Jan 15, 2014
I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.
From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?
QUERY SQL:
TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");
View 1 Replies
View Related
Mar 19, 2013
Display data from Query into Form..I am attaching the database dbtest.zip which contains a table, couple of queries and form.I have done what ever settings I could after going thru some tutorials / discussion forums online, but to no avail.
All that I want is, when I open the form "frmtest" in "form view", and select CS in Combo 8, the corresponding fields in the form viz Item name, Sl No and System Name should get updated on the form. I want to view it in "Single Form" format.
View 1 Replies
View Related
May 12, 2015
I have a form that displays employee details. I've also created a query that calculates the current age of all employees and updates automatically. I've also created a subform that displays the query data.
My question is, how do I get the query data to display on my main form? Can I do it directly from the query or do I have to use the subform? There are multiple columns on the form and I want to add it as a new column that displays the same as the rest but continues to update automatically.
View 9 Replies
View Related
May 29, 2013
I am attempting to use some external data to populate fields in my DB. I would like to reformat the ProductID in my DB to match a ProductName coming into my DB. We have many products that have 2 pieces. If the product does have two pieces, the external database has two ProductNames that look like this:
0000967
2000967
I would like my database to be able to pull information for each of these 2 part products (they will be displayed as one product in our DB, never to be seperated). I have a form that gives the exact measurements of the first piece by using the ProductName and matching my ProductID (0000967). I would now like to write a little VBA to populate some fields on the forms that are pulled from 2000967.
something like:
Forms!Product!txtField2 = DLookup("[FieldName]", "TableName", "[ProductName] = Forms!Product!ProductID")
The issue is that I need to only get the trailing 6 digits of my ProductID and add a 2 at the beginning. Is there a quick Format syntax I could use to accomplish this?
View 14 Replies
View Related
Jul 26, 2006
In MS Access form, how can I create my own message if the user enter a value that not match with the data type of a field in underlying table? Thanks a lot!
View 3 Replies
View Related
May 9, 2015
The Data Types match and they are in order. I get a syntax error.
PHP Code:
    Â
      strSQL = "INSERT INTO tblHour (Hours, WorkDate, SickDay, SickDte) VALUES (" _
   Me.AbsenceHrsID & ",#" & Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#,True,#" _
   Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#)"
CurrentDb.Execute strSQL, dbFailOnErrorÂ
View 4 Replies
View Related
Feb 5, 2013
I have built a nice database that has a form to enter data which logs in product received, there is a combo box on the same form that is linked through the query builder to auto populate the names from the contacts info table (the receivers of the product received) the contacts info table also contains information that is specific to each name such as locations.
As of now I have created a command button that brings me to the form that shows the information fields I need specific to a name, once I get that I have another command button to bring me back to the main form. How to create an additional list box on the main data entry form so as when the name is entered the new list box or text box (which is best?) will auto populate the information I need on one form instead of going back and forth.
Example:
Requester Name [ auto populate name ] currently linked to contacts info table (working)
(New field) Preferred Location [ need to auto populate location ] from the contacts info table (how do you pull locations specific to a contact name from the same table?
View 3 Replies
View Related
Jul 10, 2013
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
View 2 Replies
View Related
Nov 19, 2014
The next stage I'd like to include is a match and enter pricing details. We get a lot of repeat work that is entered onto our system, due to the nature of the work we do each item is then checked individually. There is a portion of this that could be automated.Our order intake table has an amount of information, various bits of which are supplied by various customers, but not always all of the information is required or given.
Id like a price to be copied from a previous record where the "shots" field matches a previous entry.The other fields that would need to be similar are the "coverage" "drawing no" "pattern no"..But only when these fields are populated, we often, but not always put a - in these fields where the information is not given. I think this may be bad practice, but it shows us the information has not been supplied, and not just missed from the order.
To sum up, when an order is completed i'd like to have an afterupdate event that searches the previous records, where cover, drawing no / pattern no are *matching* and the shots do match, then copy the price of a matching record, where that record is within 12 months of today (using the date_rec field) and then tick an "automated_price" field so i know the price has been generated by the system.
View 1 Replies
View Related
Aug 12, 2015
I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.
However , I want to get the data from the uploaded excel file.
I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"
Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?
Query SQL: "PolyWrongRegInsCount"
Code:
SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP
FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING
WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC"))
GROUP BY GROUPING.INSTITUTION;
My Chart SQL :
Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;
View 3 Replies
View Related
Nov 13, 2011
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
What is wrong with my macro/subform?
View 8 Replies
View Related
Apr 17, 2006
Probably an easy one...I have two tables, each with a number field say "Cat-ID" and "Ref-ID". The query I am looking to write will show the outstanding values. For example
Cat-ID: 100, 200, 300, 400, 500
Ref-ID: 100, 200, 500
Query Result: 300, 400
Any ideas on how to do this? I'm stumped.
View 2 Replies
View Related
Apr 17, 2008
I have a table with two fields:
Field1 Field2
S11.1 111001
S13 130001
S11 110001
S13.1 120001
I need to query to find where Field2's first three digits does not match the numbers in field1. Giving the sample: 120001 would be listed in the results because it does not match the numbers in S13.1 130001(2nd record) does because if there is no "."; it defaults to zero.
I don't know to set up the criteria or what to ask so it would give me the results needed.
Can anyone help please.
Thanks
View 1 Replies
View Related
Sep 19, 2005
I have converted an Old FileMaker Pro DB to Access.
There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years.
One of the main fields we query by is the "Description" field.
If I have the following Criteria:
[Enter Document Description]
Then save and exucute the query Access seems to look for an exact match to the value I provide.
I need a query to return ANY record with the criteria value in it.
For example If I entered "Fire" I would like to return anything with "Fire" in the description:
B Building FIRE alarm Install
Building C FIRE sprinkler Demolition
A Building FIRE Exit Signage
Site FIRE Protection Main
etc.
How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?
I realize this is a dumb, rookie qiestion. I tried Access Help and
I tried a "search" in this Forum, I probably didn't do a good job describing my problem.
View 4 Replies
View Related
May 18, 2005
Greetings all!
I am working on something and it has me stumped. Basically I have a list of Eastings and Northings and I am trying to find the closest postcode centroid based on the PAF file (for those that do not know of the PAF file, it contains a list of postcodes and the easting and northing of the centroid).
The best way that I can see of doing this is:
For each record I am trying to match, calculate the distance from the Easting,Northing to every easting,northing in the PAF.
Select Min(Straight Line Distance) from the results.
However I do not know how to do this without matching each record that I am looking at individually! Can anyone give me an idea of how I can put this query together?
I am using MS Access :(
Thanks!
~Shiv
View 1 Replies
View Related
Mar 16, 2006
Quick question… I am trying to accomplish something that I believe it is fairly simple. At least in Excel, it is. I have two tables with names. I am trying to create a formula that will compare name in table 1 with a name in table 2. If the names match (exact matches only) it will let me know. Something like =IF (NAME1=NAME2,”TRUE”,”FALSE”) in Excel. I have trying using Iif, but it returns that I cannot divide by zero. I have linked the tables, so if I query NAME 1 and NAME 2, it will only display names on table 1 that are found on table 2.
Any help would be appreciated.
Thanks,
View 4 Replies
View Related
Oct 25, 2006
Hi,
I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
Thank you for your time.
Rajiv
View 1 Replies
View Related
Jan 24, 2015
Is there a way to have a query return only records where the text in two fields doesn't match?
View 1 Replies
View Related
Jan 1, 2014
I have several tables that I need to join together to create a single form for multiple entries. Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.
Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly. The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.
This is what I attempted to use in SQL:
SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices]
FROM [Program List Table]
UNION
SELECT [Log Date], [Log Entry]
FROM [Program Notes]
However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.
View 8 Replies
View Related
Jul 27, 2006
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.
What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.
Can this be done?
View 3 Replies
View Related
