I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
hey all, I'm using this code on a command button to delete single entries in a listbox... and it works fine Private Sub DeleteMovie_Click() CurrentDb.Execute "Delete from " & _ "tblMovies " & _ "where Title = """ & Me.lstList & """", dbFailOnError
Me.Requery Me.lstList.Requery Me.lstList = "" End Sub However, I want to try to select and delete multiple items in the list box. So I changed the property to allow multiple selections and now the button doesn't do anything when I click it. Ideally, I'd like the button to provide a "are you sure (yes or no)" prompt. Anyone know how to get this working? thanks a bunch
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String Dim i As Variant With Me.se1 For Each i In .ItemsSelected SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;" CurrentDb.Execute (SQL) Next End With Me.se1.Requery
Result is this one : 1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
:confused: I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete() DoCmd.DeleteObject acTable, "060204_cfd_postings" End Sub
In my program you are allowed to make backups of the current databases to an external file. It also allowes you to get the tables back from one of those files. At least it should.
I thought this wouldn't be a biggy. I thought I'd just delete my current tables and replace them with "docmd.tranferdatabase" acImport.
But the stupid thing wont let me delete its tables because they are connected to each other. Can I somehow delete the link before deleting them?
If so will the links be there again once I restore the tables from the backup? Or do I need to reconnect them somehow to each other.
Or maybe another approach would be to import them without deleting the original ones. Thus the new ones get the same name of the original ones + "1" or something. Than I delete all records from all my original tables. And refill them with all the records from the new ones (the + "1"-ones) And delete the new ones. And if I would do that, would it be best to use AcImport or AcLink (from docmd.tranferdatabse)
I was wondering if there is a way in selecting all tables and deleting at one go instead of selecting one by one. Perhaps a little bit of programming. Tx in adv
At certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table and 4 records in the Classdata table, then it exports out 16 records (4 x 4). I would expect it to export out 8 records.
I am writing a sql to assign students to each section of a course. The first student would be in the first section and the second student in the second section.
The ClassParam table gives the number of sections for the course and the last period that was assigned.
The Classes table gives the class key number by class name and section number.
The student has the name of class they want to take and I want to move the class section number into the Choice1 field.
The IIF statement seems to work but all of the students are getting the 1st session of the class put into the choice1 field.
UPDATE Students, ClassParam, Classes SET Students.Choice1 = Classes.ClassID, ClassParam.PeriodAssigned = IIf([ClassParam]![PeriodAssigned]>=[ClassParam]![NumOfSections],1, [ClassParam]![PeriodAssigned]+1) WHERE ((([Students]![FirstChoice])=[Classes].[ClassName] And ([Students]![FirstChoice]) Is Not Null And ([Students]![FirstChoice])<>"") AND (([ClassParam]![PeriodAssigned])=[Classes].[SectionNumber]) AND (([Classes]![ClassName])=[ClassParam].[ClassName]) AND ((ClassParam.ClassName)=[Students]![FirstChoice]));
Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.
I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.
I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.
I have 2 main tables. One with Data that I get from an outside source and cannot be changed. The other is one that needs to be updated as needed. I have a few look up tables so I can identify the codes with the descriptions. In 2003 I was able to make a big query and link my forms to the query to make modifications. I am not able to edit any information in the 2010 query. The people want it to work the way it did in the past but I am not skilled enough in access to do that. Is there an easy way to use a query when updating the form? I used to be able to edit the query itself and it would feed back into the existing tables.
I have a database built in Access 2000. It consists of the following tables:
45 tables each containing the same fields - ID, Vehicle, Fit, Part No, Qty, Level, Higher Assembly.
1 table containing the fields - ID, Part Number, Description, Issue No.
The 45 tables are each for a different vehicle build of materials, whilst the single table contains the additional common data that each vehicle form pulls in when the build of materials part number is added.
I want to be able to undertake a search across the 45 vehicle tables to look for a specific part number that I enter in the search box, and then if any is found, for all those vehicle to be displayed, along with the part number, description and issue number from the single table.
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
I am working on an MSACCESS database that reads data from SQL Server through ODBC Connection.
I have multiple tables that I have linked in and created Queries that read the data and created Make Table query.
However, I am asking if there's a way to bring the data through a query that doesn't need the linked tables. Is there a way to bring different fields from different tables into one query without needing to import all the tables, since the tables have many fields that I don't need.
For example in MS Excel, I am able to do that, a query that will bring external data from SQL Server from different tables and only brings the fields that I need for my report.
I am working with 4 tables and I am trying to get a count of the email address field in each table grouped by Email Group Name. I tried the dcount function but returned an error because some of the email addresses have an apostrophe in the field. Is there a way to do this in 1 query? This is an example of the result I would like:
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
I am trying to make a database for a shop which can do several things:
1) I want to give in what is being bought by the shop to make their products form. This has to be saved in a table named STOCK.
2) I want to be able if I say in a form that from these things I made that product that these things dissapear from the STOCK table and that the final product is added to the STOCK.
3) If the final product is solled I want to remove it from stock.
In the end if I want to see what is still in the stock I will only have the things that are still there because the rest is removed.
Having searched the last couple of days, I am unable to find a resolution to this problem. If it has been answered already, a simply link or thread name would be appreciated. If not, help is greatly appreciated.
I have code running that imports data from a .txt file into a table.however, on some days there will be errors importing some of the data. I have found that from the last three months, there has been no data of consequence lost so, I don't care about the data errors. However, any time there is an error with the import it creates a table that is named for example:
01-03-05 Auth_ImportErrors
where "01-03-05 Auth" is the name of the file imported. So each time they import a file, a new table is created. Is there any code that I can run that can automatically delete these tables upon exit?
I routinely import from an Excel document provided by a third party. Most data in it is ok, but 10-15 records always end up in the import error tables which Access creates automatically when formats do not match. I am trying to automate this whole process thru VBA and am pretty successful so far, except that I haven't figured out how to programatically get rid of these tables (I do not need them, and data imports anyway). Is there a way to destroy a table programatically in Access?