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 Replies
ADVERTISEMENT
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
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 2 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
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 13, 2015
How do I find the previous record in a query using the autonumber field?
View 10 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
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
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
Jun 23, 2006
I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
View 1 Replies
View Related
Oct 29, 2013
I have ASSET_TYPE, MANUFACTURER and MODEL tables.
I have a Table ASSET_COMBO that links to the above tables.
I have a form to create amend an ASSETS table. The form uses combo fields to filter the options available to the user. I.E the User Picks Manufacturer then selects from list of Asset Types provided by that Manufacturer, then selects from list of Models.
I want to create a form to allow create and amend records on the ASSET_COMBO Table.
The problem I have is that I want to ensure that there are no duplicates on ASSET_COMBO Table. I know that I can Select Multiple keys from the table with no duplicates allowed. But from what I have read this can cause problems. Is there a simple way that I can detect that a query on the ASSET_COMBO Table has returned a valid record or has not found a record.
View 3 Replies
View Related
May 22, 2013
I am trying to use a query to search 2 many to many relationships but i keep getting duplicate results because each record is slightly different.
I have a table with cameras, another users and another formats. I have a table linking cameras to users and another linking cameras to formats so it looks somthing like this:
tblCameras - tblFormatlink - tblFormats
tblcameras - tblUserslink - tblUsers
When i add all the tables into a query i get loads of results as expected because i get a result for every possible combination.
What i want is when i search for all cameras that are suitable for a user who is a beginner is to get just a list of unique cameras. When i do that search at the moment i get a duplicate record for each different format the camera can do.
View 4 Replies
View Related
Jul 11, 2015
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
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 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Apr 9, 2015
My database was working fine before there was an update to my external data source.
This was my original Append Query
INSERT INTO RLCR4000_CODED ( FY, PayPeriod, ProjCode, DelTask, RegHrs, OTHrs, TotalHrs, FYTDTtlHrs, ProjDesc, TtlCost,
FYTDRegHrs, FYTDOTHrs, FYTDTtlCost, TaskDesc, EmpName, ORG, EmpId, CostCtr, PPBegDate, PPEndDate,
Level2Nodes, Level3Nodes, Level4Nodes, Level5Nodes, Level6Node )
SELECT RLCR4000_pp5_RAW.[Fiscal Year], RLCR4000_pp5_RAW.[Pay Period], RLCR4000_pp5_RAW.[Delphi Project Code],
[Code] .....
The data source changed and the result is now I have a bunch of "Near duplicate" records. I built this query and found the duplicates:
SELECT [RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode, [RLCR4000_-_Labor_Cost_by_Organi].DelphiTask, [RLCR4000_-_Labor_Cost_by_Organi].EmpId, [RLCR4000_-_Labor_Cost_by_Organi].CostCenterCode, [RLCR4000_-_Labor_Cost_by_Organi].RecordTypeCode, [RLCR4000_-_Labor_Cost_by_Organi].FiscalYear, [RLCR4000_-_Labor_Cost_by_Organi].PayPeriod, [RLCR4000_-_Labor_Cost_by_Organi].RegularHours, [RLCR4000_-_Labor_Cost_by_Organi].OvertimeHours, [RLCR4000_-_Labor_Cost_by_Organi].TotalHours, [RLCR4000_-
[code].....
There is a field called "RECORDTYPECODE" that was inserted in the external data source. It has one of four values : R, L, N, C
Ideally this is what I want
WHERE ((([RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode) In (SELECT [DelphiProjectCode]
FROM [RLCR4000_-_Labor_Cost_by_Organi] As Tmp GROUP BY [DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode]
HAVING Count(*)>1 And [DelphiTask] = [RLCR4000_-_Labor_Cost_by_Organi].[DelphiTask]
And [EmpId] = [RLCR4000_-_Labor_Cost_by_Organi].[EmpId]
And [CostCenterCode] = [RLCR4000_-_Labor_Cost_by_Organi].[CostCenterCode])))
Then R+L+N+C=
NOTE and in some cases the fields are null
I have attached a sample of the data. And the second tab is desired result. I have over 9000 near duplicates so it is imperative I create a formula to merge them
View 5 Replies
View Related
Mar 27, 2013
I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
View 2 Replies
View Related
Feb 6, 2014
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
View 3 Replies
View Related
Jan 22, 2014
How can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?
View 3 Replies
View Related
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
Sep 29, 2014
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
View 1 Replies
View Related
Jul 1, 2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
View 5 Replies
View Related
Aug 8, 2014
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
View 2 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
