When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.
I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria:
- The Review Status = 'Not Accepted'
- There are zero records in [Results] for that ReviewID
I can't figure out how to do it.
Here is an example of the tables where all record are OK...
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.
What I want to do is to view customer information that does not have customer logins.
I have a master table located on the backend of the server with about 3 fields:
Software Version Description
I have 4 databases, one for each software on the front end accessing this master table.
So far I have in the master table a dropdown to select 1 of the 4 software's, the version number and a brief description.
One the front end I have a button that when pressed will bring up the report based on the master table.
Can I have the report just select the software that it refers to. For instance if the datebase is Sony when I click on the report it only grabs the records with Sony on it. For my next database when I open up Sharp's database it only makes a report for the Sharp records.
How would I do this? Is this something I have to code or something I can do in criteria or do I create a query?
I've looked through the thread and was unable to find exactly what I'm looking for ?
Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.
What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.
So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.
I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
I have a fairly large table with approx 15k records. I have a form where I have a drop down/select control box that displays all of these records showing a few of the fields. I select one of these records to create a new record as it places one of the fields into the new record. The problem is that there are so many records that it takes a lot of scrolling to get through all the records.
Once I scroll through all the records, the next time I scroll, it is very fast and shows all the records in one scrolling. However, when I get off the form and then go back it starts over with all the records having to scroll through etc.. What I want to be able to do is have all these records loaded so that they can be scrolled through quickly the first time I open up the form.
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code: Public lngNewBalancesTBI As Long ' Defined in a separate module... ------- Dim dbs As Database Dim rst As Recordset Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I have a basic database design, well I think so anyway. It only has two tables:
1. tblClientInfo 2. tblNotes
Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.
I then have two forms:
1. Claims Loss Form 2. tblNotes_DatasheetSub1
So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).
Example of a print out of what it is doing:
John Doe - 123 Easy St - Notes 1 (Most Recent Note) John Doe - 123 Easy St - Notes 2 (Previous Note) John Doe - 123 Easy St - Notes 3 (and so on...)
It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.
How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?
I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.
I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.
The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.
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.
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?
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here... 2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While Temp table: lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a 1 - "STK" - a 2 - "CHK" - a
Main table lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a - LEAVE (EXISTS In Both Tables) 1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp) 1 - "STK" - a - LEAVE (EXISTS In Both Tables) 1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp) 3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this??? SELECT D.* FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber) WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).
Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.
I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).
I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.
SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID GROUP BY Kitloan.SchoolID, Kitloan.DateOut HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));
What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.
SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;
I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too): =DCount("KitBkID","KitloanCountQry") This works but obviously gives me the total for all bookings.
[code]....
Although I have to admit to getting lost in the syntax. This produces #Error.
I have a form with a listbox that displays the name of a table. Once the listbox item is selected, the table name is set to a variable called myFile. I want append the records from the table (myFile) into another table.
tbl_A has column "ID" with duplicates. tbl_B has column "ID" with distinct values.i want to find all the distinct IDs in tbl_A that are present in tbl_B.I've tried the following with no luck:
SELECT DISTINCT tbl_A.ID FROM tbl_A LEFT JOIN tbl_B ON tbl_A.ID = tbl_B.ID WHERE tbl_A.ID IN tbl_B.ID;
I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.I have attached a screen shot (parts.jpg) showing the structure.I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.
You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system. I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.
Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......
