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?
View Replies
ADVERTISEMENT
Oct 15, 2014
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.
View 1 Replies
View Related
Dec 6, 2004
Hi,
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
Can anyone please help ?
View 14 Replies
View Related
Feb 21, 2007
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;
Thx in advance for your patience :)
Joe
View 2 Replies
View Related
Dec 11, 2013
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?
View 3 Replies
View Related
Jun 26, 2014
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?
View 3 Replies
View Related
Jul 10, 2014
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.
View 14 Replies
View Related
Sep 18, 2013
I have a query that displays the start and finish time for an activity, I am trying to display the reults of the time between each activity....
For example
Name start End
aaa 14:07 14:09
aaa 14:11 14:12
The end time for the first activity was 14:09 and the next job was started at 14:11, so I want it to display 00:02: - (elapsed time)
Its very easy to do in excel as it would be =SUM(B2-C1) but haven't a clue how to do it in access.... is there any way?
View 3 Replies
View Related
Mar 26, 2015
delete Query How do i Specify the table containing the records that I want to delete?
View 1 Replies
View Related
May 1, 2015
I have two tables as follows;
Master file:Vessel_master
Vessel_code
Vessel_name
main data table:Main
Vessel_code
container_no
size
Voyage
weight
by using above two table have made a query as follows;
Vessel_name:Vessel_master
Vessel_code:Main
container_no:Main
size:Main
Voyage:Main
weight:Main
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.
View 1 Replies
View Related
Apr 20, 2015
I have list box named [lstItems], a table named [tblItems] and the field name is [Model]
I would like to (from a list box) delete a record from a button. I have looked and tried lots of delete code, but none of them seem to work.
View 14 Replies
View Related
Oct 25, 2014
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.
View 3 Replies
View Related
Jun 19, 2015
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.
View 8 Replies
View Related
Jul 3, 2015
I have a form based on query. Each record has a checkbox.
I would like when activate checkbox to delete the specific record. from a button in the form.
Have not find anything around
View 14 Replies
View Related
Mar 4, 2015
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..
View 3 Replies
View Related
Jun 3, 2013
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]
View 8 Replies
View Related
Oct 24, 2013
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.
View 4 Replies
View Related
Apr 17, 2013
How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
View 13 Replies
View Related
Aug 13, 2013
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.
View 6 Replies
View Related
Mar 27, 2013
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.
View 2 Replies
View Related
Jun 25, 2015
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.
View 1 Replies
View Related
Apr 17, 2015
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],
[Code] .....
View 3 Replies
View Related
Mar 21, 2005
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.
View 3 Replies
View Related
Apr 30, 2006
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!
View 3 Replies
View Related
Jan 9, 2007
I currently have 2 tables going:
Table 1: Customers
Fields: Account (join field)
First Name
Last Name
Table 2: Transactions
Fields: Account (join field)
Date
Payment (money that comes in)
Fee (invoice that is charged)
I need to find those customers that do not have any transactions (no payments, no fees) for a certain date period (ie entire month of January)
I have see "sql not exist", "minus", "outer join" for sql statements, but most of my work is in Access 2000.
Any help is greatly appreciated!!
Thanks ahead of time.
Leeann... :)
View 3 Replies
View Related
Jul 15, 2014
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.
View 10 Replies
View Related
