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

Queries :: Find Duplicate Records In Table With Two Fields

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

Queries :: Find A Way Of Overwriting Records In Table With New Data

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

Queries :: Need A Query To Find Records That Contains No

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

Queries :: Find Records Without Associated Record

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

Queries :: Find Records Not Scheduled / Complete

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

Queries :: Find And Replace Full Records

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

Queries :: Dropdown Box - Find Records Based On A Form

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

Queries :: Simple Query To Find Records Which Are Not Numbers

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

Find A Records Position In A Table

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

Modules & VBA :: Find Records In A Table?

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

Queries :: Create Query To Find Certain Records Based On Data In CSV File

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

Queries :: Find Data In One Table And Put In Field Of Another Table Then Update

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

Queries :: Make Table Query - Find Table Name

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

How To Query A Table To Find Records Showing Checked Boxes

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

Queries :: Find Dates In One Table But Not Another

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

Queries :: Find Value In Another Table And Return ID

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

Queries :: Transaction Table - Find Last Record For Each Day

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

Queries :: Find Duplicates From 2 Tables And Remove From 1 Table

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

Queries :: Update A Field If Find A Match In Another Table

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

Queries :: Find Result In A Table Containing Highest Date Value - Too Many Results

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

Queries :: Find Specific Data In A Table To Produce A Report

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

Queries :: Query That Will Return Records From A Table That Have Related Records In Another Table

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

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

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

Queries :: Retrieve Records From One Table Where Dates Are Between Records In Another Table

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

How To Find Records, Name "like" Keywords Given In Column In Another Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved