Queries :: Database For Log Activity - Delete Only Appended Records
Nov 3, 2014
I have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.
The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.
I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.
Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?
I've attached my database as I have it now. I am trying to create what amounts to an activity log.
I have created my main Subject and underlying related Issues tables, and a table for my daily activity notes. I have created my main form and subforms that should be sufficient (hopefully) for my purpose. Where I am getting hung up now is making the Subject and Issues fields combo boxes and filling them.
For the top-level Subject, I want to be able to type a new one in at any time, but also have the option to choose from a dropdown on the control as well. Then, if I do type one in, the next time I try to add a new record, it would appear in the dropdown as well. I think I have to query the underlying field to drive the dropdown list, but I cannot remember how to do that.
For the second-level Issue, there would also be a dropdown in the same way as the Subject control, and I want to be able to choose from the dropdown or add a new one in the same way... but I need to make sure that the ones that appear in the dropdown are related to the top-level Subject shown in the main part of the form. Of course, I don't want to be able to add an Issue unless it is related to a parent Subject. On these things, I am totally lost.
Finally, I built my notes to show in the subform as a continuous form, which I have used only once before. I'd like it so that the notes show newest at the top. And, I'm thinking about putting a "lock" checkbox on the note record so that I don't overwrite previous notes unless I consciously go back to edit something. I'm not sure how I can make an individual note dependent on an individual checkbox in the continuous form.
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
Hey Guys & Gals, extreme newb here attempting to display the latest activity date in an SKU activity db in a query that spans 12 months with multiple activity dates on each of the 1,200 SKUS.
I have this so far and of course it only shows the last (DMAX) activity, not SKU related. How can I relate this result to each SKU to diplay the latest activity for each.
SELECT [Usage06-07].workorderDateClosed, [Usage06-07].qty, [Usage06-07].itemNo, [Usage06-07].Item FROM [Usage06-07] WHERE ((([Usage06-07].workorderDateClosed)=Dmax("workorderdateclosed","usage06-07","item"))) ORDER BY [Usage06-07].Item;
Just a question, I am developing a database for the company I work for and being new to Access, I have successfully made a login menu when the database starts up. The employee selects there name and begins data entry. Is there any way to log the activity of each employee, which records they inputted, date and time and so on?
I have an append query that appends records to a table, and I have a form based on that table.
Users will click a button that will run the append query and then open a form for users to fill in remaining empty cells. How can I filter the form to show only the newly appended records?
I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.
My question is: (1) Is my SQL going to give me the last time a client was in the office within the 4 month window?
(2) What will my SQL need to like to count frequency of appointments before and after window for each client?
(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?
Code: SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest FROM tblTest GROUP BY tblTest.Client HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#)) ORDER BY Last(tblTest.AppointmentDate);
Details
Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201
Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.
Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code: DELETE Warranty FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair] WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table bub|12345|1 bub|45678|2 bub|91011|3
Child Table bub|45678|2
result in Main table
bub|12345|1 bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions). tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE * FROM M0070 WHERE EXISTS (SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
I appended multiple tables to one table by using the copy, paste, append data to existing table. I now have 1,691 line items in one table. However, when I run a report off this new table and export it into Excel I only receive 1,300 line items.
Hello! My Small access aplication has a tables appended from dbf database program. I am coping with an issue to set access to this tables as read only.For me its very vital that source dbf application won't be changed from MS Access.Thanks for help!
I am looking for a way to either display through a query with an expression or any other alternative that someone may have to display the last 100 records entered maybe based on date entered or something.
At the moment i have a query and report going form start date to end date but would also like a query/report that will just show me the last 100 records entered.
