Queries :: Find Records In One Table And Not In Another
Apr 2, 2014
I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.
Both tables have structure
TRAINEEID
MODCODE
CountOfUnits
I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL
Code:
SELECT
[T-temp-Target].TRAINEEID,
[T-temp-Target].MODCODE,
[T-temp-Actual].MODCODE
FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON
([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND
([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE)
WHERE ((([T-temp-Actual].MODCODE) Is Null));
View Replies
ADVERTISEMENT
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
Aug 6, 2014
I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.
View 2 Replies
View Related
May 29, 2015
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
View 4 Replies
View Related
Aug 3, 2013
I have the two tables in my access DB:
Property Information
Lease Information
I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.
Property Info:
PropertyID
Address
Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)
How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.
View 4 Replies
View Related
Jun 4, 2013
I have a database for training. I have a table of employees with one of the criteria being active which is enabled by a check box. I have a second table of all of the training data. When I add a new employee, or I want to schedule someone, I want to be able to have a combobox on a form which I can select the type of training and then with a push of a button find if there is an active employee which has not had this training through results of a query.
View 14 Replies
View Related
Apr 24, 2015
I have a table of sales information. Part of my products are kits that are a combination of individual products. (Think of it as a value meal at a fast food restaurant. You can order a hamburger, a soda, and a small french fry separately. But if you get the Value Meal #1 you get all three at a discounted price.)
What I would like to do is make a new table/query that extracts out the kits out of the original sales table and replaces the kit record with records that show the components.
I have attached a file to show as an example of how the records current look and how I would like it to look.
View 8 Replies
View Related
Jul 16, 2014
I have a form with a dropdown box that shows the 'Group by' records in a table.
For example the form shows "Art_6". My table has many records (could be 1000's) that start "Art_6".
I want a macro/query to find all records that start "Art_6" and delete them.
How to find only those that start as described.
View 13 Replies
View Related
Jun 1, 2015
I have an table that contains StudentID, Name and Age. I have imported the data from Excel sheet and there are some records which contains Null Value and some "h", "n/a" etc. I would like to design query which finds the records that are non numeric.
View 2 Replies
View Related
Jan 18, 2008
Is there a way, aside from using auto number, to find a records postion. I am using a list and want to be able to double click an entry and have it open a form and go to that record. However I can't use auto number because I allow for the deleting of records, and once deleted the auto number no longer corrilates to the records position.
If anyone knows of a different sytem that renumbers after deleting a record that would be great if you could share it with me. Or if there is a way to open a form and use goto record but instead of using record postion use where statement.
If anyone has any other ideas on this please share. Thanks.
View 6 Replies
View Related
Aug 23, 2013
I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.
The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.
My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.
Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-
Function CopyTest()
On Error GoTo CopyTest_Err
Dim strMeterRef As String
DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNext
strMeterRef = Meter_Reference
[code]....
As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.
I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.
View 2 Replies
View Related
Aug 14, 2014
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
View 6 Replies
View Related
Nov 7, 2013
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
View 1 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
Oct 18, 2007
I have a table listing xxx reasons for dispute - Users will check boxes. How do I build my query that would list all the entry numbers which have a text box?
Might sound stupid but what would be the criteria? Think it would be easier to get these results into a report and sum.
I'm baffled!19142:confused:
View 9 Replies
View Related
Mar 14, 2014
I've got two tables, both are indexed by customer ID, with a series of dates against the customer ID. One has a list of all dates a customer was visited, the other is a list of dates where activity happened on the customer account
I want to get a list of the dates when the customer was visited but where no activity happened on the customer account, i.e. where there is a customer visit date on the customer visit table but no record for that date on the activity table.
How do I do that? I can find all dates where was a date was on both tables, but how to find where its on one but not the other
View 2 Replies
View Related
Aug 8, 2014
I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].
However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.
Code:
Table 1
publication numberWO2011085209A2
WO2011100754A1
WO2011112983A3
EP2342192A4
EP2342192A2
EP2205725A2
EP2205725A4
WO2012006540A3
WO2010008486A3
WO2012083136A1
Table 2 contains another list of patents that might cite/refer to Table 1's patents and additional data such as publication date.
Code:
Table 2
Publication Number Citing PatentsPublication Date Cited Refs - Patent
AU2001287375B2 1998-12-01 US5178882A | US4225581A | WO1998001161A2
AU2001288365B2 1990-02-24 US5967154A | WO1996039117A2 | US3699979A | US3943949A | US3838702A
AU2001288437B2 1999-03-09 US6087157A
[code]....
Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:
Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...
Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).
For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..
View 2 Replies
View Related
Oct 7, 2013
I have a table that contains records of transactions. I need to find the last record for each day.
DBTransIDtransDay UID
1931219/17/07 8:50 AM128
1932879/17/07 9:13 AM128
1932929/17/07 9:14 AM128
1933049/17/07 9:16 AM128
2388149/18/07 4:54 PM128
2388449/18/07 5:06 PM128
2388459/18/07 5:06 PM128
2388469/18/07 5:07 PM128
2389299/18/07 5:45 PM128
2389309/18/07 5:45 PM128
2389319/18/07 5:45 PM128
2391299/19/07 8:55 AM128
2391389/19/07 8:57 AM128
2391399/19/07 8:57 AM128
2391409/19/07 8:57 AM128
2391419/19/07 8:57 AM128
A script that I found that appeared to do what I needed it to do:
SELECT [TT Transactions - KT].DBTransID, [TT Transactions - KT].transDay
FROM [TT Transactions - KT]
JOIN (SELECT MAX(transDay) Max_transDay_By_Day
FROM [TT Transactions - KT]
GROUP BY convert(varchar, transDay, 112)) t2
ON t1.transDay = t2.Max_transDay_By_Day
But it returns a "Syntax error in FROM clause" that I can't figure out.
View 2 Replies
View Related
Jun 29, 2014
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));
View 9 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
Jul 6, 2013
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
View 2 Replies
View Related
May 14, 2013
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
View 5 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
May 19, 2015
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
View 14 Replies
View Related
Dec 30, 2013
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
View 14 Replies
View Related
Feb 8, 2006
Hi!
Is it possible to make such query which will find all records that have
name like keywords. These words are collected as s dictionary in another table in column name.
Is it possible to make just one query or vb script that will search through all records using all of keywords given in dictonary?
example
dictionary:
abaccus
dolphin......
.....
query find records wich name is like abaccus or dolphin or........
Thank You in Advance for any advice cause i have big problem how to figure out.....I have to do this in my job......
View 2 Replies
View Related