Queries :: Two Fields Don't Match Query
Jan 24, 2015Is there a way to have a query return only records where the text in two fields doesn't match?
View Replies
ADVERTISEMENT
Queries :: Columns In Query Don't Match
Jan 1, 2014I 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.
Queries :: Query Records Match Less Than 20 Display
Oct 28, 2013I 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.
Queries :: Table Of End Of Week Sales - Query Where There Is No Match
May 4, 2014I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;
My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
Queries :: Number Of Columns In Two Selected Tables Of Union Query Do Not Match
Dec 9, 2013I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
I Need To Match 3 Fields For Duplicate
Sep 26, 2005I am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.
Match Max Value In A Row Of Fields With Row Header
Feb 25, 2014I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
View 11 Replies View RelatedNeed To Match Data In All Fields In 2 Tables
Apr 14, 2008Hi,
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?
Display Records When Two OTHER Fields Match
May 20, 2015I 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 RelatedTables :: Split Fields And Look For Updated Match?
Apr 20, 2013I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
General :: Find Records That Match Criteria Of Multiple Fields
Mar 12, 2013Using 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
General :: Auto Resize Fields Width To Match Length Of Name
Dec 26, 2012I 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]
Queries :: Date Query To Match Other Date Query?
Nov 3, 2014running 3 queries together.
1 qry has a date parameter of start/end date(running before the 3 are together)
Have a field in 1 of the 3 queries with a date field that I need to show being <= the date parameter...
How/where do I use this criteria?
Queries :: Exclude Where Columns Match
Jan 23, 2014I have a table that lists Cost Center, Partner, and Cost. I need a query to sum the cost when Cost Center and Partner do not match. How can I write that expression?
View 4 Replies View RelatedQueries :: Return Results That Exactly Match Criteria
May 9, 2013I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables).A job can have multiple procedures and an employee can have multiple procedures too.
I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.
So for instance if I have:
tblJobs
Job1
tblEmployees
Emloyee1
Employee2
[code]...
If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.I am at a lost for how to construct the SQL for something like that.
Queries :: Match Multiple Rows From Same Column
Apr 2, 2014I 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.
Queries :: Return Records With Partial Match
Feb 14, 2014I understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
Queries :: LEFT JOIN On A Partial Match
Apr 9, 2013I have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.
Test_Items1 (My Table)
Part No = 1317667223606 Desc1 = ABC123
Part No = 1317667223614 Desc1 = ABC456
Test_Products1 (Customer Data)
Product Number = 14_01 Referenced Item = 1317667223606 1317667223735 1317667224517 1317667225406
SQL (Which isn't working for me)
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";
The Desired Results from my above scenario would be this
Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01
Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank
The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them separated by spaces.
Queries :: Match Based On Multiple Values Within One Field?
Sep 22, 2014For 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?
Queries :: Match Postcodes Ad Return Part That Matches?
Sep 7, 2014I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.
I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.
So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.
Queries :: Get Contacts On Report That Match Parameters Inputted Into Box
Jun 19, 2013I very new to Access. I am using the Contact Database template from MS and added a field of text. I am trying to get a query to search the field for partial text. I've typed the following parameter to narrow it down because I only need partial information:
Like "*" & [How are the contacts involved in Organization?] & "*"
I used the query builder off of a report that was already created. I just want to be able to get contacts on the report that match the parameters inputted into the box.
When I run the report it gives me the following error:"You either have an error in your expression or you have attempted to use an undeclared parameter. Check the expression for errors or enter the parameter '[How are the contacts involved in Organization?]' in the Query Parameters dialog."I hit OK and it pulls up the report without data filled in.
Queries :: Match Up Two Tables - Removing / Changing Numbers
Jul 12, 2013Okay, this one should be simple and can be solved in one of two ways. I have two seperate tables with a 12 digit identifying number that is ALMOST exactly the same in each. I am trying to match up these two tables, which works amazingly well, except for the occasional case where the 12 digit ID (which is called API) ends in 01, instead of 00. The last two digits do not make a difference at all, but in one table it will end in 00 and the other it will end in 01, even though they are the same record.
Basically, it looks like this...
Table 1---------------Table 2
541236554700-----541236554700
541236123700-----541236123700
443231246700-----443231246701
So basically, in my final query, the last entry will not show up since these two are not related by this API (ID) number. Any way to either replace the 01 on the end with 00... OR to remove the last two digits?
Queries :: Update A Field If Find A Match In Another Table
Jun 29, 2015I 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]));
Queries :: Return Word Unknown For Any Code That Doesn't Have A Match
Aug 20, 2013I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:
Code:
SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];
Table examples:
Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX
[code]...
Queries :: Sort By Percentage Match To Multiple Field Search
Mar 19, 2014We'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%)
Queries :: Search Box Result - Add Up List Of Dates That Match A Criteria
Jun 13, 2013I am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?